Rank a cell that could be text or a number
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
That worked for a few rows and then it quit working.
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
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
ASKER
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.
ASKER
Excellent job!!
ASKER
COUNTIF.jpg