R
ras711
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!
want to average the cells that contain 0's
Thank You!
A
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.
just enter)
=AVERAGE(IF(A1:A9<>0,A1:A9,""))
Alan P.
A
Alan Perkins
OOPS - firgot to mention "assuming your data's in a1:a9" 
Alan P.
Alan P.
R
ras711
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
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
R
ras711
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!
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!
B
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)
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)
A
Alan Perkins
It's a pleasure
Alan P.
Alan P.
ras711 said:
S
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
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
R
ras711
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
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
S
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
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
R
ras711
Sorry Sandy I forgot to be the array brackets
{=AVERAGE(IF(OpenData<>0,OpenData,""))}
Array does yield the answer I was looking for.
Thanks!
{=AVERAGE(IF(OpenData<>0,OpenData,""))}
Array does yield the answer I was looking for.
Thanks!
S
Sandy Mann
ras711 said:
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:
Regards
Sandy
B
Bob Phillips
He said that he had to put quotes around a range name in a regular AVERAGE
function. Weird?
Bob
function. Weird?
Bob
R
ruralkansas
It's the ctrl+shift+enter that's the secret! THANKS! (What, exactly, does
that do??)
that do??)
M
Max
ruralkansas said:
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.