Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate average from non contiguous column, ignoring (0) values
I'm using Office 2003. I have a need to have 0 in a range of cells (instead
of blank). I need to create an average from some non contiguous cells. I want the average to ignore cells containing a 0. I can have the formula | =IF(B2=0,"",B2) | and if b2 is 0 then the formula returns a blank, and if b2 is < 0 then formula returns value from b2. If I embed this If formula into an Average formula | =AVERAGE(IF(B2=0,"",B2),B9,B16,B23) | it returns a #Value error. How can I create an Average formula that ignores cells with 0 value? -- Jesse |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate average from non contiguous column, ignoring (0) values
Try this array formula** :
=AVERAGE(IF((MOD(ROW(A2:A23)-ROW(A2),7)=0)*(A2:A23<0),A2:A23)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Jesse" wrote in message ... I'm using Office 2003. I have a need to have 0 in a range of cells (instead of blank). I need to create an average from some non contiguous cells. I want the average to ignore cells containing a 0. I can have the formula | =IF(B2=0,"",B2) | and if b2 is 0 then the formula returns a blank, and if b2 is < 0 then formula returns value from b2. If I embed this If formula into an Average formula | =AVERAGE(IF(B2=0,"",B2),B9,B16,B23) | it returns a #Value error. How can I create an Average formula that ignores cells with 0 value? -- Jesse |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate average from non contiguous column, ignoring (0) values
Jesse wrote...
.... . . . I have a need to have 0 in a range of cells (instead of blank). . . . .... There could be alternatives. How can I create an Average formula that ignores cells with 0 value? But in case there aren't alternatives, the most general way would be =SUM((B2,B9,B16,B23))/INDEX(FREQUENCY((B2,B9,B16,B23),{0}),2) If your value cells were always every 7th row beginning with row 2, you could use an array formula like =AVERAGE(IF(MOD(ROW(B2:B23),7)=2,IF(B2:B230,B2:B2 3))) If you don't know what an array formula is, look for it in online Help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate average from non contiguous column, ignoring (0) values
Hi,
Since the cells are evenly spaced apart try this array formula: =AVERAGE(IF(B2:B23*(MOD(ROW(B2:B23),7)=2)<0,B2:B2 3,"")) array - press Shift+Ctrl+Enter to enter the formula -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Jesse" wrote: I'm using Office 2003. I have a need to have 0 in a range of cells (instead of blank). I need to create an average from some non contiguous cells. I want the average to ignore cells containing a 0. I can have the formula | =IF(B2=0,"",B2) | and if b2 is 0 then the formula returns a blank, and if b2 is < 0 then formula returns value from b2. If I embed this If formula into an Average formula | =AVERAGE(IF(B2=0,"",B2),B9,B16,B23) | it returns a #Value error. How can I create an Average formula that ignores cells with 0 value? -- Jesse |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Contiguous Values in a Column? | Excel Discussion (Misc queries) | |||
average of different cells ignoring zero values | Excel Discussion (Misc queries) | |||
how to calculate average value in a row ignoring values below "x" | Excel Discussion (Misc queries) | |||
Calculate the average of numbers, ignoring multiple values | Excel Discussion (Misc queries) | |||
average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions |