|
Percentages
Hi,
There is probably a very simple solution to this but I'll ask anyway! I'd like a simple percentage formula that takes into account all eventualities ie zero amounts, negative and positive amounts and doesn't return a DIV0#. The data looks something like this A1 - 0 B1 - (255) A2 - (255) B2 - (111) A3 - 155 B3 - 75 etc I've started something that has become a string of IF and ANDs, there must be something easier!. Any help gratefully appreciated. |
Percentages
Hi David
Does this achieve what you are looking for =IF(A1=0,0,(B1-A1)/A1)*SIGN(A1) -- Regards Roger Govier "David" wrote in message ... Hi, There is probably a very simple solution to this but I'll ask anyway! I'd like a simple percentage formula that takes into account all eventualities ie zero amounts, negative and positive amounts and doesn't return a DIV0#. The data looks something like this A1 - 0 B1 - (255) A2 - (255) B2 - (111) A3 - 155 B3 - 75 etc I've started something that has become a string of IF and ANDs, there must be something easier!. Any help gratefully appreciated. |
Percentages
If cell B1 contains the divisor I would simply do the following if
=if(B1 = 0,100,"enter your normal formula here") If the divisor is 0, then the percenttage is very close to 100%. "David" wrote: Hi, There is probably a very simple solution to this but I'll ask anyway! I'd like a simple percentage formula that takes into account all eventualities ie zero amounts, negative and positive amounts and doesn't return a DIV0#. The data looks something like this A1 - 0 B1 - (255) A2 - (255) B2 - (111) A3 - 155 B3 - 75 etc I've started something that has become a string of IF and ANDs, there must be something easier!. Any help gratefully appreciated. |
Percentages
Roger was short changed for coffee this morning and dropped a few
parentheses =IF(A1=0,0,((B1-A1)/A1)*SIGN(A1)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Roger Govier" wrote in message ... Hi David Does this achieve what you are looking for =IF(A1=0,0,(B1-A1)/A1)*SIGN(A1) -- Regards Roger Govier "David" wrote in message ... Hi, There is probably a very simple solution to this but I'll ask anyway! I'd like a simple percentage formula that takes into account all eventualities ie zero amounts, negative and positive amounts and doesn't return a DIV0#. The data looks something like this A1 - 0 B1 - (255) A2 - (255) B2 - (111) A3 - 155 B3 - 75 etc I've started something that has become a string of IF and ANDs, there must be something easier!. Any help gratefully appreciated. |
Percentages
Hi Bernard
Definitely right about the shortage of caffeine this morning. However, I don't think the extra parentheses makes any difference. I had used "" as the return to 0 value in A1, and that gave me an error because of multiplying by SIGN, but changing it to 0 resolved the problem. Your extra parentheses do allow the use of Null (if required) rather than 0 as in =IF(A1=0,"",((B1-A1))*SIGN(A1)) -- Regards Roger Govier "Bernard Liengme" wrote in message ... Roger was short changed for coffee this morning and dropped a few parentheses =IF(A1=0,0,((B1-A1)/A1)*SIGN(A1)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Roger Govier" wrote in message ... Hi David Does this achieve what you are looking for =IF(A1=0,0,(B1-A1)/A1)*SIGN(A1) -- Regards Roger Govier "David" wrote in message ... Hi, There is probably a very simple solution to this but I'll ask anyway! I'd like a simple percentage formula that takes into account all eventualities ie zero amounts, negative and positive amounts and doesn't return a DIV0#. The data looks something like this A1 - 0 B1 - (255) A2 - (255) B2 - (111) A3 - 155 B3 - 75 etc I've started something that has become a string of IF and ANDs, there must be something easier!. Any help gratefully appreciated. |
Percentages
=IF(A1=0,"",((B1-A1))*SIGN(A1))
should of course be =IF(A1=0,"",((B1-A1)/A1)*SIGN(A1)) (hit delete accidentally when the spellchecker suggested repetition of A1) Now where's that coffee percolator?<bg -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bernard Definitely right about the shortage of caffeine this morning. However, I don't think the extra parentheses makes any difference. I had used "" as the return to 0 value in A1, and that gave me an error because of multiplying by SIGN, but changing it to 0 resolved the problem. Your extra parentheses do allow the use of Null (if required) rather than 0 as in =IF(A1=0,"",((B1-A1))*SIGN(A1)) -- Regards Roger Govier "Bernard Liengme" wrote in message ... Roger was short changed for coffee this morning and dropped a few parentheses =IF(A1=0,0,((B1-A1)/A1)*SIGN(A1)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Roger Govier" wrote in message ... Hi David Does this achieve what you are looking for =IF(A1=0,0,(B1-A1)/A1)*SIGN(A1) -- Regards Roger Govier "David" wrote in message ... Hi, There is probably a very simple solution to this but I'll ask anyway! I'd like a simple percentage formula that takes into account all eventualities ie zero amounts, negative and positive amounts and doesn't return a DIV0#. The data looks something like this A1 - 0 B1 - (255) A2 - (255) B2 - (111) A3 - 155 B3 - 75 etc I've started something that has become a string of IF and ANDs, there must be something easier!. Any help gratefully appreciated. |
Percentages
Thanks for you help R B and J. The problem is solved.
Enjoy your coffee!!! David "Roger Govier" wrote: =IF(A1=0,"",((B1-A1))*SIGN(A1)) should of course be =IF(A1=0,"",((B1-A1)/A1)*SIGN(A1)) (hit delete accidentally when the spellchecker suggested repetition of A1) Now where's that coffee percolator?<bg -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Bernard Definitely right about the shortage of caffeine this morning. However, I don't think the extra parentheses makes any difference. I had used "" as the return to 0 value in A1, and that gave me an error because of multiplying by SIGN, but changing it to 0 resolved the problem. Your extra parentheses do allow the use of Null (if required) rather than 0 as in =IF(A1=0,"",((B1-A1))*SIGN(A1)) -- Regards Roger Govier "Bernard Liengme" wrote in message ... Roger was short changed for coffee this morning and dropped a few parentheses =IF(A1=0,0,((B1-A1)/A1)*SIGN(A1)) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Roger Govier" wrote in message ... Hi David Does this achieve what you are looking for =IF(A1=0,0,(B1-A1)/A1)*SIGN(A1) -- Regards Roger Govier "David" wrote in message ... Hi, There is probably a very simple solution to this but I'll ask anyway! I'd like a simple percentage formula that takes into account all eventualities ie zero amounts, negative and positive amounts and doesn't return a DIV0#. The data looks something like this A1 - 0 B1 - (255) A2 - (255) B2 - (111) A3 - 155 B3 - 75 etc I've started something that has become a string of IF and ANDs, there must be something easier!. Any help gratefully appreciated. |
| All times are GMT +1. The time now is 11:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com