Help with Nested IF's?

[ciscogeek]

I am creating a spreadsheet for bowling scores for a tournament. There are 3 players in a match. For each match, there are bonus points involved. It works like this. The bowler with the highest score gets 100 points. The next highest score gets 50 points. The last player gets no bonus points. If there is a tie for the highest score, each player is awarded 75 points. If there is a tie for second highest score, each of the players get 25 points. The scores will be entered into 3 seperate cells. What is the best way to handle this processing? Any help with the functions and coding would be greatly appreciated. Thanks.

Mike

[dilipandey]

Hi Mike,

Welcome to the forum.
See the attached file and let me know if this can be of any help. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[ciscogeek]

That definitely works. Thanks for the great help.

Mike

[dilipandey]

You are welcome Mike. .

Cheers.



Regards,
DILIPandey
<click on below star if this helps>

[ciscogeek]

The function itself does work great. However, there is one problem. The cells that contain the scores to be "ranked" are not contiguous. They are like cell A6, A13, A19. How can I modify your logic to not include a range (ie $B$2:$B$4) but to use individual discontiguous cells?

[dilipandey]

Hi ciscogeek,

I have included you change request, see the attached file. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

Hi ciscogeek,

I have included you change request, see the attached file. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[ciscogeek]

Thanks for the updated help. Unfortunately, I am still stuck because I have a circular reference and I am not sure how to modify this to make it work. Any ideas? Here is the sheet and pay close attention to the Round 1 sheet. I am still trying to write the function to figure the bonus. You will see the bonus row and the bonus points are figured based on each individual match.

[dilipandey]

Hi Ciscogeek,

You are getting circular reference because you are calculating Rank within the similar column where Ranks are present. Suggest you to calculate Ranks in column N and O.

Regards,
DILIPandey

<click on below 'star' if this helps>