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

Hi,

Maybe this

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

Mike

"Carolina" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Carolina" wrote in message
...
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

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

"Mike H" wrote:

Hi,

Maybe this

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

Mike

"Carolina" wrote:

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

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

"Carolina" wrote in message
...
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

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.
--
David Biddulph


"Carolina" wrote in message
...
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

"Mike H" wrote:

Hi,

Maybe this

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

Mike

"Carolina" wrote:

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

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" wrote:

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.
--
David Biddulph


"Carolina" wrote in message
...
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

"Mike H" wrote:

Hi,

Maybe this

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

Mike

"Carolina" wrote:

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

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

"Carolina" wrote in message
...
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" wrote:

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.
--
David Biddulph


"Carolina" wrote in message
...
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

"Mike H" wrote:

Hi,

Maybe this

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

Mike

"Carolina" wrote:

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

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

"David Biddulph" wrote:

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

"Carolina" wrote in message
...
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" wrote:

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.
--
David Biddulph


"Carolina" wrote in message
...
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

"Mike H" wrote:

Hi,

Maybe this

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

Mike

"Carolina" wrote:

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