Solved

Posted on 2011-05-12

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

I'll attache spreadsheet....

Round-Robin.xlsx

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

I'll attache spreadsheet....

Round-Robin.xlsx

11 Comments

=IF(AND(D5="W",D6="W"),B$2

Not sure about D11 and D12 though.

B12: =IF(AND(D6="W",D7="W"),B$2

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

trailing spaces all columns | 4 | 42 | |

Windows VCenter creating Crashdump ? | 6 | 36 | |

VBA: fill range dynamically based on a config sheet | 6 | 25 | |

Adding Data To Master File From Import Final | 9 | 19 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**17** Experts available now in Live!