IF

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

Connie Martin

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
 
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
 
Hi Connie

the formula is an array formula and should be entered or edited using
Control+Shit+Enter, (CSE) not just Enter
When you use CSE, Excel will place curly braces around the formula { }. Do
not enter the Curly braces yourself.

One sheet has the array entered formula, the other doesn't
 
Pete, thank you so much. I forgot about that. This formula was created by
someone else and I changed it a little, and I knew originally that it was an
array formula and needed "special attention", but, not being very familiar
with all this I forgot about that. That works, so thank you ever so much!!

Connie
 
You're right, Roger.....one sheet had it, and one didn't. You will see my
answer back to Pete. Thank you so much for responding! Connie
 
Because the person who created it for me told me it was an array formula!
But I forgot about that detail, not being familiar with this type of thing.
Hope that answers your wonderment! :) Have a nice evening.
 
Sorry, I didn't realize you were asking Roger!! My head is too fuzzy
tonight!! Please excuse me!! Good night! Connie
 
Dan,

there are many constructs like

AVERAGE(IF(range_condition, range))
MAX(IF(range_condition, range))
SUM(IF(range_condition, range))

etc. which point to the formula being an array formula.

Hope this helps.

Pete
 
Thanks for returning the context and for your great explanation.

I learned two things here!

Dan
 
Back
Top