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!

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


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!

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.

"LubberLou" wrote:

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


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!

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


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!

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?

"Fred Smith" wrote:

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


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!

You are right that you don't go onto the second IF test if the first one was
true, but I don't see why that is a problem, as your conditions are mutually
exclusive and you can't have more than one true.

If you could have more than one condition true at once, and you wanted to
combine the answers by concatenating them, then you could use
=IF(first condition,"first answer","")&IF(second condition,"second
answer","")&IF(third condition,"third answer","")&... and so on.
--
David Biddulph

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

"Fred Smith" wrote:

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


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!

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.

--
Ian
--
"LubberLou" wrote in message
...
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?

"Fred Smith" wrote:

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


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!

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"),Am ount/2,0)+IF(AND(Paid_By=Owed_To,Cost="non-shared"),Amount,0)

Hope this helps.

"LubberLou" wrote:


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!