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

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

Basic formula:

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

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

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=483260

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

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(B90,E9*(B9/0.8),IF(C90,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

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

If B0 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

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

In E9 =IF(B90,B9/.8,IF(C90,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 B0 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

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

Thanks. This is really a helpful site.

KF


--
ekfinch
------------------------------------------------------------------------
ekfinch's Profile: http://www.excelforum.com/member.php...o&userid=28658
View this thread: http://www.excelforum.com/showthread...hreadid=483260

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(B90,B9/0.8,IF(C90,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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=483260