yogi_Car Sales Statistics For Specified Conditions

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #614   Jul 7, 2012     www.energyefficientbuild.com.


user Sam Peckham said:
Array Formulas: Formula to calculate average of a sub-set of data 
Hi all,

I'm struggling to calculate an average for a sub-set of data. My data set is a list of car models, prices, and whether or not they are new. I'd like to calculate the average price for new vs used cars for each model. Ideally, I'd also like to calculate the average price for the top 3 most expensive new cars for each model and compare that to the average price for the 4th, 5th, and 6th most expensive new cars for each model.

It seems like this should be possible with array formulas, but I get stuck when I try to introduce two conditions (must be 'New' and model=BMW).

Works - average $ for a certain model =arrayformula(average(if(A:A=E3,B:B)))

Works - average $ for top 3 cars of a certain model =arrayformula(average(large(if(A:A=E3,B:B),{1,2,3})))

Works - total $ for new cars of a certain model =arrayformula(SUM((A4:A=E4)*(C4:C="New")*B4:B))

Doesn't work - Average $ for Used Cars of a certain model: =arrayformula(AVERAGE((A3:A=E3)*(C3:C<>"New")*(B3:B)))

Doesn't work - Average $ for top 3 Used Cars of a certain model: =arrayformula(average(large(if(and(A:A=E3,C:C<>'New'),B:B),{1,2,3})))

I have an example spreadsheet here with the formulas and source data. The ones that aren't working are highlighted in Yellow.

https://docs.google.com/spreadsheet/ccc?key=0Av1mgN8pEzdsdEtILXpPbjJFQ25SWnV0RUlidEpHbkE#gid=0

Thanks for the help!

- Sam

-------------------------------------------------------------------------------------------------------

following is a solution to the problem -- I have provided formulas for the columns shown with brown background