| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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??? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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??? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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??? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use of Offset function in array formula | Excel Worksheet Functions | |||
| SUMPRODUCT clue needed | Excel Discussion (Misc queries) | |||
| I figured out how to do total averages in Pivot Charts! | Charts and Charting in Excel | |||
| Display an array of references | Excel Worksheet Functions | |||
| VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) | |||
Linear Mode
