Array average question
 

Hi there,

I am using the following formula to calculate the average of a row of cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<"",E49:IV49)),AND(IF(E49:IV 49<"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??

Array average question
 

See other post.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"LostwithoutLost" wrote in
message ...
Hi there,

I am using the following formula to calculate the average of a row of
cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<"",E49:IV49)),AND(IF(E49:IV 49<"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??

Array average question
 

=AVERAGE(IF(C22:C25<0,C22:C25))

enter as an array formula

"LostwithoutLost" wrote:

Hi there,

I am using the following formula to calculate the average of a row of cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<"",E49:IV49)),AND(IF(E49:IV 49<"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??

Array average question
 

Try this, array-entered:
=AVERAGE(IF(E49:IV490,E49:IV49))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LostwithoutLost" wrote:
Hi there,

I am using the following formula to calculate the average of a row of cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<"",E49:IV49)),AND(IF(E49:IV 49<"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??

Array average question
 

What you need is =AVERAGE(IF(E49:IV49<0,E49:IV49))
You must enter it as an array formula using CTRL+SHIFT+ENTER rather than
just ENTER. Excel will enclose the formula is curly braces {...}
You need not worry about blanks - AVERAGE is 'smart enough' to ignore them

Your formula has a number of errors
1) Wrong syntax fro AND
2) Boolean functions (AND, OR, NOT) do not work in array formulas
3) Wrong to put 0 is quotes - it is a number not text

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LostwithoutLost" wrote in
message ...
Hi there,

I am using the following formula to calculate the average of a row of
cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<"",E49:IV49)),AND(IF(E49:IV 49<"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??

Array average question
 

Hi,


Try this:

=AVERAGE(IF((E49:IV49<"")*(E49:IV49<0),E49:IV49) )

but this should work as well I think:

=AVERAGE(IF(E49:IV49<0,E49:IV49))

again enter using CSE


HTH
Jean-Guy

enter using Ctrl+Shift+Enter

HTH
Jean-Guy

"LostwithoutLost" wrote:

Hi there,

I am using the following formula to calculate the average of a row of cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<"",E49:IV49)),AND(IF(E49:IV 49<"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??

Array average question
 

Thankyou all very much.

its all working now.

Thanks

"Bernard Liengme" wrote:

What you need is =AVERAGE(IF(E49:IV49<0,E49:IV49))
You must enter it as an array formula using CTRL+SHIFT+ENTER rather than
just ENTER. Excel will enclose the formula is curly braces {...}
You need not worry about blanks - AVERAGE is 'smart enough' to ignore them

Your formula has a number of errors
1) Wrong syntax fro AND
2) Boolean functions (AND, OR, NOT) do not work in array formulas
3) Wrong to put 0 is quotes - it is a number not text

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LostwithoutLost" wrote in
message ...
Hi there,

I am using the following formula to calculate the average of a row of
cells
that are a) not empty, b) do not contain a zero value

=AVERAGE((IF(E49:IV49<"",E49:IV49)),AND(IF(E49:IV 49<"0",E49:IV49)))

It ignores the empty cells OK but not the ones that contain a zaero value.

Any ideas??