DIV by 0 error in array average function

[Trey Guy]

I have successfully used the following formula:

{=AVERAGE(IF($B$47:$AF$47=AI43,$B$45:$AF$45))}

where the first address range contains employee initials, the AI43 address
contains a single employee's initials, and the second address range contains
hours worked. The formula is repeated accross several columns (with AI43
changing as expected). When the initials represented in AI43 (or AJ43...
etc) are found in the first address range, the average is successfully
calculated. When those initals are NOT found, the formula returns a #DIV/0!
error. I understand WHY this is happening. I would however like some other
value (ie: 0 or "N/A") to appear in the formula's cell when this is
encountered. I have tried embedding/surrounding other IF statements like
IF(ERROR.TYPE..., but the above formula being an array seems to give me
trouble.

Anyone know how I could make this happen?

TIA,
Trey Guy

tg...@mwmc.org

[Thomas Ogilvy]

Trey,
If you do the calculation twice, once as the argument to iserror and then as
the value returned if iserror is false, then you can choose what is shown if
iserror is true.

=IF(ISERROR(AVERAGE(IF($B$47:$AF$47=AI43,$B$45:$AF$45))),"",AVERAGE(IF(($B$4
7:$AF$47=AI43,$B$45:$AF$45)))

Array enter as you did your original formula.

HTH,
Tom Ogilvy

Trey Guy wrote in message <35b64...@news3.uswest.net>...