Excel Formula Help

In the cells V18 to V25, I need a formula that is the value in T18 to T25 for each player.  But I need to add logic:

If player rank is '1' in W7 to W14 then add (.5 * W4)
If player rank is '2' in W7 to W14 then add (.3 * W4)
If player rank is '2' in W7 to W14 then add (.2 * W4)

The value in W4 continues to shrink as each week goes on.  That's OK, just a way it's setup.  At the end it should be $490 that gets allocated out.

Thanks in advance, not quite sure how to do this.
2018_Fantasy_Football_Standings.xlsx
elwayisgodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What about the else case i.e. if player rand is not either 1 or 2 or 3, what would be the output in column V?
If in that case you want simply the exact value from column T, try the below formula otherwise if you want blank in else case, just replace the bold part T18 with "" (two double quotes).

In V18
=IF(ISNUMBER(MATCH(INDEX($W$7:$W$14,MATCH(B18,$B$7:$B$14,0)),{1,2,3},0)),(CHOOSE(INDEX($W$7:$W$14,MATCH(B18,$B$7:$B$14,0)),0.5,0.3,0.2)*$W$4)+T18,T18)

=IF(ISNUMBER(MATCH(INDEX($W$7:$W$14,MATCH(B18,$B$7:$B$14,0)),{1,2,3},0)),(CHOOSE(INDEX($W$7:$W$14,MATCH(B18,$B$7:$B$14,0)),0.5,0.3,0.2)*$W$4)+T18,T18)

Open in new window

0
Rory ArchibaldCommented:
You know you're creating a circular reference?
0
Rob HensonFinance AnalystCommented:
Slightly different approach in attached file.

In cells Y2:Z4 I have added a small table with ranks 1 to 3 and the percentage of winnings for each rank. This is then used as a lookup for the following formula:

=$T18+IF(INDEX($W$7:$W$14,MATCH($B18,$B$7:$B$14,0))>3,0,VLOOKUP(INDEX($W$7:$W$14,MATCH($B18,$B$7:$B$14,0)),$Y$2:$Z$4,2,FALSE)*$W$4)

I have put this in cells W18 to W25 as I am not sure why you want to overwrite the existing formulas in V18 to V25.
2018_Fantasy_Football_Standings.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

elwayisgodAuthor Commented:
Yep.  Got a circular reference error.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I didn't get the circular reference because I tested the formula in column W.
Circular reference is there because of the formula in W3 and W4.
If you place the overwrite the column V with suggested formula, in that case W3 is dependent on V27 which in turn is dependent on V18:V25 and w4 is dependent on W3 and that creates the circular reference.
0
elwayisgodAuthor Commented:
Works perfectly.  Thanks, I would have never thought of this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.