recognizing words as numbers...for a formula

[CONFUSED]

I want excel to recognize "light" as a value of 60 but show the text not the
value. Then I want to use this cell in a formula. Any idea how to do that?

 

[Shane Devenshire]

Hi,

You could take two approaches

1. Display Light but actually have a value of 60 in the cell
2. Test the content for "light" and then use 60

The first involves a custom format, but if you have lots of items you want
to do this with it might not meet your needs.
The second would use a formula to find the value you associate with light,
for example if there are a lot of these items with different values you could
use VLOOKUP, if only one, light, then
=IF(A1="light",60,"")
and incorporate that into a formula

 

[Ashish Mathur]

Hi,

If you just want to display 60 as Light, then you can di tit through Format

Cells > Number > Category > Custom > Type > "Light". Please note that the

cell will still continue to hold 60.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com