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!

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.

"ras711" wrote in message
...
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!

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

Alan P.

"Alan Perkins" wrote in message
...
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.

"ras711" wrote in message
...
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!

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

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

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!

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

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)


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

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

It's a pleasure

Alan P.
"ras711" wrote in message
...
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!

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"ras711" wrote in message
...
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!

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

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" wrote:

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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"ras711" wrote in message
...
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!

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"ras711" wrote in message
...
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" wrote:

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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"ras711" wrote in message
...
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!

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

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" wrote:

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

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"ras711" wrote in message
...
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" wrote:

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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"ras711" wrote in message
...
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!

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

"ras711" wrote in message
...
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:

ras711" wrote in message
...

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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

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

Bob

"Sandy Mann" wrote in message
...
"ras711" wrote in message
...
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:

ras711" wrote in message
...

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
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

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

"Alan Perkins" wrote:

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.

"ras711" wrote in message
...
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!

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

"Alan Perkins" wrote:
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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--