How do you use multiple if formulas with multiple choices?

[LubberLou]

I have a sheet of 5 columns. I'm trying to make a spreadsheet of costs
between two people to show who pays for what and what each person owes the
other. The columns look like this:

A - a varying amount (in dollars)
B - who the amount was paid by (either KT or JB)
C - whether or not the cost is a shared, non-shared, or paid cost (either
"s", "n", or "p" text-values)
D - what KT owes JB
E - what JB owes KT

Here's the thing. I want the amounts in columns D and E to change based on
the various combinations of B and C together. So here are the formulas:

If B=KT and C=S, then D=$0 and E=A/2
If B=KT and C=N, then D=$0 and E=A
If B=KT and C=P, then D=$-A and E=$0

If B=JB and C=S, then D=A/2 and E=$0
If B=JB and C=N, then D=A and E=$0
If B=JB and C=P, then D=$0 and E=$-A

I've tried using many, many different if formulas and none of them seem to
work correctly. Please help! Thanks so much!

 

[LubberLou]

Oh! I forgot to mention that the 6 formulas are all lumped into TWO cells
(either D or E) to find the value for both D and E. Hopefully that makes
sense.

Here's an idea of what I was messing around with, but that didn't work
(because it is assuming that if the first "if" is wrong then the second "if"
is correct, which is not the case. One of the "if"s will be correct and the
others will be wrong. I want the formula to check for which one to use):
=IF(D6="JB" =AND(E6="S"), C6/2,IF(D6="JB" =AND(E6="N"),C6,IF(D6="KT"
=AND(E6="S"), 0, "MESSUP")))

So basically, you are looking for the answer to both D and E from only
filling in columns A, B, and C.

 

[LubberLou]

Oh, one last thing: the formula that I just entered has the columns listed
with different letters. That doesn't really matter. when I entered them they
were corresponded to the correct cells. I'm not concerned about that. I just
need to know how I would do a formula like this.

 

[Fred Smith]

Here's how to use the And function:
=IF(and(D6="JB",E6="S"),
C6/2,IF(and(D6="JB",E6="N"),C6,IF(and(D6="KT",E6="S"), 0, "MESSUP")))

Hopefully, you can take it from here.

Regards,
Fred.

 

[LubberLou]

Thanks for your help, Fred. Doesn't the formula provided mean that the second
"IF" is the answer if the first "IF" is not true, and so on? I don't want
that to happen. I want three separate if statements. Does that make sense?

 

[IanC]

Effectively they are separate statements, but Excel can only look at one at
a time.

Nested IFs work on the basis that if the first condition isn't met, Excel
has a look at the next condition. If that isn't met, it looks at the next
etc. until you run out of conditions. In your formula, if not of the
conditions are met, the result is MESSUP.

The only way this would be a problem is if two of the statements are true.
The result is that the first satisfied statement would give the result, the
second one being ignored.

 

[pshepard]

Hi LubberLou,

Based on your example, I created the following named ranges:

Paid_By = column B
Owed_By = "KT" in D1, "JB" in E1
Owed_To = "JB" in D2, "KT" in E2
Cost = column C
Amount = column A

The following formula works for me:

=IF(AND(Paid_By=Owed_By,Cost="paid"),-Amount,0)+IF(AND(Paid_By=Owed_To,Cost="shared"),Amount/2,0)+IF(AND(Paid_By=Owed_To,Cost="non-shared"),Amount,0)

Hope this helps.