Nested IF & Nested AND

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

diablo - 11 Dec 2007 20:32 GMT

I'm trying to compare two cells to create two different outcomes. The
formula I'm using is as follows;

=IF((AND(H15="Capitol",I15="both")),F15/2,),IF((AND(H15="Capitol",I15="MPL41")),F15,)

Cell H15 does equal Capitol, Cell I15 does equal both, Cell F15 does
equal $3,094.36. When I use this formula I get the correct answer of
$1,547.18
=IF((AND(H15="Capitol",I15="both")),F15/2,)

When I try to nest another IF as shown above I get the error #value!.

By adding the IF((AND(H15="Capitol",I15="MPL41")),F15,) I want to
create a second condition for when I15 changes it's value to "MPL41".

What am I doing wrong?

Thanks,
Brian

Reply to this Message

Don Guillett - 11 Dec 2007 20:42 GMT

Not sure I understand but try something like
if(h15="capitol"),f15/if(i15="both",2,1),"")

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> I'm trying to compare two cells to create two different outcomes. The
> formula I'm using is as follows;
[quoted text clipped - 15 lines]
> Thanks,
> Brian

Reply to this Message

joeu2004 - 11 Dec 2007 20:58 GMT

> I'm trying to compare two cells to create two different outcomes.
> The formula I'm using is as follows;
[quoted text clipped - 3 lines]
> [....]
> What am I doing wrong?

Your error is the extraneous ",)" after f15/2. One way to write that
is:

=if(and(H15="Capitol",I15="both"), F15/2,
if(and(H15,"Capitol",I15="MPL41"), F15, "")

Note: It is unwise to have an empty value-if-false part. If neither
condition is met, the result is FALSE instead of a numeric value.
However, you might want to replace "" with something else, perhaps 0.

Reply to this Message

Rate this thread: