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

x
?
Solved

Excel Formula Help

Posted on 2011-05-12
11
Medium Priority
?
328 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:elwayisgod
  • 6
  • 5
11 Comments
 
LVL 24

Expert Comment

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

Author Comment

by:elwayisgod
ID: 35747748
Yes.. for the W, L or P.  Sorry
0
 
LVL 24

Expert Comment

by:StephenJR
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:elwayisgod
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

by:StephenJR
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

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

Expert Comment

by:StephenJR
ID: 35748307
Yes, that's the ticket.
0
 
LVL 24

Accepted Solution

by:
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

by:elwayisgod
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

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

Author Comment

by:elwayisgod
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

830 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question