This is an array formula, which means that you have to commit it using
CTRL-SHIFT-ENTER (CSE) rather than the normal ENTER. Click on the cell
with the formula in, then press F2, then hold down the Shift and Ctrl
keys and press Enter. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
do not type these yourself. If you subsequently edit/amend the
formula, you must use CSE again.

Hope this helps.

Pete

On Aug 29, 12:23*am, Connie Martin
wrote:
I have this formula in L20 that works one place and not the other:

=IF(F20=0,"",IF(F20=1,1,AVERAGE(IF(F15:F19<0,F15: F19))))

On one worksheet the number in F20 is 1, and it gives 1 in L20 with this
formula, but in another worksheet, the number is 11 in F20, and I get #VALUE!
in L20.

What is the reason?

Connie