Prev: VLOOKUP - Not returning the proper number (Caro)
Next: Can multiple criteria be used for IF formulas?
From: CHaney on 25 Feb 2010 12:12
Thanks Joe for the help, I figured it out.

Used the following formula within the cell: =IF(F3=0,1,U3/F3-1)
Then used this formula in the Conditional Format to blank out the cell when
both cells were 0: =F3+U3=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
First  |  Prev  | 
Pages: 1 2
Prev: VLOOKUP - Not returning the proper number (Caro)
Next: Can multiple criteria be used for IF formulas?