Solved

# Rank a cell that could be text or a number

Posted on 2011-10-20
Medium Priority
294 Views
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

Thanks
If-ISNUMBER.jpg
If-ISTEXT.jpg
0
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

=IF(COUNT(E6),IF(E6=0,"",COUNTIFS(E\$6:E\$86,">0",E\$6:E\$86,"<"&E6)+1),E6)

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

regards, barry
0

Author Comment

ID: 37003814
That returned a value of TRUE.
COUNTIF.jpg
0

LVL 50

Expert Comment

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
27407904.xlsx
0

Author Comment

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

LVL 50

Expert Comment

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
0

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

Author Closing Comment

ID: 37022030
Excellent job!!
0

## Featured Post

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.
###### Suggested Courses
Course of the Month15 days, 20 hours left to enroll