| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations resulting in #DIV/0!
The following example is:
a1 0% a2 0% a3 0% a4 0% a5 0% a6 0% Here's what I have: =sum(a1:a6)/countif(a1:a6,"0") I have multiple cells using this formual that have numbers and function fine. But, with no numbers to calculate against I'm getting the #DIV/0!. How can I fix this so I don't have the #DIV/0!? I've been reading about =IF but I've tried numerous ways and have come up with nothing working. Thanks for your help. Debbie |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculations resulting in #DIV/0!
=if(countif(a1:a6,"0")=0,"",sum(a1:a6)/countif(a1:a6,"0"))
-- Gary''s Student - gsnu200770 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculations resulting in #DIV/0!
=IF(COUNTIF(A1:A6,"0",sum(a1:a6)/countif(a1:a6,"0"),"Not data")
or =IF(ISERRROR(sum(a1:a6)/countif(a1:a6,"0")),"Problem",sum(a1:a6)/countif(a1:a6,"0")) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Debbie" wrote in message ... The following example is: a1 0% a2 0% a3 0% a4 0% a5 0% a6 0% Here's what I have: =sum(a1:a6)/countif(a1:a6,"0") I have multiple cells using this formual that have numbers and function fine. But, with no numbers to calculate against I'm getting the #DIV/0!. How can I fix this so I don't have the #DIV/0!? I've been reading about =IF but I've tried numerous ways and have come up with nothing working. Thanks for your help. Debbie |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculations resulting in #DIV/0!
Typo
=IF(COUNTIF(A1:A6,"0")0,sum(a1:a6)/countif(a1:a6,"0"),"Not data") -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernard Liengme" wrote in message ... =IF(COUNTIF(A1:A6,"0",sum(a1:a6)/countif(a1:a6,"0"),"Not data") or =IF(ISERRROR(sum(a1:a6)/countif(a1:a6,"0")),"Problem",sum(a1:a6)/countif(a1:a6,"0")) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Debbie" wrote in message ... The following example is: a1 0% a2 0% a3 0% a4 0% a5 0% a6 0% Here's what I have: =sum(a1:a6)/countif(a1:a6,"0") I have multiple cells using this formual that have numbers and function fine. But, with no numbers to calculate against I'm getting the #DIV/0!. How can I fix this so I don't have the #DIV/0!? I've been reading about =IF but I've tried numerous ways and have come up with nothing working. Thanks for your help. Debbie |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculations resulting in #DIV/0!
"Bernard Liengme" wrote...
.... =IF(COUNTIF(A1:A6,"0")0,sum(a1:a6)/countif(a1:a6,"0"),"Not data") .... An alternative approach, =SUM(A1:A6)/MAX(1,COUNTIF(A1:A6,"0")) which would return 0 if none of the cells in A1:A6 contained a positive number, but since the COUNTIF criteria implies only positive values would be valid, then the average of any set of positive numbers would be positive, so a 0 result would be an unambiguous indication that the average wasn't valid. Possible at this point to use a custom number format, e.g., [<=0]"no valid data";General |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculations resulting in #DIV/0!
Thanks everyone, for your quick responses.
This helps me complete my calculations. Much appreciated! Debbie "Harlan Grove" wrote: "Bernard Liengme" wrote... .... =IF(COUNTIF(A1:A6,"0")0,sum(a1:a6)/countif(a1:a6,"0"),"Not data") .... An alternative approach, =SUM(A1:A6)/MAX(1,COUNTIF(A1:A6,"0")) which would return 0 if none of the cells in A1:A6 contained a positive number, but since the COUNTIF criteria implies only positive values would be valid, then the average of any set of positive numbers would be positive, so a 0 result would be an unambiguous indication that the average wasn't valid. Possible at this point to use a custom number format, e.g., [<=0]"no valid data";General |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculations resulting in #DIV/0!
Very nice!
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Harlan Grove" wrote in message ... "Bernard Liengme" wrote... ... =IF(COUNTIF(A1:A6,"0")0,sum(a1:a6)/countif(a1:a6,"0"),"Not data") ... An alternative approach, =SUM(A1:A6)/MAX(1,COUNTIF(A1:A6,"0")) which would return 0 if none of the cells in A1:A6 contained a positive number, but since the COUNTIF criteria implies only positive values would be valid, then the average of any set of positive numbers would be positive, so a 0 result would be an unambiguous indication that the average wasn't valid. Possible at this point to use a custom number format, e.g., [<=0]"no valid data";General |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula does not display the resulting value in the cell | New Users to Excel | |||
| Excel adds significant digits, resulting in errors in calculations | Excel Worksheet Functions | |||
| Formula to convert resulting #N/A to 0 | Excel Worksheet Functions | |||
| Formula resulting in 0 | Excel Discussion (Misc queries) | |||
| sumproduct resulting in #N/A | Excel Worksheet Functions | |||
Hybrid Mode
