Formula needed

[plfry]

I have a user who has made an Excel spreadsheet to track inventory that she
has in our gift shop. She has one column where she records a series of
letters that gets printed on a price tag, so the shop workers know what the
wholesale price of an item is. For example, she may enter CHC, which means
that the price is 2.72. What we need is some way to convert that CHC into
2.72 in another column. The values of the letters always remain the same,
for example, C=2, D=5, H=7, etc. Is there any way to write a formula like IF
column A = "C", then enter a value of 2 in column G, if column A = "D", then
enter 5, etc.?

 

[plfry]

Ok, I saw a previous post that helped me write a formula - I now have the
letters converting to numbers. But now I need the numbers in the three new
columns to display as one number in another column, so that I can then format
that number as a dollar amount. For example, column D has a 2 in it, column
E has a 3 in it, column F has a 7 in it. I now want column H to display 237,
which I can then format to show 2.37. Any ideas?

 

[Michael]

Hi
If data is in say , D1, E1 and F1, use this formula to combine them.
=D1&E1&F1

Copy down as far as you need by dragging the little black box in the bottom
right of your formula cell

Regards
Michael

 

[plfry]

Wow - that way easy - thanks - now just one more thing - it does not seem to
want to allow me to format the combined number as a dollar amount. Any ideas
for doing that?

plfry

 

[plfry]

Wow - that was easy - thanks - now just one more thing...it does not seem to
want to allow me to format the combined number as a dollar amount - any way
to do that?

plfry

 

[Sandy Mann]

Plfry,

In that case try

=D1+E1/10+F1/100

HTH

Sandy

 

[plfry]

I figured it out - I had to use VALUE and then divide the value by 100 so
that 235 shows up as $2.35. Thanks!

plfry

 

[Michael]

Glad I could help.
Another thing, if your decimal point is in the same place, you could also
use this formula instead
="$"&D1&"."&E1&F1
This would incorporate the dollar sign as well as putting a decimal point
after the first number.
Obviously the decimal point position could also become a nuisance !

Regards
Michael