A formula that adds 12 to a text value to give a text value in return

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

I have been using the formula you guys supplied me with and it works like a dream but in the last part of my spreadsheet I have to change the formula slightly.

I need to add the value of 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

**scottylad2** · 10-31-2010, 02:41 PM

Originally Posted by andybocchi

I have been using the formula you guys supplied me with and it works like a dream but in the last part of my spreadsheet I have to change the formula slightly.

I need to add the value of 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

What would you like returned for 12 entered into CP5? ie 1a in there will return 11 in the output cell, what would you like for 12?

**scottylad2** · 10-31-2010, 02:46 PM

using the sam logic already applied

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

**royUK** · 10-31-2010, 02:52 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**andybocchi** · 10-31-2010, 03:03 PM

Sorry maybe im not explaining myself clearly.

I have a cell AH5 that has a text entry e.g. 2a, which converts into a numerical value of 17, i need to know how i can amend the formula that i have been using to simply add 12 to the value 17, and output the answer into its text equivalent which would be 29 which is a 4a.

Sorry is that clearer, its hard to explain, thanks

**martindwilson** · 10-31-2010, 03:04 PM

not only is title wrong this seems to being dealt with in this thread
http://www.excelforum.com/excel-2007...al-values.html

**martindwilson** · 10-31-2010, 03:23 PM

=LOOKUP(A1+12,{3,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35},{"w","1c","1b","1a","2c","2b","2a","3c","3b","3a","4c","4b","4a","5c","5b","5a"})
where a1 is your original value
having said that if you construct a table with your values its easer to maintain and use a simple vlookup or index/ match rather than having to carefully type those strings in (mostly because lookup requires the lookup range to be in ascending order)