3 ms·
1. What is the average amount owed? =AVERAGE('Raw Data'!D2:D252) $547.55 2. How many music customers are there? =COUNTIF('Raw Data'!B2:B252,"Musi
by Xcelerate 14y ago
1. What is the average amount owed?
=AVERAGE('Raw Data'!D2:D252)
$547.55
2. How many music customers are there?
=COUNTIF('Raw Data'!B2:B252,"Music")
103
3. What is the due date of the customer in zip code 11680?
=SUMIFS('Raw Data'!E2:E252,'Raw Data'!C2:C252,"=11680")
4/4/2012
4. If you divide the highest zip code by the lowest zip code, what is the remainder?
=MOD(MAX('Raw Data'!C2:C252),MIN('Raw Data'!C2:C252))
7782
5. How many theater customers have a May 26 due date?
=COUNTIFS('Raw Data'!B2:B252,"Theater",'Raw Data'!E2:E252,DATEVALUE("May 26, 2012"))
2
6. Considering all customers, find the standard deviation of dividing the customer's zip code by their number.
{=STDEV('Raw Data'!C2:C252/(1*MID('Raw Data'!A2:A252,LEN("Customer "),LEN('Raw Data'!A2:A252)-LEN("Customer ")+1)))}
85.98374041
A few notes:
- #3's answer cell should have date format (otherwise it will show up as 41003)
- #4's answer cell should have number format (for some reason it defaults to currency)
- The curly brackets on #6 denote CTRL+SHIFT+ENTER for an array formula
There's still room for some improvement. The range 2:252 and the string "Customer " are hard-coded into the formulas. These could be removed with some additional code but this would increase the length of the formula by a good amount.
So, how many did I get right? :)