Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I need to return "" instead of 0
Hello:
My spreadsheet counts occurrences of incidents by month; I need to average the number of actual occurrences without including zero values in the average. the months row I want to average is populated by: =IF(ISBLANK(Month_Vic_2005),"",COUNTIF(Month_Vic_2 005,"1")) where Month_Vic_2005 is a named range that populates from another named range that contains dates of occurrence. (COUNTIF "1" is replaced by 2,3 4, etc. for later months in series.) I suspect the problem is that the ISBLANK function is returning FALSE because the named range in question contains the formula: =IF(ISBLANK(Vic_Incident_Date_2005),"",MONTH(Vic_I ncident_Date_2005)). Where there is no occurrence (say for April) I want the cell value blank, not zero. The zeroes are causing the average function to return a false value for the months for which I actually have data. I'm probably approaching this from the wrong end; I've tried IF(range = 0, 0, <1, is null, NOT. Tia for the help. |
#2
|
|||
|
|||
Try
=AVERAGE(If(rng<0,rng)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "dave roth" wrote in message ... Hello: My spreadsheet counts occurrences of incidents by month; I need to average the number of actual occurrences without including zero values in the average. the months row I want to average is populated by: =IF(ISBLANK(Month_Vic_2005),"",COUNTIF(Month_Vic_2 005,"1")) where Month_Vic_2005 is a named range that populates from another named range that contains dates of occurrence. (COUNTIF "1" is replaced by 2,3 4, etc. for later months in series.) I suspect the problem is that the ISBLANK function is returning FALSE because the named range in question contains the formula: =IF(ISBLANK(Vic_Incident_Date_2005),"",MONTH(Vic_I ncident_Date_2005)). Where there is no occurrence (say for April) I want the cell value blank, not zero. The zeroes are causing the average function to return a false value for the months for which I actually have data. I'm probably approaching this from the wrong end; I've tried IF(range = 0, 0, <1, is null, NOT. Tia for the help. |
#3
|
|||
|
|||
Hi:
Thanks so much; that's got the average correct. "Bob Phillips" wrote: Try =AVERAGE(If(rng<0,rng)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "dave roth" wrote in message ... Hello: My spreadsheet counts occurrences of incidents by month; I need to average the number of actual occurrences without including zero values in the average. the months row I want to average is populated by: =IF(ISBLANK(Month_Vic_2005),"",COUNTIF(Month_Vic_2 005,"1")) where Month_Vic_2005 is a named range that populates from another named range that contains dates of occurrence. (COUNTIF "1" is replaced by 2,3 4, etc. for later months in series.) I suspect the problem is that the ISBLANK function is returning FALSE because the named range in question contains the formula: =IF(ISBLANK(Vic_Incident_Date_2005),"",MONTH(Vic_I ncident_Date_2005)). Where there is no occurrence (say for April) I want the cell value blank, not zero. The zeroes are causing the average function to return a false value for the months for which I actually have data. I'm probably approaching this from the wrong end; I've tried IF(range = 0, 0, <1, is null, NOT. Tia for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return lowest quantity | Excel Worksheet Functions | |||
RETURN intersecting value with known horizotal & vertical?? | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions | |||
Return the smallest value | Excel Worksheet Functions | |||
Return the end of month date from a date | Excel Worksheet Functions |