If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
"... it still gives an error" isn't very specific. You haven't told us what
formula you are using, so we can't tell you what you've done wrong. If instead of =(E34-D34)/E34 you wanted to start from =(J37-C37)/ABS(C37), then instead of =IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34)) you would end up with =IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37))) If you want the result as a percentage, format the cell with a percentage format to suit your requirements. Is that the formula you were using? If so, what input values did you have in J37 and C37, what result did you get, and what result did you expect? -- David Biddulph "kaimarja" wrote in message ... Hi David, I have the same problem as above, but my formula is slightly different: =(J37-C37)/ABS(C37). I compare two values and want to get the percentage of increase or decrease between the two. Sometimes one of the values (or both) is zero, and then it gives an error. I Tried the advice given above but I must do something wrong as it still gives an error. Thanks for helping me. Kaimarja "David Biddulph" wrote: =IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34)) Note that with the formulae which some other contributors have suggested, you would get the same answer for an input of D34=99 and E34=0 as you would get for D34 and E34 both 99. Is that what you want? -- David Biddulph "pgarcia" wrote in message ... I'm getting #DIV/0! with the following formula, the cell is format as a percent. Thanks =(E34-D34)/E34 E34 = 0 D34 = 0 Result should be 0% |
#12
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
Hi David, Thanks for the reply. Sorry for not being specific enough. Here are
the answers to your questions: I use the formula =(J37-C37)/ABS(C37), C being last years value and J being this years value. The value in C37 is 0 and the value in J37 is 70 . The result cell is formatted to give a % and in all the other result cells when the formula does not refer to cells containing 0, I get a correct answer but not in the above case, where last year's cell contains a 0. The result shows as #DIV/0! What I want is that when last year I had 0 and this year I have 70, the result of my formula should show a % of increase. I tried to copy and paste the formula you propose in your answer, but I still get an error message from excel "The formula you typed contains an error". Of course I understand I'm asking my formula to calculate a % of 0, which is illogical, but I wondered whether there was a way around it. Thanks for your help. Kai "David Biddulph" wrote: "... it still gives an error" isn't very specific. You haven't told us what formula you are using, so we can't tell you what you've done wrong. If instead of =(E34-D34)/E34 you wanted to start from =(J37-C37)/ABS(C37), then instead of =IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34)) you would end up with =IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37))) If you want the result as a percentage, format the cell with a percentage format to suit your requirements. Is that the formula you were using? If so, what input values did you have in J37 and C37, what result did you get, and what result did you expect? -- David Biddulph "kaimarja" wrote in message ... Hi David, I have the same problem as above, but my formula is slightly different: =(J37-C37)/ABS(C37). I compare two values and want to get the percentage of increase or decrease between the two. Sometimes one of the values (or both) is zero, and then it gives an error. I Tried the advice given above but I must do something wrong as it still gives an error. Thanks for helping me. Kaimarja "David Biddulph" wrote: =IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34)) Note that with the formulae which some other contributors have suggested, you would get the same answer for an input of D34=99 and E34=0 as you would get for D34 and E34 both 99. Is that what you want? -- David Biddulph "pgarcia" wrote in message ... I'm getting #DIV/0! with the following formula, the cell is format as a percent. Thanks =(E34-D34)/E34 E34 = 0 D34 = 0 Result should be 0% |
#13
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
You can't "show a % of increase", since it's mathematically undefined,
but you can show a default value: =IF(C37=0, "undefined", J37/C37 - 1) or =IF(C37=0, 1, J37/C37 - 1) In article , kaimarja wrote: Hi David, Thanks for the reply. Sorry for not being specific enough. Here are the answers to your questions: I use the formula =(J37-C37)/ABS(C37), C being last years value and J being this years value. The value in C37 is 0 and the value in J37 is 70 . The result cell is formatted to give a % and in all the other result cells when the formula does not refer to cells containing 0, I get a correct answer but not in the above case, where last year's cell contains a 0. The result shows as #DIV/0! What I want is that when last year I had 0 and this year I have 70, the result of my formula should show a % of increase. I tried to copy and paste the formula you propose in your answer, but I still get an error message from excel "The formula you typed contains an error". Of course I understand I'm asking my formula to calculate a % of 0, which is illogical, but I wondered whether there was a way around it. |
#14
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
If you copied and pasted (rather than retyped) my formula
=IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37))) and Excel reported "The formula you typed contains an error", then I can only assume that you are working with Windows Regional Options that are looking for a semi-colon, rather than a comma, as a list separator? If so, you ought to use =IF(J37-C37=0;0;IF(C37=0;"infinite % difference";(J37-C37)/ABS(C37))) If you are still getting a problem, copy and paste the formula from your formula bar to the newsgroup. You are right that a percentage of zero is illogical, hence the various suggestions for reporting the situation, such as my "infinite % difference" result. -- David Biddulph "kaimarja" wrote in message ... Hi David, Thanks for the reply. Sorry for not being specific enough. Here are the answers to your questions: I use the formula =(J37-C37)/ABS(C37), C being last years value and J being this years value. The value in C37 is 0 and the value in J37 is 70 . The result cell is formatted to give a % and in all the other result cells when the formula does not refer to cells containing 0, I get a correct answer but not in the above case, where last year's cell contains a 0. The result shows as #DIV/0! What I want is that when last year I had 0 and this year I have 70, the result of my formula should show a % of increase. I tried to copy and paste the formula you propose in your answer, but I still get an error message from excel "The formula you typed contains an error". Of course I understand I'm asking my formula to calculate a % of 0, which is illogical, but I wondered whether there was a way around it. Thanks for your help. Kai "David Biddulph" wrote: "... it still gives an error" isn't very specific. You haven't told us what formula you are using, so we can't tell you what you've done wrong. If instead of =(E34-D34)/E34 you wanted to start from =(J37-C37)/ABS(C37), then instead of =IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34)) you would end up with =IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37))) If you want the result as a percentage, format the cell with a percentage format to suit your requirements. Is that the formula you were using? If so, what input values did you have in J37 and C37, what result did you get, and what result did you expect? -- David Biddulph "kaimarja" wrote in message ... Hi David, I have the same problem as above, but my formula is slightly different: =(J37-C37)/ABS(C37). I compare two values and want to get the percentage of increase or decrease between the two. Sometimes one of the values (or both) is zero, and then it gives an error. I Tried the advice given above but I must do something wrong as it still gives an error. Thanks for helping me. Kaimarja "David Biddulph" wrote: =IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34)) Note that with the formulae which some other contributors have suggested, you would get the same answer for an input of D34=99 and E34=0 as you would get for D34 and E34 both 99. Is that what you want? -- David Biddulph "pgarcia" wrote in message ... I'm getting #DIV/0! with the following formula, the cell is format as a percent. Thanks =(E34-D34)/E34 E34 = 0 D34 = 0 Result should be 0% |
#15
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
You can pass the 0 as value.
"pgarcia" wrote: I'm getting #DIV/0! with the following formula, the cell is format as a percent. Thanks =(E34-D34)/E34 E34 = 0 D34 = 0 Result should be 0% |
#16
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
This formula is all fine and dandy to get zero... now what about when you put
numbers in those boxes... it still returns zero |
#17
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
You haven't told us which formula, so we can't help you.
If you are referring to a formula in a previous message, you need to quote enough of the previous message(s) to put your reply into context. -- David Biddulph "Jim" wrote in message ... This formula is all fine and dandy to get zero... now what about when you put numbers in those boxes... it still returns zero |
#18
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
Reading your solution to subject topic. I have a similar situation where
AO=units sold and AP=units returned. In trying to get the return rate percentage, I must divide AP by AO. However, if 0 units sold and 1 unit returned, what formula would give me the high return rate that I need to show when I can't divide by 0? I was using the formula =IF(AP2=0,0,AP2/AO2). This works for all except those with 0 in AO or units sold column. I need to see at least a 100% return rate when AO=0 and AP=1 or more. Can you help? Thank you. "JE McGimpsey" wrote: You can't "show a % of increase", since it's mathematically undefined, but you can show a default value: =IF(C37=0, "undefined", J37/C37 - 1) or =IF(C37=0, 1, J37/C37 - 1) In article , kaimarja wrote: Hi David, Thanks for the reply. Sorry for not being specific enough. Here are the answers to your questions: I use the formula =(J37-C37)/ABS(C37), C being last years value and J being this years value. The value in C37 is 0 and the value in J37 is 70 . The result cell is formatted to give a % and in all the other result cells when the formula does not refer to cells containing 0, I get a correct answer but not in the above case, where last year's cell contains a 0. The result shows as #DIV/0! What I want is that when last year I had 0 and this year I have 70, the result of my formula should show a % of increase. I tried to copy and paste the formula you propose in your answer, but I still get an error message from excel "The formula you typed contains an error". Of course I understand I'm asking my formula to calculate a % of 0, which is illogical, but I wondered whether there was a way around it. |
#19
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
You want:
=IF(AO2=0,1,AP2/AO2). Format as percent Regards, Fred. "K@MJP" wrote in message ... Reading your solution to subject topic. I have a similar situation where AO=units sold and AP=units returned. In trying to get the return rate percentage, I must divide AP by AO. However, if 0 units sold and 1 unit returned, what formula would give me the high return rate that I need to show when I can't divide by 0? I was using the formula =IF(AP2=0,0,AP2/AO2). This works for all except those with 0 in AO or units sold column. I need to see at least a 100% return rate when AO=0 and AP=1 or more. Can you help? Thank you. "JE McGimpsey" wrote: You can't "show a % of increase", since it's mathematically undefined, but you can show a default value: =IF(C37=0, "undefined", J37/C37 - 1) or =IF(C37=0, 1, J37/C37 - 1) In article , kaimarja wrote: Hi David, Thanks for the reply. Sorry for not being specific enough. Here are the answers to your questions: I use the formula =(J37-C37)/ABS(C37), C being last years value and J being this years value. The value in C37 is 0 and the value in J37 is 70 . The result cell is formatted to give a % and in all the other result cells when the formula does not refer to cells containing 0, I get a correct answer but not in the above case, where last year's cell contains a 0. The result shows as #DIV/0! What I want is that when last year I had 0 and this year I have 70, the result of my formula should show a % of increase. I tried to copy and paste the formula you propose in your answer, but I still get an error message from excel "The formula you typed contains an error". Of course I understand I'm asking my formula to calculate a % of 0, which is illogical, but I wondered whether there was a way around it. |
#20
|
|||
|
|||
Getting #DIV/0!, how to get 0%?
Hi Fred,
Thank you for your response. However, it's the AP2/AO2 where AO=0 that is the problem. I believe that I resolved the issue using =IF(AP2=0,0,IF(AO2=0,1,AP2/AO2). This seemed to result in the percentage I was looking for. Your solution was the second part of the formula I ended up using. My best. K@MJP "Fred Smith" wrote: You want: =IF(AO2=0,1,AP2/AO2). Format as percent Regards, Fred. "K@MJP" wrote in message ... Reading your solution to subject topic. I have a similar situation where AO=units sold and AP=units returned. In trying to get the return rate percentage, I must divide AP by AO. However, if 0 units sold and 1 unit returned, what formula would give me the high return rate that I need to show when I can't divide by 0? I was using the formula =IF(AP2=0,0,AP2/AO2). This works for all except those with 0 in AO or units sold column. I need to see at least a 100% return rate when AO=0 and AP=1 or more. Can you help? Thank you. "JE McGimpsey" wrote: You can't "show a % of increase", since it's mathematically undefined, but you can show a default value: =IF(C37=0, "undefined", J37/C37 - 1) or =IF(C37=0, 1, J37/C37 - 1) In article , kaimarja wrote: Hi David, Thanks for the reply. Sorry for not being specific enough. Here are the answers to your questions: I use the formula =(J37-C37)/ABS(C37), C being last years value and J being this years value. The value in C37 is 0 and the value in J37 is 70 . The result cell is formatted to give a % and in all the other result cells when the formula does not refer to cells containing 0, I get a correct answer but not in the above case, where last year's cell contains a 0. The result shows as #DIV/0! What I want is that when last year I had 0 and this year I have 70, the result of my formula should show a % of increase. I tried to copy and paste the formula you propose in your answer, but I still get an error message from excel "The formula you typed contains an error". Of course I understand I'm asking my formula to calculate a % of 0, which is illogical, but I wondered whether there was a way around it. |
Thread Tools | |
Display Modes | |
|
|