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!