If statement help please

[Markyboy]

Hello

Hello I need a formula to help with the following below.

The varaiables are Andy and Z both collect funds from the customer and
both ship products.

The partners split the profit and the shipper keeps the wholesale cost
in addition to the profit.

Columns A, B work out the two income levels all ok.
Column C works out who has collected the money and is due to pay it
to.
Column F works out who shipped all ok.

In this example Z collects the money and A ships so Z pays to A column
c funds ($62.50)

I need columns E to work out if A ships and Z collects funds, also need
column C to work out vice versa, I can ensure the opposite cell will
show as clear by using conditional formatting.


A Shippers Income $62.50

B Partner Income $32.50

C Money to be paid to Andy

D Andy pay to Z = $formula?? (answer $0) this example

E Z pay to Andy = $formula?? (answer $62.50) this example

F Shipped by Andy

Sorry for the vertical layout guys this software crunches the text
together if in normal excel panoramic view.

There may be a cleaner way of doing this guys, but I have tried and/if
statements and not had any success.

Thanks for your help

Mark

 

[Markyboy]

bump ...............

 

[Roger Govier]

Hi Mark

In D2

=IF(AND(C1="Andy",F1="Andy),0,IF(AND(C1="Andy",F1="Z",B1),0)

In E2
=IF(AND(C1="Z",F1="Z"),0,IF(AND(C1="Z",F1="Andy",B1),0)

 

[Markyboy]

Thanks Roger

Unfortunately it comes up with a false value and not a figure.

I guess I can explain a little clearer now, and if I can get ok for one
cell I can do the other easily.

A Shippers Income $62.50

B Partner Income $32.50

C Option (money to be paid to A or Z)

D A owes Z (if C=A then $0)

If C = Z then the answer will return A or B depending on F being A or
Z. I can arrange the order of this last bit if wrong way round.

E Z owes A (I will add this formula from your answer to D above)

F Option (shipped by A or Z)

Is this clear??? sorry if not

Mark

 

[Roger Govier]

Hi Mark

Sorry, typed the bracket in the wrong place in original posting.
Have tested this time.
in D2 =IF(AND(C1="Andy",F1="Andy"),0,IF(AND(C1="Andy",F1="Z"),B1,0))
in E2 =IF(AND(C1="Z",F1="Z"),0,IF(AND(C1="Z",F1="Andy"),B1,0))

 

[Markyboy]

Thanks Roger I do appreciate this, nearly there though.

It is working now for one combination not both.

I need cell D to return me one of two amounts either $62.50 in A o
$32.50 in B, depending on which answer is in F, Andy or Z.

Thank

 

[Roger Govier]

Hi Mark

I think I now understand.
In D2 =IF(C1="A",0,IF(F1="Z",A1,B1))
In E2 =IF(C1="Z",0,IF(F1="A",A1,B1))

Is this right?

 

[Markyboy]

Thank you so much Roger

I am so grateful thank you,


Mark

 

[Roger Govier]

You're more than welcome Mark. Thanks for the feedback.
Sorry it took so long for me to see what you were after.