# 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
###### Who is Participating?
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.

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)
``````
Commented:
You know you're creating a circular reference?
Finance 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

Experts Exchange Solution brought to you by

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

Author Commented:
Yep.  Got a circular reference error.
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.
Author Commented:
Works perfectly.  Thanks, I would have never thought of this.
###### 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.