Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
don't show #DIV/0 error in cell
I'm using the following formula, which is GREAT, but I would like to alter it
to when Col F & Col C are 0 there is not #DIV/0 shown in the cell. =SUMIF(F13:F51,"IN",C13:C5)/COUNTIF(F13:F51,"IN") Same thing for the formula =IF(M3=0,"",+M3/K7*100) or =INT(K1) Again, GREAT formulas, but when M3 and K7 are both 0, I'd like no error message in cell as well as when K1 is 0 This is probably a very easy fix, I just can't get it right. Thanks in advance. Christy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
don't show #DIV/0 error in cell
You could try something like this:
=if(iserror(Your calculation),NA(),Your Calculation) HTH, Barb Reinhardt "Christy" wrote: I'm using the following formula, which is GREAT, but I would like to alter it to when Col F & Col C are 0 there is not #DIV/0 shown in the cell. =SUMIF(F13:F51,"IN",C13:C5)/COUNTIF(F13:F51,"IN") Same thing for the formula =IF(M3=0,"",+M3/K7*100) or =INT(K1) Again, GREAT formulas, but when M3 and K7 are both 0, I'd like no error message in cell as well as when K1 is 0 This is probably a very easy fix, I just can't get it right. Thanks in advance. Christy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
don't show #DIV/0 error in cell
For the first formula (looks familiar !!) it is when the COUNTIF term
is zero that you will get #DIV/0 errors, so amend it like this: =IF(COUNTIF(F13:F51,"IN")=0,0,SUMIF(F13:F51,"IN",C 13:C5)/ COUNTIF(F13:F51,"IN")) This will put 0 in the cell - if you want the cell to look blank then change the ,0, to ,"", in the middle. For your second formula it is when K7 is zero that you will get this error, so you can avoid it like this: =IF(OR(M3=0,K7=0),"",M3/K7*100)) For your third formula, try this: =IF(ISNUMBER(K1),INT(K1),"") Hope this helps. Pete On Aug 11, 12:10 am, Christy wrote: I'm using the following formula, which is GREAT, but I would like to alter it to when Col F & Col C are 0 there is not #DIV/0 shown in the cell. =SUMIF(F13:F51,"IN",C13:C5)/COUNTIF(F13:F51,"IN") Same thing for the formula =IF(M3=0,"",+M3/K7*100) or =INT(K1) Again, GREAT formulas, but when M3 and K7 are both 0, I'd like no error message in cell as well as when K1 is 0 This is probably a very easy fix, I just can't get it right. Thanks in advance. Christy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
don't show #DIV/0 error in cell
Pete, Thanks again. Everything woked like a charm!
Christy "Pete_UK" wrote: For the first formula (looks familiar !!) it is when the COUNTIF term is zero that you will get #DIV/0 errors, so amend it like this: =IF(COUNTIF(F13:F51,"IN")=0,0,SUMIF(F13:F51,"IN",C 13:C5)/ COUNTIF(F13:F51,"IN")) This will put 0 in the cell - if you want the cell to look blank then change the ,0, to ,"", in the middle. For your second formula it is when K7 is zero that you will get this error, so you can avoid it like this: =IF(OR(M3=0,K7=0),"",M3/K7*100)) For your third formula, try this: =IF(ISNUMBER(K1),INT(K1),"") Hope this helps. Pete On Aug 11, 12:10 am, Christy wrote: I'm using the following formula, which is GREAT, but I would like to alter it to when Col F & Col C are 0 there is not #DIV/0 shown in the cell. =SUMIF(F13:F51,"IN",C13:C5)/COUNTIF(F13:F51,"IN") Same thing for the formula =IF(M3=0,"",+M3/K7*100) or =INT(K1) Again, GREAT formulas, but when M3 and K7 are both 0, I'd like no error message in cell as well as when K1 is 0 This is probably a very easy fix, I just can't get it right. Thanks in advance. Christy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
don't show #DIV/0 error in cell
Glad to hear it - thanks for feeding back.
Pete On Aug 11, 1:08 am, Christy wrote: Pete, Thanks again. Everything woked like a charm! Christy "Pete_UK" wrote: For the first formula (looks familiar !!) it is when the COUNTIF term is zero that you will get #DIV/0 errors, so amend it like this: =IF(COUNTIF(F13:F51,"IN")=0,0,SUMIF(F13:F51,"IN",C 13:C5)/ COUNTIF(F13:F51,"IN")) This will put 0 in the cell - if you want the cell to look blank then change the ,0, to ,"", in the middle. For your second formula it is when K7 is zero that you will get this error, so you can avoid it like this: =IF(OR(M3=0,K7=0),"",M3/K7*100)) For your third formula, try this: =IF(ISNUMBER(K1),INT(K1),"") Hope this helps. Pete On Aug 11, 12:10 am, Christy wrote: I'm using the following formula, which is GREAT, but I would like to alter it to when Col F & Col C are 0 there is not #DIV/0 shown in the cell. =SUMIF(F13:F51,"IN",C13:C5)/COUNTIF(F13:F51,"IN") Same thing for the formula =IF(M3=0,"",+M3/K7*100) or =INT(K1) Again, GREAT formulas, but when M3 and K7 are both 0, I'd like no error message in cell as well as when K1 is 0 This is probably a very easy fix, I just can't get it right. Thanks in advance. Christy- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error message - PivotTable will not fit on sheet. Show as much as | Excel Worksheet Functions | |||
Pivot Table Show Top 10 Error | Excel Discussion (Misc queries) | |||
format cell to show leading zeros and make cell a three digit fiel | Excel Worksheet Functions | |||
Formatting to show/not show "DUE" and "RECEIVED" in cell | New Users to Excel | |||
enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions |