ExcelBanter - Averages using array.

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Averages using array. (https://www.excelbanter.com/excel-discussion-misc-queries/127847-averages-using-array.html)

LostwithoutLost

Averages using array.

Hi there,

I am using the following array formula to work out an average of a row of
cells that contain numerical data. i,e not an empty cell and the value isnt 0.

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

It works fine for empty cells, but it does not seem to ignore cells that
contain "0" or "0:00" any ideas???

Bob Phillips

Averages using array.

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

--
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 array formula to work out an average of a row of
cells that contain numerical data. i,e not an empty cell and the value
isnt 0.

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

It works fine for empty cells, but it does not seem to ignore cells that
contain "0" or "0:00" any ideas???

LostwithoutLost

Averages using array.

Fantastic,

Thankyou very much.

"Bob Phillips" wrote:

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

--
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 array formula to work out an average of a row of
cells that contain numerical data. i,e not an empty cell and the value
isnt 0.

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

It works fine for empty cells, but it does not seem to ignore cells that
contain "0" or "0:00" any ideas???

All times are GMT +1. The time now is 04:36 AM.