multiply range of cells by a fluctuating exchange rate

[Guest]

How can I multiply a range of cells by the (changing) contents of another
cell? For example: I have a range of cells with formulas and I want to divide
each o these cells by another specific cell -say A1 (where I input each days
exchange rate). So, the whole range gets multiplied by the content of A1.

I can`t use Copy, Paste Special, Multiply because this will multiply the
range by the current exchange rate only. Each day I only want to change the
exchange rate and then the whole table gets updated by each day's exchange
rate.

Thank you

 

[Ragdyer]

Don't understand your problem.

Construct your formulas to reference A1, where you manually enter and/or
revise the exchange rate in A1 whenever you wish.

You can simply enter the date you last revised A1 in an adjoining cell, say
B1.

Do you have a problem with this?
Maybe post some of your formulas if you think you might need some help.

 

[Guest]

Sorry, maybe I did not explain it well. I have already my financial tables
with formulas in the original curency, and I want to display the tables in
US$, too. I have a cell with the exchange rate (where I put the exchange rate
that may be 2.15 today and 2.20 tomorrow). So, there might be a method to
divide the whole table by that cell with the exchange rate. Then I just need
to input the current exchange ae and I can always show the updted US$ table.

 

[Guest]

For example:
Now:
A1*B1
A2*B2
A3*A4
etc

Desired:
(A1*B1)/E4
(A2*B2)/E4
(A3*B3)/E4
etc.
And in E4 there is the place to input the exchange rate.

 

[Ragdyer]

Again, I don't understand your problem since what you posted under "Desired"
*IS* the formula you should use.

It appears that you *already* know what to do.

What is your question?

Perhaps just relative and absolute references?

=(A1*B1)/$E$4

OR, quite simply:

=A1*B1/$E$4

 

[Guest]

Now I understand what you don't understand in my question. You are right that
my problem is not with the formula itself and in principle I "know" what to
do. Indeed, if there were few cells there would be no problem at all, I would
just input all the formulas and ready.
But I am dealing with thousands of cells. So, my poblem is not the content
but the method to multiply (or divide) them all at once by one reference
cell. So, I am asked for help to get this operational without having to
enter the formula individually but multiplying the whole range of thousands
of cells by one reference cell in one go.
I tried i with copy and Past special Multiply, but then it just takes the
number and later if the rate changes I have to do it again. Then I tried to
selct it with a Multoply formula, but it didn't work out.

 

[Ragdyer]

Just where are these "1,000's" of cells?

Are they in columns?
Are they non-contiguous cells haphazardly placed throughout the sheet?

Are they in any type of order at all?

There are many options to create and/or copy formulas easily and quickly.
For instance, if you actually had your cells situated as in your example,
with values in A1 and B1, and they filled the *entire* 2 columns, down to
B65536, you could enter a formula in C1, and by simply *double* clicking the
fill handle of C1, that formula would be duplicated down Column C almost
instantly.

So, what configuration do you have for your data cells?

 

[Guest]

Sory for making so much trouble. The data is in tables (adjacent and
non-adjeacent colums and rows, some calculation in columns other in rows) in
several worksheets in a workbook for financial projections: Income statement,
balance sheet, cash flow and a loan rapyment schedule, and that is summerized
in a summary table that picks specific items of all the other worksheets.
Some of the data is input as values (e.g. sales), other as formulas (e.g.
gross profit) always picking data from other orksheets. I was asked to
transform these tables into US$ (maintaing the oiginals). The US$ tables
would be placed at some distance (say 4 colums or rows) of the original
tables in th same sheets.
So, I have already copied the tables using the Edit Replace, it worked fine.
The next step is to transform all the numbers (the results in each cell) into
US$ using the exchange rate that is put in the data worksheet.

 

[Roger Govier]

Hi Oliver

RD has pointed out that you need an Absolute cell reference e.g $E$4 to
hold your exchange rate.
As an absolute reference it will not alter as you copy the formula down
a column or across a row in the manner RD has described.

However, from your last posting, I wonder if your problem is just that
you want the exchange rate in just on place - on your Data sheet - but
want that value to be used in various formulae in different sheets. If
so, then the easiest way (IMHO) would be to create a Named range.

Insert>Name>Define XRate Refers to 'Data Sheet'!$E$4 or whatever
is the location where you want to enter your exchange rate.

Then all of the formulae in the dollar Area of your various sheets need
to be modified to be
=(original_formula)*XRate