Using AVERAGEIF with an array in a CSE function

Question

Hi

I'm just getting into the wonderful world of CSE formulae, and am devising a training exercise for  a class.

as you can see from the image below, I've successfully used an array formula to calculate the average cost per tip in cell B22.

I've then tried to use AVERAGEIF with an array in B25 but Excel rejects it with an unhelpful error message - the "there's aproblem wit this formula" pop-up.

Can anyone help?

Thanks!

Answer 1

Enter below formula as an array formula (Ctrl+Shift+Enter) in cell B25:

=AVERAGE(IF(B3:B20=A25,C3:C20*D3:D20))

Regards,

Amit Tandon

www.globaliconnect.com

Answer 2

Hi,

Try this formula in cell B25

=SUMPRODUCT((B$3:B$20=A25)*(C$3:C$20)*(D$3:D$20))/SUMIF(B$3:B$20,A25,C$3:C$20)

Hope this helps.

Answer 3

@oldhasbeen

Alternative with a non-array formula:

In C12: =SUMPRODUCT(--(A2:A12=A14), B2:B12, C2:C12)/COUNTIF(A2:A12,A14) Enter only

Re. an unhelpful error message - the "there's aproblem wit this formula" pop-upAccording to the AVERAGEIF doc the 3rd, if not omitted must be a range: "The actual set of cells to average". In other words you can't pass an array

Answer 4

Thanks Amit.

Easy when you know how!