error "expect, all currency values to be of same currency" (translated)

My guess is that you made something wrong.



In D2, the formula is:
=B*$C$2

Now, if we are fool enough to insert in D4 the formula =B3+D2, we get the logical red triangle telling that we are not allowed to add different currencies.

If we insert in D5 the formula =D2+D3 it behaves flawlessly
as well as =B2+B3.

We are even allowed to enter =D5/B5 as I did in C6.
It returns the counterpart of one Dollar in Euro.

Remember the advice of a long time computers user:

_when we get a 'wrong' result, before writing that a program behaves wrongly, check if you didn't made an error._

Yvan KOENIG (from FRANCE samedi 28 février 2009 15:39:37)

Yes, it's perfectly fool.

If column B is formatted as Euros and D as Dollars,
you understand that it would be foolish to calculate B2+D2.

If I inserts =B2*$C$2 in B3
the result would be euros and it would be incorrect to code =B3+D2.
You understand that ?

It's exactly what your formula is doing.

For Numbers, when you multiply B2 ( an Euros value) by the number stored in C2, the result _is an Euros value._
As you try to add it to D2 which is a dollar value it refuses.

To achieve your goal you must store the converted value in a cell whose format will be dollar.


Here it's quite what I posted in my first message.

The only real difference is that D2 contains a value directly entered as dollars.
In D3 the formula converts B2 in dollars.
So, when it's done, we may add D2 and D3 because both are dollars values.

It's perfectly logical.

The only other correct solution would require a function allowing us to define the currency in a formula, something like:

=CURRENCY(B2*C2;"$")*D2

Of course, an alternate one would be to leave the column B as a simple numerical column.
This time, B2*C2 would be a simple number with no currency attribute and there will be no conflict.

The design is deliberate to get rid of odd behaviors.

Yvan KOENIG (from FRANCE samedi 28 février 2009 16:39:10)

Somsut wrote:
My view of the problem is quite different. Exchange rate ($C$2) should be treated like a $/€ value. Following your arguing we would perfectly get a $ value if we calculate =B2*$C$2 ( € * $/€ = $ ). This is finally what you are doing in D2. The only thing I do differently is that I add another dollar value =B2*$C$2+D2 ($ + $ = $). At this point complaining about using wrong currencies is not fair 🙂

During the calculations, Numbers is not allowed to change a format.
There is no format $/€. You are just dreaming.

In your formula you are doing that:

(B2 € * aNumberWithNoFormat) + (D2 $)
(B2 € * aNumberWithNoFormat) is perfectly correct and the result is a number of €.
Then you try to add a number of $ which is perfectly odd.

You didn't understand the behavior of cell D2 or D3.

The formula calculates (B2 € * aNumberWithNoFormat) then a specified format urges Numbers to give it the format $.

This is what is not done in your formula and this is why I wrote that the correct way to do what you wish would be to have a CURRENCY function allowing us to write the formula:

=CURRENCY(B2*C2,"$")+D2

with this formula,
the number of € (B2*C2) would be defined as a number of $ and then it would be legible to add it D2.

We may also imagine, as you did that we are allowed to use a number defined as a rate ($/€) but at this time, like the CURRENCY function it's an utopia.

So we have to use the program with its clean, correct and _not bugged_ behavior.

I spent too much time with this perfectly odd problem.
For me, thread closed.

Yvan KOENIG (from FRANCE samedi 28 février 2009 23:04:16)

As I received a complementary message in my mailbox I pass here the sample table which is from my point of view a clean way to treat this kind of needs.



In column G the formula is:

=IF(NOT(ISBLANK(C)),C*$C$2,IF(NOT(ISBLANK(D)),D*$D$2,IF(NOT(ISBLANK(E)),E*$E$2,I F(NOT(ISBLANK(F)),F,""))))

In H3, the formula is:
=SUM($G$3:$G3)
then apply fill down.

Yvan KOENIG (from FRANCE dimanche 1 mars 2009 16:03:45)

Somsut - You are actually correct and it is a bug (or at least a missing feature) in Numbers 09.

It is quite acceptable to have a formula similar to this in real life:

(Euro_Amt * Eur USDRate) + (GBP_Amt * GBP USDRate) + USD_Amt = Total_USD

The problem here is that Apple is trying to guess what it is you (or most people) are doing in a situation with currencies and is protecting you from yourself. The problem is that in this case, Numbers can't actually "understand" the formula so it is using a simplistic rule to tell you that it is wrong when in fact it is perfectly correct.

Yes there are ways using hidden columns etc. to get around this but you shouldn't have to get around an error like this.

What should actually happen is that it should flag this as an error but allow you to ignore the error and continue processing. (Like Excel does for some errors - e.g. forumlas not the same in adjacent columns).

Head on up to the Provide Feedback option on the Numbers menu and let Apple know.

Jordan

Can't really call it a bug, just an unwanted or unnecessary feature.

You can get around the "feature" by using the PRODUCT function.

=PRODUCT(Euros,conversion)+Dollars