Calc. Avg. Instead of SUMPRODUCT

[Guest]

I have a 'Sumproduct' function working fine on my Excel file, but I need to
use similar method, this time to do the average instead of sum. I need to
evaluate about 4 criteria, then average the numbers from another column. I
know there's no such: AverageProduct function. I have tried to use the
Average(--(sheet1!$A$2:$A$10000=sheet2$M$8)*(sheet1!$B$2:$B$10000=sheet2$A11)-----*(sheet1!$I$2:$I$10000)).
The formula runs ok, but it gets only a value on one row instead of averaging
out all the values on multiple rows.
Is there another way/function of doing this?
Any direction is very much appreciated.

Thanks.

 

[Bernard Liengme]

The formula =SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11), C1:C100)
will SUM all the C cells having A values equal to F10 and B values equal to
F11
The formula =SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11))
will COUNT all the records for which A values equal to F10 and B values
equal to F11
So one over the other will give the average
=SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11),
C1:C100)/=SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11))
best wishes

 

[Biff]

Hi!

Try it like this:

=AVERAGE(IF((rng1=criteria1)*(rng2=criteria2)*(rng3=criteria3)*(rng4=criteria4),rng_to_avg))

This is an array formula. It must be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

Biff

 

[Guest]

Bill and Bernard, thank you both. I managed to get it to work as:

IF(ISERROR(AVERAGE(IF(COL1=W8,IF(COL1=A11,IF(COL3=B11,IF(COL4=V8,COL5))))),"0",AVERAGE(IF---------------------------))))))

and it worked perfectly.

Thanks.

 

[Guest]

Sorry, I meant Biff.