Help converting a word to a number value

[warpedsoul]

Hello all. I am trying to create a spreadsheet to keep track of an online racing league that I'm in. I have created columns for the racers, and rows for the races. What I would like to do is type in 1st, and it represent the number 13 for when I do an autosum at the bottom of that column. It would be easier for me to type in the position place, instead of the points value. The position place will always carry the name numeric value. Here is what I have so far:

Start & Park Season 1.xlsm

[tigeravatar]

warpedsoul,

Welcome to the forum!
In cell D41 and copied right:

Please Login or Register  to view this content.

[warpedsoul]

warpedsoul,

Welcome to the forum!
In cell D41 and copied right:

Please Login or Register  to view this content.

Im not sure that I follow your reply.

To help clarify, on the spreadsheet I attacked, for example, if I put "1st" in cell D2, I would like for it to work with autosum and have 13 in cell D41. If I put "3rd" in cell D3, then cell D41 becomes 20.

[Jakobshavn]

Here is a macro-based solution.
If you enter 1st (or other words from your table) in a cell, it will automatically be converted to the correct numerical value.
Macros must be enabled for this to work!

[warpedsoul]

Here is a macro-based solution.
If you enter 1st (or other words from your table) in a cell, it will automatically be converted to the correct numerical value.
Macros must be enabled for this to work!

Thanks. That does help. Is there any way to have the words "1st" "2nd" etc stay in the field, but the number still be represented?

[tigeravatar]

Oh i see, you put 1st, 2nd, etc instead of just 1, 2, etc. A formula to account for that would probably be kinda long and ugly. Jakobshavn's macro solution should work just fine for you though

[Jakobshavn]

This should do it:

[warpedsoul]

This should do it:

You, sir, are the man! Thanks so much! If it isn't a long process, care to explain how?

[Jakobshavn]

Sure:

1. the macro detects any change to the large results table
2. the macro looks for the word in your small translate table
3. if the word is found, the macro replaces the word with the appropriate value
4. the macro applies a "cheater" format to the cell

The "cheater" format makes the cell look like the typed word, but actually it has a numerical value.

That is, the cell displays 1st, but the formula bar reveals that it is actually 13.

[GeneralDisarray]

Sort the list the other way, add a placeholder for the list (---) and validate the range..


viola. see attached example


formula needed: =SUMPRODUCT(IF(MATCH(D2:D40,$B45:$B55,0)=10,13,IF(MATCH(D2:D40,$B45:$B55,0)=11,0,MATCH(D2:D40,$B45:$B55,0))))

[warpedsoul]

Sure:

1. the macro detects any change to the large results table
2. the macro looks for the word in your small translate table
3. if the word is found, the macro replaces the word with the appropriate value
4. the macro applies a "cheater" format to the cell

The "cheater" format makes the cell look like the typed word, but actually it has a numerical value.

That is, the cell displays 1st, but the formula bar reveals that it is actually 13.

How do i edit if I wanted to add more racers?

[Jakobshavn]

This version fixes the formula in row #41 to work for racer #10. To add more racers, you would need to extend the formulas in row #41 and do a one-line update to the macro to cover the additional columns for the additional racers.

[warpedsoul]

Sort the list the other way, add a placeholder for the list (---) and validate the range..


viola. see attached example


formula needed: =SUMPRODUCT(IF(MATCH(D2:D40,$B45:$B55,0)=10,13,IF(MATCH(D2:D40,$B45:$B55,0)=11,0,MATCH(D2:D40,$B45:$B55,0))))

This is very helpful too. Thanks you. I didn't think about doing it this way.

[warpedsoul]

This version fixes the formula in row #41 to work for racer #10. To add more racers, you would need to extend the formulas in row #41 and do a one-line update to the macro to cover the additional columns for the additional racers.

I can't find where to edit the macro lol.

[Jakobshavn]

To change the macro to accommodate additional racers, right-click the tabname at the bottom of the Excel window and choose View Code. The single line to change is:

Set Results = Range("D2:M40")

The M is for racer #10. To handle 11 racers, use:

Set Results = Range("D2:N40")

and follow this pattern for each racer added

[GeneralDisarray]

did you try the other way? (post #10) you can get it to happen w/o a macro

[warpedsoul]

did you try the other way? (post #10) you can get it to happen w/o a macro

I have. I will use that one as an alternative.

Also, I'm looking to input a season rank section that includes total points accrued and sorts by rank. I have updated the OP with the updated spreadsheet

[Jakobshavn]

Hi warpedsoul:

Attached is a version with a rank table. Since we are using the Excel Rank() worksheet function, tied scores will have the same rank. See Excel Help for RANK for a more complete explanation.