IF with substraction

[Carolina]

I have data in E7 and in H7. Depending on which data they want to go with I
need a formula to make a substration. If they select in L8 that they want the
50% program then in another cell I need the result of E7-M7. If they select
in L8 that they want the 0% program then in another cell I need the result of
H7-M7. No other options available.

My attempt was the following but I got FALSE....I need the differences:
=IF(L8="50%",(E8-M8),IF(L8="0%",(H8-M8),0))

Can you help???

Thanks.
Carolina

 

[Mike H]

Hi,

Maybe this

=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,0))

Mike

 

[T. Valko]

My attempt was the following but I got FALSE

=IF(L8="50%",(E8-M8),IF(L8="0%",(H8-M8),0))

I don't see how you could get a result of FALSE with that formula.

Try it like this:

=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,0))

 

[Carolina]

It did the job! Thanks.
Now, one more question....since this formula is being dragged to rows below
and then added at the bottome, I want it to show "0" when neither 0% nor 50%
where selected so that my column total ONLY reflects those variances from 0%
and 50%

Thanks a million!
Carolina

 

[David Biddulph]

Firstly I don't believe that formula can return FALSE, whatever the inputs.
If you are convinced that it does, firstly copy the formula from your
formula bar and paste it here into the group; don't try to retype it.
Also tell us what is in each of the cells which are inputs to that formula,
in this case L8, E8, and M8.

Secondly you have unnecessary parentheses.
=IF(L8="50%",(E8-M8),IF(L8="0%",(H8-M8),0)) can be simplified to
=IF(L8="50%",E8-M8,IF(L8="0%",H8-M8,0))

Thirdly, are you sure that you want text strings for your percentage tests?
If you want numbers, ditch the quote marks.
=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,0))

Fourthly, the cells you refer to in your formula are not the cells you refer
to in your text, so sort out which row you are using.

Fifthly, I assume that when you talk about "substration" and "substraction"
you mean "subtraction"? If so, your method is correct, in that E8-M8 will
subtract the value in M8 from the value in E8.

Sixthly, you say no other options are available other than 50% or 0%. If
so, you may wish to flag an error if invalid inputs are given either in your
formula
=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,"Invalid input - only 50% or 0% permitted"))
or by the use of data validation in your L8 input cell.

 

[David Biddulph]

You've currently got the number 0 as the result if the input is neither 50%
nor 0%. If you really want the text string "0" instead, put the zero at the
end of the formula in quotes, but you need to be careful how you do further
arithmetic, as many arithmetic operations will give #VALUE! errors if you
feed them with text inputs.

 

[Carolina]

David,
Per your suggestion below I amended the formula to be:
=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,"0"))
Yet it did not do what I wanted it to do. It kept giving me the results for
the part of the formula that states L8=0%,H8-M8

This morning I tried: =IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,IF(L8="_",0,0)))
....this required that I added the "_" part to the formula as oppsed to just
a blank space and this did the trick!

Thanks for responding to my inquiry and sorry for the confusion at the
beginning

Carolina

 

[David Biddulph]

Your recent change doesn't make sense.
If you were getting the result from the L8=0% path before, you should still
get the same result now, as the change you have inserted is in the
alternative path.
Additionally, even in that alternative path your change achieves nothing as
both outcomes are zero.

If you have got a different result from the formula, it's because your
inputs were different.
Perhaps what you have done is change from having an empty cell in L8 (which
would be treated as being zero) to a " " string in L8, which is non-zero.
If you've done that change in L8, then the old formula would give the same
as the new one, which is a zero result instead of H8-M8.
If you wanted to distinguish between an empty cell and zero, you could
change
=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,0)) to
=IF(L8=50%,E8-M8,IF(AND(L8<>"",L8=0%),H8-M8,0)) or
=IF(L8=50%,E8-M8,IF(AND(ISNUMBER(L8),L8=0%),H8-M8,0)) [the latter would
cope with either blank or a space]

 

[Carolina]

Thanks!
I used =IF(L8=50%,E8-M8,IF(AND(L8<>"",L8=0%),H8-M8,0))
and it worked wonders!

Thanks for all your time and assistance

Carolina

Your recent change doesn't make sense.
If you were getting the result from the L8=0% path before, you should still
get the same result now, as the change you have inserted is in the
alternative path.
Additionally, even in that alternative path your change achieves nothing as
both outcomes are zero.

If you have got a different result from the formula, it's because your
inputs were different.
Perhaps what you have done is change from having an empty cell in L8 (which
would be treated as being zero) to a " " string in L8, which is non-zero.
If you've done that change in L8, then the old formula would give the same
as the new one, which is a zero result instead of H8-M8.
If you wanted to distinguish between an empty cell and zero, you could
change
=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,0)) to
=IF(L8=50%,E8-M8,IF(AND(L8<>"",L8=0%),H8-M8,0)) or
=IF(L8=50%,E8-M8,IF(AND(ISNUMBER(L8),L8=0%),H8-M8,0)) [the latter would
cope with either blank or a space]
--
David Biddulph

David,
Per your suggestion below I amended the formula to be:
=IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,"0"))
Yet it did not do what I wanted it to do. It kept giving me the results
for
the part of the formula that states L8=0%,H8-M8

This morning I tried: =IF(L8=50%,E8-M8,IF(L8=0%,H8-M8,IF(L8="_",0,0)))
...this required that I added the "_" part to the formula as oppsed to
just
a blank space and this did the trick!

Thanks for responding to my inquiry and sorry for the confusion at the
beginning

Carolina