How do you average if your data contains a 0?

[Guest]

Have set up a range named DataOpen. Within the range are numbers & 0. Do not
want to average the cells that contain 0's

Thank You!

 

[Alan Perkins]

Hi ras - enter this as an array formula (use ctrl+shift+enter instead of
just enter)
=AVERAGE(IF(A1:A9<>0,A1:A9,""))


Alan P.

 

[Alan Perkins]

OOPS - firgot to mention "assuming your data's in a1:a9"

Alan P.

 

[Guest]

Hi Alan

Tried and performed as written-It works-But if I try to assign a range name
to the A:1:A9 it doesn' work. What am I doing wrong?

Thanks for your quick reply

 

[Guest]

Hi Alan,

It's ras again! Discovered my problem. When I was using the range name
OpenData within the regular average function I had to place quotes around
OpenData otherwise I got a #VALUE error. Apparently by entering it as an
array formula as recommended by you the range name does not require the
quotes. It now works...Thanks Again!

 

[Bob Phillips]

It works fine with a named range.

It is an array formula, so you have to commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Alan Perkins]

It's a pleasure

Alan P.

Hi Alan,

It's ras again! Discovered my problem. When I was using the range name
OpenData within the regular average function I had to place quotes around
OpenData otherwise I got a #VALUE error. Apparently by entering it as an
array formula as recommended by you the range name does not require the
quotes. It now works...Thanks Again!

 

[Sandy Mann]

Hi ras711,

Well it doesn't work with me (Excel 97 SP2 on Windows XP Home)

=AVERAGE(IF(OpenData<>0,OpenData,"")) array entered returns the correct
answer as Bob said but

=AVERAGE(IF("OpenData"<>0,"OpenData","")) returns #VALUE! whether array or
straight entered and

=AVERAGE(IF("OpenData"<>0,OpenData,"")) does return a number but it is the
average INCLUDING zeros.

Exactly what formula are you using?

Regards

Sandy

 

[Guest]

Sandy,

This is the formula that I am using in Xcel 2003:

=AVERAGE(IF(OpenData<>0,OpenData,""))

Did you create a range name?

If not create one by hi-liting the cells you wish to average then in the
upper left where you see the current cell number enter over the cell number
OpenData as the
range name.

Then use formula above -Unless there is a difference between 97 & 2003 it
should work.

Hope this helps!

ras711

 

[Sandy Mann]

mmmmmm....... this is odd.

I entered numbers 1 - 9 in cells A1:A9 and put zero in A10 and created a
named range of these cells of OpenData.

With your formula I get #VALUE!. I swapped the 9 & 0 around so that the
zero was not the last entry in the range and then I got 4.5 which is the
average including the zero - not what you wanted. Array enter the formula
and it returned 5 - the answer you want.

Is it not the same in XL2003?

Regards

Sandy

 

[Guest]

Sorry Sandy I forgot to be the array brackets

{=AVERAGE(IF(OpenData<>0,OpenData,""))}

Array does yield the answer I was looking for.

Thanks!

 

[Sandy Mann]

Sorry Sandy I forgot to be the array brackets

{=AVERAGE(IF(OpenData<>0,OpenData,""))}

Array does yield the answer I was looking for.

I must have misundestood what you were saying, I thought that you were
saying that you managed to get the formula to work by simply enclosing the
range name in quotes:

It's ras again! Discovered my problem. When I was using the range name
OpenData within the regular average function I had to place quotes around
OpenData otherwise I got a #VALUE error.

Regards

Sandy

 

[Bob Phillips]

He said that he had to put quotes around a range name in a regular AVERAGE
function. Weird?

Bob

 

[Guest]

It's the ctrl+shift+enter that's the secret! THANKS! (What, exactly, does
that do??)

 

[Max]

It's the ctrl+shift+enter that's the secret!
What, exactly, does that do ??

:
enter this as an array formula
(use ctrl+shift+enter instead of just enter)
=AVERAGE(IF(A1:A9<>0,A1:A9,""))

Because the suggested formula is an array formula, and array formulas need
to be confirmed in this manner: CTRL+SHIFT+ENTER
(instead of just pressing ENTER), as Alan suggested

After confirming it correctly, if you look closely in the formula bar,
the entire formula will appear within curly braces {...}
These curly braces are inserted by Excel
(we don't enter these braces manually)

And the CTRL+SHIFT+ENTER needs to be re-done each time to re-enter correctly
the array formula, if the formula is subsequently edited.