Home 
Search 
Today's Posts 
#1




Change over time fornula
Trying to calculate change % with a couple criteria. Here's what I'm doing...
Column a (labels) easydata input Column b (2004 number) easydata input, all =0 Column c (2005 number) easydata input, all =0 Column d (Change #) easy [=c2b2] conditional format red font for neg# Column e (Change %) here is the problem...I need to iclude all of these conditions. * if d2=0, then e2 should show "No Change" (or display 0%)...pretty easy so far * if there is a difference between b2 and c2, then change over time formula is ((c2b2)/b2). No problem s long as b2 is NOT zero * trouble is, if b2=0 then I get a DIV error in the change over time formula. I need any change from zero in 2004 to any number 0 in 2005 to display "100%" Would like some help creating a formula to care for this. Thank you. 
#2




Change over time fornula
In e2: =if(b2=0,1,d2/b2). Format the result as a percent.
"Trevor" wrote: Trying to calculate change % with a couple criteria. Here's what I'm doing... Column a (labels) easydata input Column b (2004 number) easydata input, all =0 Column c (2005 number) easydata input, all =0 Column d (Change #) easy [=c2b2] conditional format red font for neg# Column e (Change %) here is the problem...I need to iclude all of these conditions. * if d2=0, then e2 should show "No Change" (or display 0%)...pretty easy so far * if there is a difference between b2 and c2, then change over time formula is ((c2b2)/b2). No problem s long as b2 is NOT zero * trouble is, if b2=0 then I get a DIV error in the change over time formula. I need any change from zero in 2004 to any number 0 in 2005 to display "100%" Would like some help creating a formula to care for this. Thank you. 
#3




Change over time fornula
Disarmingly simple, it seems; works like a charm, thank you very much. I am
trying to understand what the formula does...(okay, besides the fact that it works!)...may I ask you to break it down for me? It tells me that if b2 is one or zero to divide the difference (d2) by the 2004 number (b2). Yes? "bpeltzer" wrote: In e2: =if(b2=0,1,d2/b2). Format the result as a percent. 
#4




Change over time fornula
The formula says that if b2 is 0, return 1 (100%) as the result. Otherwise,
the result is d2/b2. Bruce "Trevor" wrote: Disarmingly simple, it seems; works like a charm, thank you very much. I am trying to understand what the formula does...(okay, besides the fact that it works!)...may I ask you to break it down for me? It tells me that if b2 is one or zero to divide the difference (d2) by the 2004 number (b2). Yes? "bpeltzer" wrote: In e2: =if(b2=0,1,d2/b2). Format the result as a percent. 
#5




Change over time fornula
Okay Bruce, on further review, there is a missing element he "* if d2=0,
then e2 should show "No Change" (or display 0%)."  even if the numbers are zero. Using your suggested formula below, and considering the above criteria, when the 2004 number is zero and the 2005 number is zero, cell e2 returns "100%" when I was hoping for "0%" because a change from 0 to 0 is nil. Thoughts, please? Trevor "Trevor" wrote: snip< Column e (Change %) here is the problem...I need to iclude all of these conditions. * if d2=0, then e2 should show "No Change" (or display 0%)...pretty easy so far * if there is a difference between b2 and c2, then change over time formula is ((c2b2)/b2). No problem s long as b2 is NOT zero * trouble is, if b2=0 then I get a DIV error in the change over time formula. I need any change from zero in 2004 to any number 0 in 2005 to display "100%" 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
change date based on time  Excel Discussion (Misc queries)  
Can series tool change formulas over time when used to copy them  Excel Worksheet Functions  
change format for time value  Excel Discussion (Misc queries)  
HOW CAN I CHANGE SETUP ON MULTIPLE SHEETS AT 1 TIME?  Setting up and Configuration of Excel  
Calculate change over time  Excel Worksheet Functions 