Excel Formula Help

elwayisgod
elwayisgod used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

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

Most Valuable Expert 2011
Top Expert 2011

Commented:
You know you're creating a circular reference?
Finance Analyst
Commented:
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
Ensure you’re charging the right price for your IT

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

Author

Commented:
Yep.  Got a circular reference error.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial