# Can a Letter Represent a Value?

#### Sawyer1206

##### New Member
Brand new to the forum and fairly new to Excel and my question is:

Can I format a cell so when I place a letter it will represent a numerical value in a formula?

Here's the scenario: I want to format a cell so when I type in the letter 'E' a multiplier of '1' is placed in a formula but the letter' 'E' is displayed in the cell. I would need to do the same for 'C' which would represent '.01' in a formula and again with 'M' which would represent .001.

Can this be Done?

 MATERIAL QUANTITY PRICE PER EXTENSION LABOR PER EXTENSION Example per 1 1 1.00 E 1.00 8.0 E 8.0 Example per 100 1 1.00 C 0.01 8.0 C 0.08 example per 1000 1 1.00 M 0.001 8.0 M 0.008

<tbody>
</tbody>

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
you put E,C or M into Column D and in `column E you want 1, 0.01 & 0.001 to display

if so,
then in E2 put the formula
=LOOKUP(D2,{"E","C","M"},{1,0.01,0.001})

The values in a LOOKUP function must be in sorted order, so the formula would be:

=LOOKUP(F2,{"C","E","M"},{0.01,1,0.001})

The values in a LOOKUP function must be in sorted order, so the formula would be:

=LOOKUP(F2,{"C","E","M"},{0.01,1,0.001})
This is the spreadsheet I'm using. Mu current method of use is:

3/4" EMT CONDUIT is sold and labored by 100' or 'C' and my current setup is:

PER 100
G34 =E34*.01*A34 AND D34 =B34*.01*A34

PER 1000
G41 =E41*.001*A41 AND D41 =B41*.001*A41

I hope this makes sense but if I could 'C' and 'F' columns as multipliers .01 for C and .001 for M I could enter the cell into my formula and automate the sheet.

Can this be done or am I just confusing everyone?

https://imgur.com/a/eKMSMMp

<blockquote class="imgur-embed-pub" lang="en" data-id="a/eKMSMMp"><a href="//imgur.com/eKMSMMp"></a></blockquote>******** async src="//s.imgur.com/min/embed.js" charset="utf-8">*********>

The values in a LOOKUP function must be in sorted order, so the formula would be:

=LOOKUP(F2,{"C","E","M"},{0.01,1,0.001})
This is the spreadsheet I'm using. Mu current method of use is:

3/4" EMT CONDUIT is sold and labored by 100' or 'C' and my current setup is:

PER 100
G34 =E34*.01*A34 AND D34 =B34*.01*A34

PER 1000
G41 =E41*.001*A41 AND D41 =B41*.001*A41

I hope this makes sense but if I could 'C' and 'F' columns as multipliers .01 for C and .001 for M I could enter the cell into my formula and automate the sheet.

Can this be done or am I just confusing everyone?

https://imgur.com/a/eKMSMMp

you said

G34 =E34*.01*A34 AND D34 =B34*.01*A34

G34 =E34*LOOKUP(F34,{"C","E","M"},{0.01,1,0.001})*A34
D34 =B34*
LOOKUP(C34,{"C","E","M"},{0.01,1,0.001})*A34

G41 =E41*LOOKUP(F41,{"C","E","M"},{0.01,1,0.001})*A41
D41 =B41*
LOOKUP(C41,{"C","E","M"},{0.01,1,0.001})*A41

Where F & C column has C,E,M
I may not have the formulas the correct way round

Last edited:
you said

G34 =E34*.01*A34 AND D34 =B34*.01*A34

G34 =E34*LOOKUP(F34,{"C","E","M"},{0.01,1,0.001})*A34
D34 =B34*
LOOKUP(C34,{"C","E","M"},{0.01,1,0.001})*A34

G41 =E41*LOOKUP(F41,{"C","E","M"},{0.01,1,0.001})*A41
D41 =B41*
LOOKUP(C41,{"C","E","M"},{0.01,1,0.001})*A41

Where F & C column has C,E,M
I may not have the formulas the correct way round

YES...YES...YES!!!! THAT WORKS!!!

But, When I don't have a letter in cell 'C' or "F' I get the #NA in the formula cell. Can I have it place "---------" in the cell until the other cells are populated?

Thanks so much for the help, this is making my estimating faster and more efficient for sure.

For example:

G34 =IFERROR(E34*LOOKUP(F34,{"C","E","M"},{0.01,1,0.001})*A34,"----------")

YOU ARE KILLING IT ERIC!!! WORKS PERFECT!!! I've been trying to figure this out for a long time now. I'm not trying to be a pain but I do have one more question. I SWEAR!

My formula for "D" column is D34 =ROUND(M34*A34,0) Our policy is to round to the nearest dollar and I'm one step away from finishing the spreadsheet. I've been playing with entering the function with no success.

Replies
4
Views
173
Replies
5
Views
526
Replies
3
Views
83
Replies
1
Views
259
Replies
14
Views
489

### Forum statistics

1,196,480
Messages
6,015,450
Members
441,896
Latest member
clomah ### 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.

### Which adblocker are you using?    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

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