Calculate different Currencies in the same column?

There is a way to detect the type of currency. Addition of two different money types is an error in Numbers (but note that SUM does not create the same error, it must be the + operator). We can use that to our advantage.

A1= $1 (formatted as dollar currency)
Column H is a mix of euros and dollars
Column I =IF(ISERROR(H+$A$1),"euro","dollar")

=SUMIF(I,"dollar",H) will sum up the dollars
=SUMIF(I,"euro",H) will sum up the euros

=SUM(H6,H10, and other cells with €)
=SUM(H9,H12, and other cells with $)

As far as I know, a formula can't "guess" the currency used in a given cell.

Given that, I think that a better answer would be to use two columns, one for € amounts, one for $ amounts.

Yvan KOENIG (VALLAURIS, France) mardi 18 janvier 2011 11:25:33

Well seen Badunit

Just a drawback.

If the document must be open in a different country, say in France, every currency amounts will be converted in dollars (or in euros, I don't remember exactly).

If they are in two columns, it would be easy to restore the correct currency.
If they are in a single one it will be a mess.

Yvan KOENIG (VALLAURIS, France) mardi 18 janvier 2011 22:04:11

I've never had the need or opportunity to open documents in different localizations. It is odd that it converts currencies, ignoring the chosen cell format. That would be a mess. Does it do this for cells formatted specifically as currency or does it do it only to cells in which someone has typed in a currency but left the format as automatic?

Badunit wrote:
I've never had the need or opportunity to open documents in different localizations. It is odd that it converts currencies, ignoring the chosen cell format. That would be a mess. Does it do this for cells formatted specifically as currency or does it do it only to cells in which someone has typed in a currency but left the format as automatic?

As I wrote, I don't remember exactly.

Go to my idisk :

<http://public.me.com/koenigyvan>

Download :

For_iWork:euros&dollars.numbers.zip

Unpack it and open it in Numbers.

column B is formatted as currency - euro
column C is formatted as currency - dollar US
column E is left as Automatic



Would be fine to know what you get on your side.

As you may see, here, the values stored in column E as dollars ones are treated as strings.
I guess that on your machine, they will be treated as numbers.

Yvan KOENIG (VALLAURIS, France) mercredi 19 janvier 2011 11:28:45

I downloaded and opened the file. It looks identical to the image you posted except that column C does not have "US" after the dollar symbol. I was surprised that the currency symbol remained on the right of the number, as in your post. It is always to the left of the number for the US. It moves to the left if I reformat the cells.

The column E values that are in euros are formatted as "automatic" but also show the formatting options for currency. The dollar values are simply "automatic". If I remove the space between the $ and the number, the currency formatting options appear and the values align on the right side like the euros are.

On a French system, the currency appears on the right.

The currencies symbols are localized with an old piece of code using obsolete symbols. I reported that to Apple since the delivery of Numbers '08. Once, Tom Gewecke posted the name of the tool used by Apple because I was guessing (wrongly) that Apple invented its own list.

There is an official list defined by ISO 4217 but Apple seems to ignore what is ISO.
They do the same with date format (ISO 8601 format is YYYY-MM-DD) and with "décalage horaire" which they continue to name *_Time to GMT_* when ISO 8601 name it *_Time to UTC_* which is more appropriate.

If you search with the key string *_currencies OR currency_*
in the Numbers area with time set to all, you will see that
these symbols
and
the fact that users didn't understand that when they used the formula *_= time-value x currency-value_* they got a time-value as result
and that if they used *_= currency-value x time-value_*, they got a currency-value as result
are the problems which surfaced most of the time.

I forgot the second one which no longer strike in Numbers '09. Now,

*_= time-value x currency-value_* and *_= currency-value x time-value_* return the red triangle flagging an error.

I typed a x as multiplication operator to get the wanted underlined bold style 😉

If we replace time-value by duration-value, the result is a bare number as long as we don't apply a currency style.

In my file the problem with dollars value in column E treated as strings was my fault. Replacing $ by $US force the app to recognize that the entry is a currency value.

Back to the point of departure, I'm glad to learn that the problem with currencies fooled when documents are open in different locations is gone.
I would have be more glad to receive a message from Apple telling that a reported oddity was killed 😟

I don't know the way they treated the symbols problem in the version to come.
I think that if they use the same odd scheme, it will be useful to ask regularly users to file a feedback about that.

Yvan KOENIG (VALLAURIS, France) mercredi 19 janvier 2011 16:48:08