More elegant way to solve this...in Excel 2003?

[XLOOKUP]

A B

1 50
0 20
1
0 20
1 5
0

I need to find the average of column B where A = 1 and B <> blank. Here's what I got:

=SUMPRODUCT(--(A1:A6=1),(B1:B6))/SUMPRODUCT(--(A1:A6=1),--(B1:B6<>""))

or

=SUMIF(A1:A6,1,B1:B2)/SUMPRODUCT(--(A1:A6=1),--(B1:B6<>""))

I feel like there's an shorter formula for this though...is there?

Keep in mind I want to do this on Excel 2003, so no sumifs/countfs/averageifs.

Thanks!

EDIT - this seem to work too:

=AVERAGE(IF(A1:A6=1,IF(B1:B6<>"",B1:B6,FALSE),FALSE))

with CSE

[TMS]

And you can try:

Please Login or Register  to view this content.

Again, committed with Ctrl-Shift-Enter



Regards, TMS

[Ace_XL]

The AVERAGEIF does not take into account blank cells anyways. So this should give you the desired results

=AVERAGEIF(A1:A6,1,B1:B6)