Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I cannot enter this formula into Excel. I get an error message asking for an apostrophe?IF($S2=0,"",IF($S2>0,RANK($S2,$S$2:$S$8000),RANK($S2,$S$2:$S$8000-COUNTIF($S$2:$S$8000,0)))
Solved by B. W. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 01/10/2018 - 12:17
Welcome, Thanks for choosing Got It Pro-Excel. I can help you with that problem.
Excelchat Expert 01/10/2018 - 12:18
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User 01/10/2018 - 12:18
OK
Excelchat Expert 01/10/2018 - 12:19
Will you mind if you provide the file which you are applying this formula?
User 01/10/2018 - 12:20
I cant, company policy
Excelchat Expert 01/10/2018 - 12:20
Okay.
Excelchat Expert 01/10/2018 - 12:21
A moment as I diagnose the formula...
Excelchat Expert 01/10/2018 - 12:27
Thank you for your patience.
Excelchat Expert 01/10/2018 - 12:27
I can see the formula has an error.
Excelchat Expert 01/10/2018 - 12:27
The formula should be:
Excelchat Expert 01/10/2018 - 12:27
=IF($S2=0,"",IF($S2>0,RANK($S2,$S$2:$S$8000),RANK($S2,$S$2:$S$8000)-COUNTIF($S$2:$S$8000,0)))
Excelchat Expert 01/10/2018 - 12:28
The second RANK function DID NOT have a closing parenthesis.
Excelchat Expert 01/10/2018 - 12:28
Kindly check and confirm that it works fine.
Excelchat Expert 01/10/2018 - 12:28
Thank you.
User 01/10/2018 - 12:29
Yes it works , brilliant , thank you.
Excelchat Expert 01/10/2018 - 12:29
Again
Excelchat Expert 01/10/2018 - 12:29
Thank you for your patience.
Excelchat Expert 01/10/2018 - 12:30
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.