Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
How do I average a column of numbers and exclude cells with zero values?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
Hi!
Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A20<0,A1:A20)) Biff "Stephen" wrote in message ... How do I average a column of numbers and exclude cells with zero values? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
Biff wrote...
Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A20<0,A1:A20)) .... As I never tire of pointing out, better to use =AVERAGE(IF(rng0,rng)) because if values could be negative as well as positive, then legitimate values could also be zero, so the only time it make sense (mathematically) to exclude zeros is when all values should be only positive or only negative. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
=AVERAGE(IF(H2:H132<0,H2:H132)) is the function I wrote from your example
and I am still getting an error. Is there anything wrong with the function I have typed? Stephen "Biff" wrote: Hi! Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A20<0,A1:A20)) Biff "Stephen" wrote in message ... How do I average a column of numbers and exclude cells with zero values? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
What does "rng" stand for in this function?
Stephen "Harlan Grove" wrote: Biff wrote... Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =AVERAGE(IF(A1:A20<0,A1:A20)) .... As I never tire of pointing out, better to use =AVERAGE(IF(rng0,rng)) because if values could be negative as well as positive, then legitimate values could also be zero, so the only time it make sense (mathematically) to exclude zeros is when all values should be only positive or only negative. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
Stephen wrote...
=AVERAGE(IF(H2:H132<0,H2:H132)) is the function I wrote from your example and I am still getting an error. Is there anything wrong with the function I have typed? .... "Biff" wrote: .... Entered as an array using the key combination of CTRL,SHIFT,ENTER: .... It needs to be an array formula. You don't just type it an press [Enter], you type it, hold down [Ctrl] and [Shift] keys and press [Enter]. If the error is #VALUE!, then the odds are you didn't enter it as an array formula. If the error is #DIV/0!, then the odds are there are no nonzero numbers in H2:H132. If the error is anything else, then you have that error value in at least one cell in H2:H132. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
Stephen wrote...
What does "rng" stand for in this function? .... It's a placeholder for whatever range address you want to use. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average function
If I may be permitted...... The "rng" in Harlan's formula is simply "range", i.e. in your case H2:H132 Following Harlan's argument I assume you have only positive values and zeroes and you can therefore use =AVERAGE(IF(H2:H1320,H2:H132)) This formula needs to be confirmed with CTRL+SHIFT+ENTER (as Biff says) so that curly braces appear around the formula in the formula bar, alternatively you can use a formula which just requires ENTER =SUM(H2:H132)/MAX(1,COUNTIF(H2:H132,"0")) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=536675 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Figuring daily average...function ??? | Excel Worksheet Functions | |||
AVERAGE function returns #DIV/0! error | Excel Discussion (Misc queries) | |||
Average function assistance | Excel Discussion (Misc queries) | |||
EXCEL 2000 AVERAGE function | Excel Worksheet Functions | |||
Using the average function | Excel Worksheet Functions |