Rank and Nested IF Formula - Help Please!

Here is a formula that will return "N/A" if the values in the corresponding cells of columns L, S and T all contain "N/A":

Please Login or Register  to view this content.

I am not sure that it accomplishes what you want however since it now returns a value of 1 when "N/A" is in column L. If that is not the desired result then if you could manually input a few of the expected values for column AB, Rank 1, perhaps someone will be able to give you a formula to do what you need.

I think that I understand the logic, if there is a number in L2 find how many scores in L2:L200 are greater than that number and add one. If L2 doesn't have a number and R2 does then count how many scores in R2:R200 are greater than that number and add one. If L2 doesn't have a number and R2 doesn't have a number and S2 does then count how many scores in S2:S200 are greater than that number and add one. I have a formula that does that, however the results are not what is stated on the spreadsheet (i.e. AA3 is 46 not 41). I am going to post the formula anyway, and hopefully you can see what is causing the discrepancy:

Please Login or Register  to view this content.

Let me know if you find the discrepancy or if I have misinterpreted the query.

That almost works—thanks very much! I do appreciate the help. Your interpretation is right apart from the fact that the scores in columns R and S need to be ranked in relation to those in L. What I'm trying to do is stream students into classes by test scores. The problem for me is where I don't have a student's latest score; then I need to look at a previous test (Col R) or even the one before that (Col S) to put the student into the right class. Thanks!

This formula gets the expected result, 41, in row 3, however it gives a different result for row 13, 145 as opposed to 138.

Please Login or Register  to view this content.

Looking at the scores in column L the lowest score, of the 144 students that have a numeric score, is 197 and Student 13's score in column S is 180, therefore a rank of 145 seems correct to me, if I understand the logic of:

scores in columns R and S need to be ranked in relation to those in L

Let me know if you have any questions.

Try this formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

You're Welcome and thank you for the feedback. If you haven't already please take a moment and mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a nice day.