Link to home
Start Free TrialLog in
Avatar of elwayisgod
elwayisgodFlag for United States of America

asked on

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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

You know you're creating a circular reference?
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of elwayisgod

ASKER

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