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.

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:D5)))

Another option that's a few keystrokes shorter:

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

Biff

"danw" <[email protected]> wrote in message
news:[email protected]...
>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.

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)),"")