Solved

# Excel Formula Help

Posted on 2011-05-12
321 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

LVL 24

Expert Comment

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

Author Comment

Yes.. for the W, L or P.  Sorry
0

LVL 24

Expert Comment

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

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

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

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

LVL 24

Expert Comment

Yes, that's the ticket.
0

LVL 24

Accepted Solution

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

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

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

Author Comment

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

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
VMware vCenter Converter Standalone 5.0 is a FREE tool available from VMware upon registration, the purpose of this software tool is to easily convert physical or virtual computers, images of computers to VMware virtual machines. Server and Workstat…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This video shows you how easy it is to boot from ISO images for virtual machines with the ISO images stored on a local datastore on the ESXi host.