Can a Letter Represent a Value?

[Sawyer1206]

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?

[TABLE="width: 1000"]
<tbody>[TR]
[TD]MATERIAL[/TD]
[TD]QUANTITY[/TD]
[TD]PRICE[/TD]
[TD]PER[/TD]
[TD]EXTENSION[/TD]
[TD]LABOR[/TD]
[TD]PER[/TD]
[TD]EXTENSION[/TD]
[/TR]
[TR]
[TD]Example per 1[/TD]
[TD]1
[/TD]
[TD]1.00
[/TD]
[TD]E[/TD]
[TD]1.00[/TD]
[TD]8.0[/TD]
[TD]E[/TD]
[TD]8.0[/TD]
[/TR]
[TR]
[TD]Example per 100[/TD]
[TD]1
[/TD]
[TD]1.00[/TD]
[TD]C[/TD]
[TD]0.01[/TD]
[TD]8.0[/TD]
[TD]C[/TD]
[TD]0.08[/TD]
[/TR]
[TR]
[TD]example per 1000[/TD]
[TD]1[/TD]
[TD]1.00[/TD]
[TD]M[/TD]
[TD]0.001[/TD]
[TD]8.0[/TD]
[TD]M[/TD]
[TD]0.008[/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

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})

 

[Eric W]

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})

 

[Sawyer1206]

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

 

[Sawyer1206]

<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">*********>

 

[Sawyer1206]

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

 

[etaf]

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

 

[Sawyer1206]

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.

 

[Eric W]

For example:

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

 

[Sawyer1206]

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.