[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Excel Formula Help

Posted on 2011-05-12
Medium Priority
328 Views
I have three fields in cells A5, A6, A7:
Team 1
Team 2
Team 3

Cell B2 has an amount:  500

In cells D1, D2, D3 there is only 3 possible values: ' W', 'L' or 'P'.

There are six combinations I need to evaluate in cells b10 to b12.
Team 1 & Team 2 (Example Below)
Team 1 & Team 3
Team 2 & Team 3

In cell B10:  If D1 and D2 both equal 'W' then: =B\$2*(1+C5)*(1+C6)-B\$2
If either D1 or D2 are 'L' then:  =B2 * -1
If both D1 and D2 are 'P' then:  =0
If D1 is 'P' and D2 is 'W' then: B2*C6
If D1 is 'W' and D2 is 'P' then:  B2 * C5

Round-Robin.xlsx
0
Question by:elwayisgod
• 6
• 5

LVL 24

Expert Comment

ID: 35747689
When you say D1, D2, D3 I take it you mean D5:D7 as per your file?
0

Author Comment

ID: 35747748
Yes.. for the W, L or P.  Sorry
0

LVL 24

Expert Comment

ID: 35748153
OK, this then?

=IF(AND(D5="W",D6="W"),B\$2*(1+C5)*(1+C6)-B\$2,IF(OR(D5="L",D6="L"),B\$2*-1,IF(AND(D5="P",D6="P"),0,IF(AND(D5="P",D6="W"),B\$2*C6,IF(AND(D5="W",D6="P"),B\$2*C5,"???")))))

Not sure about D11 and D12 though.
0

Author Comment

ID: 35748228
If I have:

D5=W
D6=W
D7=P

Why is B12 =  ???

Shouldn't it do same logic as how B11 calc's?
0

LVL 24

Expert Comment

ID: 35748243
Well you haven't explained the logic. You said what should happen in B10. That refers to D5 and D6. What should B11 and B12 refer to?
0

Author Comment

ID: 35748280
OK.  D11 uses rows 5 and 7.  D12 uses rows 6 and 7.  That help?
0

LVL 24

Expert Comment

ID: 35748307
Yes, that's the ticket.
0

LVL 24

Accepted Solution

StephenJR earned 2000 total points
ID: 35748330
B11: =IF(AND(D5="W",D7="W"),B\$2*(1+C5)*(1+C7)-B\$2,IF(OR(D5="L",D7="L"),B\$2*-1,IF(AND(D5="P",D7="P"),0,IF(AND(D5="P",D7="W"),B\$2*C7,IF(AND(D5="W",D7="P"),B\$2*C5,"???")))))

B12: =IF(AND(D6="W",D7="W"),B\$2*(1+C6)*(1+C7)-B\$2,IF(OR(D6="L",D7="L"),B\$2*-1,IF(AND(D6="P",D7="P"),0,IF(AND(D6="P",D7="W"),B\$2*C7,IF(AND(D6="W",D7="P"),B\$2*C6,"???")))))
0

Author Comment

ID: 35748372
Did this for B11.  Not sure about how to remove the ???? part....

=IF(AND(D\$5="W",D\$7="W"),B\$2*(1+C\$5)*(1+C\$7)-B\$2,IF(OR(D\$5="L",D\$7="L"),B\$2*-1,IF(AND(D\$5="P",D\$7="P"),0,IF(AND(D\$5="P",D\$7="W"),B\$2*C\$7,IF(AND(D\$5="W",D\$7="P"),B\$2*C\$5,"???")))))
0

LVL 24

Expert Comment

ID: 35749071
That means none of the other conditions are met. What should happen?
0

Author Comment

ID: 35749464
I guess it's ok that way.  There will always have to be a W, L or P in the field, then.  Thats minor and actually could be a good thing... I'll just keep as is....
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.