How can I store calculations using a currency exchange rate (stored in A1 for example) but not have the result change when the exchange rate is update?
I do not want to store each exchange rate variation in a new location every time it changes.

How can I store calculations using a currency exchange rate (stored
in A1 for example) but not have the result change when the exchange
rate is update? I do not want to store each exchange rate variation
in a new location every time it changes.

Normally, exchange rates are transaction-specific, meaning they are
stored in the transaction record under a dedicated field.

Normally, each row is a separate transaction record, meaning each
record will possibly have a different exchange rate entered into its
ExchRate field.

The ExchRate field is used to calc the home (or foreign) amount for
each record.

Thus, if you expect to keep accurate transaction records then you need
to embrace entering the exchange rate for each transaction!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, 24 May 2013 10:30:31 UTC+8, Alex Skinner wrote:
How can I store calculations using a currency exchange rate (stored in A1 for example) but not have the result change when the exchange rate is update?

I do not want to store each exchange rate variation in a new location every time it changes.

Thanks Garry,
The exchange rate was merely an example of what I want to achieve. As I recall an earlier version of excel the formula =Value(A1*C1) (for example) stored the Value in the cell but this is no longer the case. Or maybe I am mistaken? It can't be an uncommon requirement and tyhere must be a way to achieve it. Ant further ideas?

On Friday, 24 May 2013 10:30:31 UTC+8, Alex Skinner wrote:
How can I store calculations using a currency exchange rate (stored
in A1 for example) but not have the result change when the exchange
rate is update?

I do not want to store each exchange rate variation in a new
location every time it changes.

Thanks Garry,
The exchange rate was merely an example of what I want to achieve.
As I recall an earlier version of excel the formula =Value(A1*C1)
(for example) stored the Value in the cell but this is no longer the
case. Or maybe I am mistaken? It can't be an uncommon requirement
and tyhere must be a way to achieve it. Ant further ideas?

What you describe works when the rate remains constant for all calcs
that use it. So...

Amount in A1
Rate in C1

..some other column in same row...
=Value($A1*$C1)
-OR-
=$A1*$C1

...where the col refs remain constant while the row refs adjust
appropriately for each row the formula is used in.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, 24 May 2013 10:30:31 UTC+8, Alex Skinner wrote:
How can I store calculations using a currency exchange rate (stored in A1 for example) but not have the result change when the exchange rate is update?

I do not want to store each exchange rate variation in a new location every time it changes.

Thanks once again Garry,

Your suggestion doesn't seem to do what I want.
The Value function converts a text string that represents a number to that number and so is not useful in this instance.

In more detail my question is:
I store a Rate (.95) in A1
Elsewhere in the sheet I might have an amount $2 in A5.
I wish the result of the calculation $A$1*A5 to be stored in C5
Next an amount of $3 in A6 and calculated value $A$1*A6 in C6

At some future time the rate changes to .97 and I wish to use this value in future calculations. All previous results in Column C are automatically updated.

If I could store the calculated value in C then no recalculation would occur.

I know that I could Copy the cells and paste as Values to achieve this result but it is cumbersome and not practical for a data entry person.

There must be many occasions where this feature is desired but I can't find any reference, even in the Excel Bible.

Alex

On Friday, 24 May 2013 10:30:31 UTC+8, Alex Skinner wrote:
How can I store calculations using a currency exchange rate (stored
in A1 for example) but not have the result change when the exchange
rate is update?

I do not want to store each exchange rate variation in a new
location every time it changes.

Thanks once again Garry,

Your suggestion doesn't seem to do what I want.
The Value function converts a text string that represents a number to
that number and so is not useful in this instance.

In more detail my question is:
I store a Rate (.95) in A1
Elsewhere in the sheet I might have an amount $2 in A5.
I wish the result of the calculation $A$1*A5 to be stored in C5
Next an amount of $3 in A6 and calculated value $A$1*A6 in C6

At some future time the rate changes to .97 and I wish to use this
value in future calculations. All previous results in Column C are
automatically updated.

If I could store the calculated value in C then no recalculation
would occur.

I know that I could Copy the cells and paste as Values to achieve
this result but it is cumbersome and not practical for a data entry
person.

There must be many occasions where this feature is desired but I
can't find any reference, even in the Excel Bible.

Alex

Try storing new rates in another cell, say above the new records that
use that rate. This will also provide you with a history of the
rates...

A1, Rate1
A2:A10, calcs that use Rate1

A11, Rate2
A12:A20, calcs that use Rate2

..and so on!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Yet again Thanks Gary,

I guess I will have to adopt this approach for the time being.

It looks as though the time has come for me to take the plunge and get into VBA programming and automate a Copy then Save Values approach to do what I really want to do.

Thanks for your input.

Alex