Can multiple criteria be used for IF formulas?

[CHaney]

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.

 

[Joe User]

=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]

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

=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 -----

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.

 

[CHaney]

=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

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

=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 -----

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.

 

[CHaney]

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

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

=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 -----

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.