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.