Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
(Excel 2003)
Trying to find the % change (up or down) for the following data series. That data includes zeros and effects my formula Example: Column [b] is 2006 Column [C] is 2007 Column [D] would be the formula column showing % Change if Column [C] is < Column [b] then % would be negative if [C] is = [D] then no change or zero. CELL = [A1],[B1],[c1] DATA: SalespesonA,0,5 SaespersonB,4,6 SalespersonC,6,0 SalespersonD,8,3 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
One way:
D1: =IF(B1<0,B1/C1-1,"N/A") Format as a percentage. In article , dj479794 wrote: (Excel 2003) Trying to find the % change (up or down) for the following data series. That data includes zeros and effects my formula Example: Column [b] is 2006 Column [C] is 2007 Column [D] would be the formula column showing % Change if Column [C] is < Column [b] then % would be negative if [C] is = [D] then no change or zero. CELL = [A1],[B1],[c1] DATA: SalespesonA,0,5 SaespersonB,4,6 SalespersonC,6,0 SalespersonD,8,3 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
In general your formula should be =(C1-B1)/B1, but of course the problem
arises when you are starting from zero as with salesperson A. You may wish to use something like: =IF(B1=0,"infinite increase",(C1-B1)/B1) -- David Biddulph "dj479794" wrote in message ... (Excel 2003) Trying to find the % change (up or down) for the following data series. That data includes zeros and effects my formula Example: Column [b] is 2006 Column [C] is 2007 Column [D] would be the formula column showing % Change if Column [C] is < Column [b] then % would be negative if [C] is = [D] then no change or zero. CELL = [A1],[B1],[c1] DATA: SalespesonA,0,5 SaespersonB,4,6 SalespersonC,6,0 SalespersonD,8,3 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
Percent change would be
(D - C)/C Percent change is not valid when you start with a zero in column C. "dj479794" wrote: (Excel 2003) Trying to find the % change (up or down) for the following data series. That data includes zeros and effects my formula Example: Column [b] is 2006 Column [C] is 2007 Column [D] would be the formula column showing % Change if Column [C] is < Column [b] then % would be negative if [C] is = [D] then no change or zero. CELL = [A1],[B1],[c1] DATA: SalespesonA,0,5 SaespersonB,4,6 SalespersonC,6,0 SalespersonD,8,3 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
=if(or(C1=0,C1=""),"",(c1-B1)/c1)
format as percent "dj479794" wrote: (Excel 2003) Trying to find the % change (up or down) for the following data series. That data includes zeros and effects my formula Example: Column [b] is 2006 Column [C] is 2007 Column [D] would be the formula column showing % Change if Column [C] is < Column [b] then % would be negative if [C] is = [D] then no change or zero. CELL = [A1],[B1],[c1] DATA: SalespesonA,0,5 SaespersonB,4,6 SalespersonC,6,0 SalespersonD,8,3 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
Oops, fumble fingered again today:
D1: =IF(B1<0, C1/B1-1, "N/A") In article , JE McGimpsey wrote: One way: D1: =IF(B1<0,B1/C1-1,"N/A") Format as a percentage. In article , dj479794 wrote: (Excel 2003) Trying to find the % change (up or down) for the following data series. That data includes zeros and effects my formula Example: Column [b] is 2006 Column [C] is 2007 Column [D] would be the formula column showing % Change if Column [C] is < Column [b] then % would be negative if [C] is = [D] then no change or zero. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
Your formula works. Thanks. One concern. when there is data like:
2006 = 0 2007 = 4 It shows an increase of 100%. Should it not be 400% assuming all data must be a whole number. "JE McGimpsey" wrote: Oops, fumble fingered again today: D1: =IF(B1<0, C1/B1-1, "N/A") In article , JE McGimpsey wrote: One way: D1: =IF(B1<0,B1/C1-1,"N/A") Format as a percentage. In article , dj479794 wrote: (Excel 2003) Trying to find the % change (up or down) for the following data series. That data includes zeros and effects my formula Example: Column [b] is 2006 Column [C] is 2007 Column [D] would be the formula column showing % Change if Column [C] is < Column [b] then % would be negative if [C] is = [D] then no change or zero. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
% Change Formula without #DIV/0!
No, if you start with 0, the formula
=IF(B1<0, C1/B1-1,"N/A") will return "N/A", because the % increase is, by definition, undefined. It's not 400%, because 2006 = 0.8 2007 = 4 is a 400% increase (e.g., 0.8 + 4*0.8). Put another way, what number would you multiply by zero to get 4? That (if it existed) would be the % increase... In article , dj479794 wrote: Your formula works. Thanks. One concern. when there is data like: 2006 = 0 2007 = 4 It shows an increase of 100%. Should it not be 400% assuming all data must be a whole number. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i change a formula to its value | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Tex Formula Does not change. | Excel Discussion (Misc queries) | |||
want to copy formula, only change one number in formula | Excel Worksheet Functions | |||
How does Data Validation change with a formula change? | Excel Worksheet Functions |