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