Prev: VLOOKUP - Not returning the proper number (Caro)
Next: Can multiple criteria be used for IF formulas?
From: CHaney on 25 Feb 2010 10:26
I am using the below formula to calculate percent of change from one year to
another year.

=IF(F3=0,1,U3/F3-1)

F3 being the amount spent in previous year
U3 being amount spent in current year

The question I want my formula to answer is: If F3 and U3 have a quotient
greater than zero, then calculate. However, I also want it to yield a 100
answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are
zero.
--
Thanks, Christine
From: Joe User on 25 Feb 2010 10:42
"CHaney" wrote:
> =IF(F3=0,1,U3/F3-1)
[....]
> I also want it to yield a 100 answer if the F3
> is zero AND I also want it to yield 0 if both F3
> and U3 are zero.

Your current formula seems to do just that. Did you try it?

When F3 and U3 are zero, it will return 1 (100% if formatted as Percentage)
because F3 is zero. There is no need to make a special case.

How is your formula behaving differently than you expect?

If the issue is it returns 1 instead of 100 [sic], either change 1 to 100 in
the formula (ill-advised) or be sure that the cell is formatted as Percentage.

If you do change 1 to 100 (ill-advised), you will need to change the last
argument to 100*(U3-F3)/F3 in order to be copacetic. In that case, be sure
that the cell is __not__ formatted as Percentage.


----- original message -----

"CHaney" wrote:
> I am using the below formula to calculate percent of change from one year to
> another year.
>
> =IF(F3=0,1,U3/F3-1)
>
> F3 being the amount spent in previous year
> U3 being amount spent in current year
>
> The question I want my formula to answer is: If F3 and U3 have a quotient
> greater than zero, then calculate. However, I also want it to yield a 100
> answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are
> zero.
> --
> Thanks, Christine
From: Joe User on 25 Feb 2010 10:43
Please see responses to your later reposting.


----- original message -----

"CHaney" wrote:

> I am using the below formula to calculate percent of change from one year to
> another year.
>
> =IF(F3=0,1,U3/F3-1)
>
> F3 being the amount spent in previous year
> U3 being amount spent in current year
>
> The question I want my formula to answer is: If F3 and U3 have a quotient
> greater than zero, then calculate. However, I also want it to yield a 100
> answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are
> zero.
> --
> Thanks, Christine
From: CHaney on 25 Feb 2010 11:15
Oh, sorry I should have entered that. I get the following results with my
formula.

If F3 and U3 are >0 it calculates
If F3 is 0, I get 100% --exactly as you said because it's formatted for
percentage

However, when both F3 and U3 are 0, I also get 100% when I want it to yield
0 as the percent of change. So, I wanted to know if I can ask it to yield the
one if only F3 is 0, and zero if U3 is zero, and zero if F3 and U3 are both 0.

--
Thanks, Christine


"Joe User" wrote:

> "CHaney" wrote:
> > =IF(F3=0,1,U3/F3-1)
> [....]
> > I also want it to yield a 100 answer if the F3
> > is zero AND I also want it to yield 0 if both F3
> > and U3 are zero.
>
> Your current formula seems to do just that. Did you try it?
>
> When F3 and U3 are zero, it will return 1 (100% if formatted as Percentage)
> because F3 is zero. There is no need to make a special case.
>
> How is your formula behaving differently than you expect?
>
> If the issue is it returns 1 instead of 100 [sic], either change 1 to 100 in
> the formula (ill-advised) or be sure that the cell is formatted as Percentage.
>
> If you do change 1 to 100 (ill-advised), you will need to change the last
> argument to 100*(U3-F3)/F3 in order to be copacetic. In that case, be sure
> that the cell is __not__ formatted as Percentage.
>
>
> ----- original message -----
>
> "CHaney" wrote:
> > I am using the below formula to calculate percent of change from one year to
> > another year.
> >
> > =IF(F3=0,1,U3/F3-1)
> >
> > F3 being the amount spent in previous year
> > U3 being amount spent in current year
> >
> > The question I want my formula to answer is: If F3 and U3 have a quotient
> > greater than zero, then calculate. However, I also want it to yield a 100
> > answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are
> > zero.
> > --
> > Thanks, Christine
From: CHaney on 25 Feb 2010 11:35
=U3/F3-1*(IF(F3=0,1)*(IF(F3+U3=0,0)))

I have also tried this formula. But the yield is #DIV/0!
--
Thanks, Christine


"CHaney" wrote:

> Oh, sorry I should have entered that. I get the following results with my
> formula.
>
> If F3 and U3 are >0 it calculates
> If F3 is 0, I get 100% --exactly as you said because it's formatted for
> percentage
>
> However, when both F3 and U3 are 0, I also get 100% when I want it to yield
> 0 as the percent of change. So, I wanted to know if I can ask it to yield the
> one if only F3 is 0, and zero if U3 is zero, and zero if F3 and U3 are both 0.
>
> --
> Thanks, Christine
>
>
> "Joe User" wrote:
>
> > "CHaney" wrote:
> > > =IF(F3=0,1,U3/F3-1)
> > [....]
> > > I also want it to yield a 100 answer if the F3
> > > is zero AND I also want it to yield 0 if both F3
> > > and U3 are zero.
> >
> > Your current formula seems to do just that. Did you try it?
> >
> > When F3 and U3 are zero, it will return 1 (100% if formatted as Percentage)
> > because F3 is zero. There is no need to make a special case.
> >
> > How is your formula behaving differently than you expect?
> >
> > If the issue is it returns 1 instead of 100 [sic], either change 1 to 100 in
> > the formula (ill-advised) or be sure that the cell is formatted as Percentage.
> >
> > If you do change 1 to 100 (ill-advised), you will need to change the last
> > argument to 100*(U3-F3)/F3 in order to be copacetic. In that case, be sure
> > that the cell is __not__ formatted as Percentage.
> >
> >
> > ----- original message -----
> >
> > "CHaney" wrote:
> > > I am using the below formula to calculate percent of change from one year to
> > > another year.
> > >
> > > =IF(F3=0,1,U3/F3-1)
> > >
> > > F3 being the amount spent in previous year
> > > U3 being amount spent in current year
> > >
> > > The question I want my formula to answer is: If F3 and U3 have a quotient
> > > greater than zero, then calculate. However, I also want it to yield a 100
> > > answer if the F3 is zero AND I also want it to yield 0 if both F3 and U3 are
> > > zero.
> > > --
> > > Thanks, Christine
 |  Next  |  Last
Pages: 1 2
Prev: VLOOKUP - Not returning the proper number (Caro)
Next: Can multiple criteria be used for IF formulas?