| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average
When I use the average function, I want to ignore values like $0.00, #DIV/0!.
How do I do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average
If you don't want to include zero it doesn't make any sense to include
negative values and then this will work =SUMIF(A2:A10,"0",A2:A10)/COUNTIF(A2:A20,"0") although it is advisable to correct the div error in it's source like =IF(D2=0,"",C2/D2) -- Regards, Peo Sjoblom "Mike" wrote in message ... When I use the average function, I want to ignore values like $0.00, #DIV/0!. How do I do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
=AVERAGE(IF(ISNUMBER(IF(rng<0,rng)),rng)) where rng is your range of interest. In article , Mike wrote: When I use the average function, I want to ignore values like $0.00, #DIV/0!. How do I do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average
This answer was extremely helpful. I didn't have to use the average
function, because I cleared up this issues the division issues. Thank you for being so attentive and detailed. "Peo Sjoblom" wrote: If you don't want to include zero it doesn't make any sense to include negative values and then this will work =SUMIF(A2:A10,"0",A2:A10)/COUNTIF(A2:A20,"0") although it is advisable to correct the div error in it's source like =IF(D2=0,"",C2/D2) -- Regards, Peo Sjoblom "Mike" wrote in message ... When I use the average function, I want to ignore values like $0.00, #DIV/0!. How do I do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
| average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Linear Mode
