I am trying to convert a value in BHD to $. I have the amount in Currency,
BHD, in A1, and I have the type set as Currency, $ in A2. I am typing this
formula into A2: =A1*2.65. But I get ##VALUE!##, instead of an amount. Why
can I not multiply with currency?

"sorrywm" wrote in message
...
I am trying to convert a value in BHD to $. I have the amount in Currency,
BHD, in A1, and I have the type set as Currency, $ in A2. I am typing this
formula into A2: =A1*2.65. But I get ##VALUE!##, instead of an amount. Why
can I not multiply with currency?

*VALUE! indicates that the contents of A1 are not in the expected format. In
other words, A1 contains something other than a numerical value. My guess is
that it's text. You can test for this by putting the formula
=ISTEXT(A1)
into a spare cell.

You can only multiply by 2.65 if you start with a number. Maybe you imported
the data in A1 and it's ended up in text format? You should understand there
is an essential difference between a number displayed as currency (where the
currency symbol is merely part of the display, not the underlying data) and
a text string containing letters or symbols and numeric characters.

I checked the format of the cell in question again, just to make sure I was
not seeing things the first time. Sure enough, it is in currency format and
still will not multiply. Any clue as to why this is?

"Stephen" wrote:

"sorrywm" wrote in message
...
I am trying to convert a value in BHD to $. I have the amount in Currency,
BHD, in A1, and I have the type set as Currency, $ in A2. I am typing this
formula into A2: =A1*2.65. But I get ##VALUE!##, instead of an amount. Why
can I not multiply with currency?

*VALUE! indicates that the contents of A1 are not in the expected format. In
other words, A1 contains something other than a numerical value. My guess is
that it's text. You can test for this by putting the formula
=ISTEXT(A1)
into a spare cell.

You can only multiply by 2.65 if you start with a number. Maybe you imported
the data in A1 and it's ended up in text format? You should understand there
is an essential difference between a number displayed as currency (where the
currency symbol is merely part of the display, not the underlying data) and
a text string containing letters or symbols and numeric characters.

Curiously, when I try the ISTEXT(A1) test, I get TRUE, but when I go to
"Format Cells" for A1, it shows that it is in Currency format. Why?

"Stephen" wrote:

"sorrywm" wrote in message
...
I am trying to convert a value in BHD to $. I have the amount in Currency,
BHD, in A1, and I have the type set as Currency, $ in A2. I am typing this
formula into A2: =A1*2.65. But I get ##VALUE!##, instead of an amount. Why
can I not multiply with currency?

*VALUE! indicates that the contents of A1 are not in the expected format. In
other words, A1 contains something other than a numerical value. My guess is
that it's text. You can test for this by putting the formula
=ISTEXT(A1)
into a spare cell.

You can only multiply by 2.65 if you start with a number. Maybe you imported
the data in A1 and it's ended up in text format? You should understand there
is an essential difference between a number displayed as currency (where the
currency symbol is merely part of the display, not the underlying data) and
a text string containing letters or symbols and numeric characters.

Format shows that the cell is prepared to accept a value as currency,
not that it actually contains a currency value. For instance, if you
were to type "Fred" into the cell, there is no way that Excel could
intrerpret that as currency, but the format would not change. Moreover,
changing the format changes only the display (if possible), not the
value in the cell, so if the data was originally entered as text,
changing the format to currency will not change the value from text to a
number even if Excel knew how to interpret it as numeric (unlike the
"Fred" example).

Some ways to convert text to a numeric value (where possible) are
- Select the cell, move the cursor to the formula bar, and press enter
(effectivly re-entering the value) (requires that the cell not have a
text format, but you said it doesn't)
- copy an empty cell, select the cell in question and Edit|Paste
Special|Add

Jerry

sorrywm wrote:

Curiously, when I try the ISTEXT(A1) test, I get TRUE, but when I go to
"Format Cells" for A1, it shows that it is in Currency format. Why?

"Stephen" wrote:


"sorrywm" wrote in message
...

I am trying to convert a value in BHD to $. I have the amount in Currency,
BHD, in A1, and I have the type set as Currency, $ in A2. I am typing this
formula into A2: =A1*2.65. But I get ##VALUE!##, instead of an amount. Why
can I not multiply with currency?

*VALUE! indicates that the contents of A1 are not in the expected format. In
other words, A1 contains something other than a numerical value. My guess is
that it's text. You can test for this by putting the formula
=ISTEXT(A1)
into a spare cell.

You can only multiply by 2.65 if you start with a number. Maybe you imported
the data in A1 and it's ended up in text format? You should understand there
is an essential difference between a number displayed as currency (where the
currency symbol is merely part of the display, not the underlying data) and
a text string containing letters or symbols and numeric characters.

Thank you very much for your help. I realized after reading your post that I
should have checked to see whether the person entering the data had typed in
the "BHD" I saw before the value, or whether that displayed because it was
selected as the currency. I re-entered the value without the text preceding
it, and was then able to multiply.

"Jerry W. Lewis" wrote:

Format shows that the cell is prepared to accept a value as currency,
not that it actually contains a currency value. For instance, if you
were to type "Fred" into the cell, there is no way that Excel could
intrerpret that as currency, but the format would not change. Moreover,
changing the format changes only the display (if possible), not the
value in the cell, so if the data was originally entered as text,
changing the format to currency will not change the value from text to a
number even if Excel knew how to interpret it as numeric (unlike the
"Fred" example).

Some ways to convert text to a numeric value (where possible) are
- Select the cell, move the cursor to the formula bar, and press enter
(effectivly re-entering the value) (requires that the cell not have a
text format, but you said it doesn't)
- copy an empty cell, select the cell in question and Edit|Paste
Special|Add

Jerry

sorrywm wrote:

Curiously, when I try the ISTEXT(A1) test, I get TRUE, but when I go to
"Format Cells" for A1, it shows that it is in Currency format. Why?

"Stephen" wrote:


"sorrywm" wrote in message
...

I am trying to convert a value in BHD to $. I have the amount in Currency,
BHD, in A1, and I have the type set as Currency, $ in A2. I am typing this
formula into A2: =A1*2.65. But I get ##VALUE!##, instead of an amount. Why
can I not multiply with currency?

*VALUE! indicates that the contents of A1 are not in the expected format. In
other words, A1 contains something other than a numerical value. My guess is
that it's text. You can test for this by putting the formula
=ISTEXT(A1)
into a spare cell.

You can only multiply by 2.65 if you start with a number. Maybe you imported
the data in A1 and it's ended up in text format? You should understand there
is an essential difference between a number displayed as currency (where the
currency symbol is merely part of the display, not the underlying data) and
a text string containing letters or symbols and numeric characters.