multiply range of cells by a fluctuating exchange rate
 

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

multiply range of cells by a fluctuating exchange rate
 

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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

multiply range of cells by a fluctuating exchange rate
 

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.




"Ragdyer" wrote:

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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

multiply range of cells by a fluctuating exchange rate
 

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" wrote:

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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

multiply range of cells by a fluctuating exchange rate
 

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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" wrote:

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.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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

multiply range of cells by a fluctuating exchange rate
 

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" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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" wrote:

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.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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

multiply range of cells by a fluctuating exchange rate
 

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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" wrote:

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

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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" wrote:

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.
--
HTH,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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

multiply range of cells by a fluctuating exchange rate
 

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.

"Ragdyer" wrote:

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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" wrote:

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

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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" wrote:

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.
--
HTH,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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

multiply range of cells by a fluctuating exchange rate
 

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.

InsertNameDefine 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

--
Regards

Roger Govier


"Oliver" wrote in message
...
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.

"Ragdyer" wrote:

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Oliver" wrote in message
...
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" wrote:

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

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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" wrote:

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.
--
HTH,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all
may
benefit
!


--------------------------------------------------------------------------
-
"Oliver" wrote in message
...
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