Excel 2007 : Converting text values to numerical values

**andybocchi** · 10-29-2010, 01:25 PM

am a teacher and am trying to create a datasheet with childrens levels on it. The levels are in the form of text.

w being the lowest, then 1c, 1b, 1a, then 2c, 2b, 2a and so on upto a level 5a. Each level has a numerical value, w = 3, 1c = 7, 1b = 9, 1a = 11 and so on (increments of 2 each time).

I need to create a formula that works out the difference between the childs grade at the end of the year (column B) from the childs grade at the beginning of the year (Column A) from text form with the answer shown in a numerical form.

I have tried doing the following

=IF(V27="w",3,IF(V27="1c",7,IF(V27="1b",9,IF(V27="1a",11,IF(V27="2c",13,IF(V27="2b",15,IF(V27="2a",1 7,))))))-IF(R27="w",-3,IF(R27="1c",7,IF(R27="1b",9,IF(R27="1a",11,IF(R27="2c",13,IF(R27="2b",15,IF(R27="2a",17,))))))))

but has a limit of 8 entries and i need 15!

Any ideas would be greatly appreciated as i am at a lost cause now

Thanks
Andy

**Paul** · 10-29-2010, 01:34 PM

Hi Andy, welcome to the forum.

You might try:

Please Login or Register  to view this content.

Or, if you create a table with the lookup values in one column and the return values in another (e.g. A1:A16 and B1:B16), then you could use:

Please Login or Register  to view this content.

**JulieM** · 10-29-2010, 01:45 PM

I see Paul has given you the actual formula while I was creating a spreadsheet for you, but thought I might as well upload it anyway!

**andybocchi** · 10-29-2010, 02:10 PM

You guys are a life saver. Thank you

**andybocchi** · 10-31-2010, 02:11 PM

Ok last question i promise. I have been using the formula you guys supplied me with and it works like a dream. In the last part of my spreadsheet I have to change the formula slightly.

I need to add 12 to a cell (CP5) but get the answer displayed as text again e.g. 1a, 1b etc. Any ideas, im sure its a simple fix??

How do i ammend the following??

=LOOKUP(CP5,{"1a","1b","1c","2a","2b","2c","3a","3b","3c","4a","4b","4c","5a","5b","5c","w"},{11,9,7,17,15,13,23,21,19,29,27,25,35,33,31,3})

Andy

**squiggler47** · 10-31-2010, 02:56 PM

well given the result of the last formula, this should convert it back to the original letters :-

Please Login or Register  to view this content.

**squiggler47** · 10-31-2010, 02:58 PM

and the reverse again :-

=IFERROR((LEFT(B6,1))*7+(99-CODE(RIGHT(B6,1)))*2,3)

**martindwilson** · 11-01-2010, 07:49 PM

3 threads and no acknowledgment of others you are a pain!

Excel 2007 : Converting text values to numerical values

LinkBack

Thread Tools

Rate This Thread

Display

Converting text values to numerical values

Re: Converting text values to numerical values

Re: Converting text values to numerical values

Re: Converting text values to numerical values

Re: Converting text values to numerical values

Re: Converting text values to numerical values

Re: Converting text values to numerical values

Re: Converting text values to numerical values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions