If statement help

[ekfinch]

I am trying to write a formula using multiple if statements or some other method in Excel and I cannot seem to make it work. Here is the scenario:

If B9 > 0 then calculate value (E9) using (B9/.8) else
if C9 > 0 then calculate value (E9) using (C9/.9) else
E9.

This is an insurance application dealing with coinsurance and conversion to 100%.

Any ideas would be greatly appreciated.

[swatsp0p]

Question: What do you want to happen if BOTH B9 and C9 are >0?

Basic formula:

In E9 =IF(B9>0,B9/.8,IF(C9>0,C9/.9,"N/A"))

For this example, if B9>0, it won't even look at C9. Is this what you want?

Good Luck

[Gary''s Student]

What if both C9 and B9 are greater than zero?
--
Gary''s Student


"ekfinch" wrote:

>
> I am trying to write a formula using multiple if statements or some
> other method in Excel and I cannot seem to make it work. Here is the
> scenario:
>
> If B9 > 0 then calculate value (E9) using (B9/.8) else
> if C9 > 0 then calculate value (E9) using (C9/.9) else
> E9.
>
> This is an insurance application dealing with coinsurance and
> conversion to 100%.
>
> Any ideas would be greatly appreciated.
>
>
> --
> ekfinch
> ------------------------------------------------------------------------
> ekfinch's Profile: http://www.excelforum.com/member.php...o&userid=28658
> View this thread: http://www.excelforum.com/showthread...hreadid=483260
>
>

[Elkar]

I'm not sure I completely understand just what you want. Here's assuming
that E9 is multiplied if one of the conditions is met. Also, if B9 contains
any value greater than 0, the value in C9 will be ignored. Hopefully this
will at least point you in the right direction. Post back with a clearer
idea of what you want if you need more help.

=IF(B9>0,E9*(B9/0.8),IF(C9>0,E9*(C9/0.9),E9))

HTH,
Elkar

"ekfinch" wrote:

>
> I am trying to write a formula using multiple if statements or some
> other method in Excel and I cannot seem to make it work. Here is the
> scenario:
>
> If B9 > 0 then calculate value (E9) using (B9/.8) else
> if C9 > 0 then calculate value (E9) using (C9/.9) else
> E9.
>
> This is an insurance application dealing with coinsurance and
> conversion to 100%.
>
> Any ideas would be greatly appreciated.
>
>
> --
> ekfinch
> ------------------------------------------------------------------------
> ekfinch's Profile: http://www.excelforum.com/member.php...o&userid=28658
> View this thread: http://www.excelforum.com/showthread...hreadid=483260
>
>

[ekfinch]

I am closer. Thanks for the help. I will clarify.

If B>0 then I do not care about C. THere will never be anything there.
SO, I just want E9 to be what is calculated in B9 or (B9/.8).

If B9 is 0 and C9 > 0 then I want E9 to be what is calculated in C9 (C9/.9).

If both B9 and C9 = 0 then there will be a value entered into E9. That is fine as it will not be used again without bringing in a new spreadsheet.

Thanks,

KF

[Elkar]

It sounds like swatsp0p's solution ought to work for you then.

In E9 =IF(B9>0,B9/.8,IF(C9>0,C9/.9,"N/A"))

You could adjust the "N/A" part to display "Enter value here" so that the
user knows that a value needs to be manually entered.

"ekfinch" wrote:

>
> I am closer. Thanks for the help. I will clarify.
>
> If B>0 then I do not care about C. THere will never be anything
> there.
> SO, I just want E9 to be what is calculated in B9 or (B9/.8).
>
> If B9 is 0 and C9 > 0 then I want E9 to be what is calculated in C9
> (C9/.9).
>
> If both B9 and C9 = 0 then there will be a value entered into E9. That
> is fine as it will not be used again without bringing in a new
> spreadsheet.
>
> Thanks,
>
> KF
>
>
> --
> ekfinch
> ------------------------------------------------------------------------
> ekfinch's Profile: http://www.excelforum.com/member.php...o&userid=28658
> View this thread: http://www.excelforum.com/showthread...hreadid=483260
>
>

[ekfinch]

I took his and modified it. I do like the Enter Value Here.

Thanks. This is really a helpful site.

KF

[swatsp0p]

I would caution the wording of "Enter Value Here", as this will be displayed in E9. You really want a value entered into either B9 or C9...not "Here" as that would overwrite your formula.

You could use:

E9=IF(B9>0,B9/0.8,IF(C9>0,C9/0.9,"Enter Value in B"&ROW()&" or C"&ROW()))

which returns "Enter Value in B9 or C9" when both are blank. This formula allows the return to change as you copy the formula down your range (if needed).

I am glad this is working for you.

Cheers!

Bruce