software

Trying to calculate change % with a couple criteria. Here's what I'm doing...
Column a (labels) easy--data input
Column b (2004 number) easy--data input, all gt;=0
Column c (2005 number) easy--data input, all gt;=0
Column d (Change #) easy [=c2-b2] 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 quot;No Changequot; (or display 0%)...pretty easy so
far
* if there is a difference between b2 and c2, then change over time formula
is ((c2-b2)/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 gt;0 in 2005 to
display quot;100%quot;

Would like some help creating a formula to care for this. Thank you.

In e2: =if(b2=0,1,d2/b2). Format the result as a percent.

quot;Trevorquot; wrote:

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

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?

quot;bpeltzerquot; wrote: In e2: =if(b2=0,1,d2/b2). Format the result as a percent.The formula says that if b2 is 0, return 1 (100%) as the result. Otherwise,
the result is d2/b2. --Bruce

quot;Trevorquot; wrote:

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

Okay Bruce, on further review, there is a missing element he quot;* if d2=0,
then e2 should show quot;No Changequot; (or display 0%).quot; -- 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 quot;100%quot;
when I was hoping for quot;0%quot; because a change from 0 to 0 is nil. Thoughts,
please?
Trevor

quot;Trevorquot; wrote:
gt;sniplt;
gt; Column e (Change %) here is the problem...I need to iclude all of these
gt; conditions.
gt; * if d2=0, then e2 should show quot;No Changequot; (or display 0%)...pretty easy so
gt; far
gt; * if there is a difference between b2 and c2, then change over time formula
gt; is ((c2-b2)/b2). No problem s long as b2 is NOT zero
gt; * trouble is, if b2=0 then I get a DIV error in the change over time
gt; formula. I need any change from zero in 2004 to any number gt;0 in 2005 to
gt; display quot;100%quot;
gt;