Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Key for Calculating Average? | Excel Worksheet Functions | |||
Simple average question | New Users to Excel | |||
Another Average non-adjacent cells question... | Excel Discussion (Misc queries) | |||
Average If Question | Excel Discussion (Misc queries) | |||
Multicell Array Formula and List Question | Excel Worksheet Functions |