IF

[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

 

[Pete_UK]

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

 

[Roger Govier]

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

 

[Connie Martin]

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

 

[dan dungan]

Hi Roger,

I'm wondering how you knew it was an array formula?

Dan

 

[Pete_UK]

You're welcome, Connie - thanks for feeding back.

Pete

 

[Connie Martin]

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

 

[Connie Martin]

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.

 

[Connie Martin]

Sorry, I didn't realize you were asking Roger!! My head is too fuzzy
tonight!! Please excuse me!! Good night! Connie

 

[Pete_UK]

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

 

[smartin]

Hi Roger,

I'm wondering how you knew it was an array formula?

Dan

Dan,

You omitted the context of the question. tsk tsk. The formula in
question was this:

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

The tip-off here is the construct

IF(F15:F19<>0

Normally, IF expects a single TRUE/FALSE value. But this fragment points
at 5 cells.

How can it do that? Normally, IF can't. But an array formula will
evaluate each result in turn.

 

[dan dungan]

Thanks, Pete

 

[dan dungan]

Thanks for returning the context and for your great explanation.

I learned two things here!

Dan