IF
 

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

IF
 

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

IF
 

Hi Connie

the formula is an array formula and should be entered or edited using
Control+****+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

--
Regards
Roger Govier

"Connie Martin" wrote in message
...
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

IF
 

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

"Pete_UK" wrote:

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

IF
 

Hi Roger,

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

Dan

IF
 

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

Pete

On Aug 29, 12:52*am, Connie Martin
wrote:
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

IF
 

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

"Roger Govier" wrote:

Hi Connie

the formula is an array formula and should be entered or edited using
Control+****+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

--
Regards
Roger Govier

"Connie Martin" wrote in message
...
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

IF
 

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.

"dan dungan" wrote:

Hi Roger,

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

Dan

IF
 

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

"dan dungan" wrote:

Hi Roger,

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

Dan

IF
 

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

On Aug 29, 12:52*am, dan dungan wrote:
Hi Roger,

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

Dan

IF
 

dan dungan wrote:

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.

IF
 

Thanks, Pete

IF
 

Thanks for returning the context and for your great explanation.

I learned two things here!

Dan

On Aug 28, 7:58*pm, smartin wrote:
dan dungan wrote:
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.