Nested If Limit

[Rick]

I need to exceed the 7 nested function limit to extend the following formula:

=IF(RANK(Q$159,$C$159:$S$159)=1,"Leader",IF($C$160=1,$Q$159-$C$159,IF($E$160=1,$Q$159-$E$159,IF($G$160=1,$Q$159-$G$159,IF($I$160=1,$Q$159-$I$159,IF($K$160=1,$Q$159-$K$159,IF($M$160=1,$Q$159-$M$159)))))))

with this,

IF($O$160=1,$Q$159-$O$159),IF($S$160=1,$Q$160-$S$160)

This formula (modified by column) is used for each player in a NASCAR
fantasy league spreadsheet that tracks 9 players total points (Row 159), and
ranks them (Row 160). It determines who is the "Leader" and then subtracts
the players points from the "Leaders" points which is then displayed as -XXX
points behind the leader.

The original formula worked fine until this year when two more people joined
our league. It was then that the 7 nested functions limit stopped my
spreadsheet from working.

I don't know if VBA is the answer or some other worksheet function. I know
next to nothing about VBA but would appreciate any suggestions that would
help me solve this problem. I hope this description is clear, if not let me
know and I will try to clarify it. Thanks.

 

[Biff]

Hi!

Try this:

If row 160 contains the ranks:

=IF(C160=1,"Leader",C159-MAX($C159:$S159))

Biff

 

[JMB]

Is it safe to say D160, F160, H160, J160, L160, N160, P160, R160 contain
player names (or some data that will not match the 1 you are trying to find
in row 160?) If so, try


=IF(RANK(Q$159,$C$159:$S159)=1,"Leader",Q159-INDEX($C$159:$S$159,MATCH(1,$C$160:$S$160,0)))

 

[Rick]

Thanks for your help. This solves my problem and is much easier to follow
than my oroginal solution. Thanks again.

Rick