Problem w/Array Formula

[danw]

I use an Array formula that provides an average of a range of numbers in a
column. On a spreadsheet similar to the following:
A B C D E
1 Name Type Size Rate Cd
2 Shenan Eff 120 600 E
3 Shenan 2Bd 300 750 B
4 Willow 2Bd 320 900 B
5 Willow 3Bd 450 1,450 C
a formula like {=AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D25))} produces
#DIV/0!. I need a formula that will will result in an empty value ("")
should the result be #DIV/0!. Any idea's are greatly appreciated.

 

[Biff]

Hi!

Try one of these: (both are array formulas)

The pedantic method:

=IF(ISERROR(AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D25))),"",AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D25)))

Another option that's a few keystrokes shorter:

=IF(SUMPRODUCT(--(B2:B5="2B"),--(E2:E5="C")),AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D25)),"")

Biff

 

[Harlan Grove]

danw wrote...

I use an Array formula that provides an average of a range of numbers in a
column. On a spreadsheet similar to the following:
A B C D E
1 Name Type Size Rate Cd
2 Shenan Eff 120 600 E
3 Shenan 2Bd 300 750 B
4 Willow 2Bd 320 900 B
5 Willow 3Bd 450 1,450 C
a formula like {=AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D25))} produces
#DIV/0!. I need a formula that will will result in an empty value ("")
should the result be #DIV/0!. Any idea's are greatly appreciated.

Note that there are no "2B" values in the col B sample values above. Do
you mean to match your col B against "2B" as an exact match or as a
substring match? If the latter, make the first conditional term

(LEFT(B2:B5,2)="2B")

For a general approach that only traps #DIV/0! caused by no matching
records, try

=IF(SUMPRODUCT((B2:B5="2B")*(E2:E5="C")),
AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D25)),"")