Can a Letter Represent a Value?

Sawyer1206

New Member
Joined
Feb 14, 2019
Messages
8
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?

MATERIALQUANTITYPRICEPEREXTENSIONLABORPEREXTENSION
Example per 11
1.00
E1.008.0E8.0
Example per 1001
1.00C0.018.0C0.08
example per 100011.00M0.0018.0M0.008

<tbody>
</tbody>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
in your example
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})
 
Upvote 0
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})
 
Upvote 0
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
 
Upvote 0
<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">*********>
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
For example:

G34 =IFERROR(E34*LOOKUP(F34,{"C","E","M"},{0.01,1,0.001})*A34,"----------")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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

Which adblocker are you using?

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
Back
Top