Problem w/Array Formula
 

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"),D2:D5))} 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.

Problem w/Array Formula
 

Hi!

Try one of these: (both are array formulas)

The pedantic method:

=IF(ISERROR(AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D2 :D5))),"",AVERAGE(IF((B2:B5="2B")*(E2:E5="C"),D2:D 5)))

Another option that's a few keystrokes shorter:

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

Biff

"danw" wrote in message
...
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"),D2:D5))} 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.

Problem w/Array Formula
 

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"),D2:D5))} 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"),D2:D5)),"")