Nested If Limit

R

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.
 
B

Biff

Hi!

Try this:

If row 160 contains the ranks:

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

Biff
 
J

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)))
 
R

Rick

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

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top