Set a letter to represent a value

Mandie09

New Member
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.

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

pgc01

MrExcel MVP
Hi

Use:

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

Rick Rothstein

MrExcel MVP
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.

Last edited:

pgc01

MrExcel MVP
Thank you Rick, I missed it.

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

Mandie09

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

Mandie09

New Member
What does the 2,1 represent?

Rick Rothstein

MrExcel MVP
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)?

Replies
3
Views
189
Replies
10
Views
332
Replies
2
Views
146
Replies
7
Views
236
Replies
4
Views
110

Threads
1,186,165
Messages
5,956,332
Members
438,247
Latest member
UZev

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back