Formula needed
 

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.?

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?

"plfry" wrote:

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.?

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" wrote:

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?

"plfry" wrote:

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.?

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

"Michael" wrote:

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" wrote:

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?

"plfry" wrote:

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.?

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

"Michael" wrote:

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" wrote:

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?

"plfry" wrote:

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,

In that case try

=D1+E1/10+F1/100

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"plfry" wrote in message
...
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

"Michael" wrote:

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" wrote:

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?

"plfry" wrote:

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.?

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

"plfry" wrote:

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

"Michael" wrote:

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" wrote:

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?

"plfry" wrote:

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.?

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

"plfry" wrote:

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

"plfry" wrote:

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

"Michael" wrote:

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" wrote:

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?

"plfry" wrote:

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.?