Rank a cell that could be text or a number

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
Windows XP Pro
Excel 2007

A spreadsheet has 86 rows. It is used to place competitive runs.  In cell E5 is the time ran.  In cell D6 is the penalty (if any).  In cell E6 is the sum of E5 + D6.    After all of the times are entered, the time in E6 is ranked against all of the other times posted and and the ranking placed in F5.  The problem is not all of the lines have numeric entries. If a run is disqualified, a DQ is entered into cell D6. Plus there are non entry lines that have 0.000 for times. those times need to be exlcuded from the ranking.  I can get numbers to rank, but I have to change the Rank cell reference to end at the last row that has a valid entry. I would like to have a formula that can be copied down all of the rows.

I have tried using both ISTEXT and ISNUMBER , but have not been able to make either work correctly. If I use ISTEXT, the cells with zeroes for times rank as 1.  If I use ISNUMBER, the text is not carried from E6 to F5.

I am attaching screen shots to show what I have tried and the results.

Plain language:

If E6 is a number that is greater than 0, then rank E6 agasint E6:E86.
If E6 is text then enter the text

Question by:Lindahq
  • 4
  • 3
LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 37003792
Try using COUNTIFS function, i.e. this formula copied down


that should put a blank against zeroes, show the text value for text and rank the non-zero numbers, lowest being 1

regards, barry

Author Comment

ID: 37003814
That returned a value of TRUE.
LVL 50

Expert Comment

by:barry houdini
ID: 37003878
I can't see how you get TRUE....see this working example - I tried to set it up the same way you have it.

Values in column E are generated randomly - press F9 to regenerate. If you can't work it out from that is it possible to include your actual workbook rather than a screenshot?

regards, barry
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 37019553
That worked for a few rows and then it quit working.
LVL 50

Expert Comment

by:barry houdini
ID: 37021541
Sorry, I thought I replied to this earlier but I don't see it now.......

Does the example I posted do what you want? I didn't create the whole range down to E86 but you should get the idea from that....

It's difficult for me to understand why you can't make that work for your real data - can you post the workbook, or a sanitised version of it if necessary?

regards, barry

Author Comment

ID: 37022029
I also thought you had answered it and we had found the problem. At any rate the formula is working and doing exactly as I wanted.

Author Closing Comment

ID: 37022030
Excellent job!!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question