Set a letter to represent a value

[Mandie09]

I need to be able to type in 0, S, or D into a cell and it stand for a number value such as, 0=0, S=1.5, D=3. I need to be able to change this cell to any of those 3 and it automatically change the number value. In the cells I already have =SUM(G5*H2+G2) all the way through H17. I need to be able to change G2 to either 0, S, or D and it change the value accordingly.

 

[pgc01]

Hi

Use:

=SUM(G5*H2+LOOKUP(2,1/(G2={0,"D","S"}),{0,3,1.5}))

 

[Rick Rothstein]

Another way...

=G5*H2+1.5*(SEARCH(G2,"0SD")-1)

You did not really need the SUM function call as you are already adding the numbers directly using the + sign.

 

[pgc01]

Thank you Rick, I missed it.

=G5*H2+LOOKUP(2,1/(G2={0,"D","S"}),{0,3,1.5})

 

[Mandie09]

This is perfect! You are a life saver! Thank you!

 

[Mandie09]

What does the 2,1 represent?

 

[Rick Rothstein]

What does the 2,1 represent?

The 2 is the first argument to the LOOKUP function, the comma delimits the first argument from the second argument, the 1 is the numerator for the fraction that makes up the second argument to the LOOKUP function, namely this...

1/(G2={0,"D","S"})

Just out of curiosity, did you see the formula I posted in Message #3 (you may find it easier to figure out how it works)?