How do I use a calculated constant in multiple cells?
 

i want to use excel for pricelists but with a facility to change profit
margins. At present I enter the proposed markup in one cell and calculate the
figure I have to use to multiply the cost price by to acheive the profit in
another cell (Cell 2). So far, so easy.

I then enter the product descriptions in one vertical list and their cost
prices in the adjoining column (Column B). To get the sales price I have to
multiply each entry in column B by the figure calculated in Cell 2, but when
I fill down the formula, the row value in Column B increases by one but so
does the reference to Cell 2.

Is there any way I can keep the cell 2 reference fixed whist changing the
rows in column B?

How do I use a calculated constant in multiple cells?
 

Let's say your costs are in column B and your calculated sale prices are in
column C

Rather than using something like
=B1*A2 in cell C1 and copy down
use
=B1*$A$2 in cell C1 and copy down

The $ prevents the reference from changing during the copy down
--
Gary's Student


"Stuart Grantham" wrote:

i want to use excel for pricelists but with a facility to change profit
margins. At present I enter the proposed markup in one cell and calculate the
figure I have to use to multiply the cost price by to acheive the profit in
another cell (Cell 2). So far, so easy.

I then enter the product descriptions in one vertical list and their cost
prices in the adjoining column (Column B). To get the sales price I have to
multiply each entry in column B by the figure calculated in Cell 2, but when
I fill down the formula, the row value in Column B increases by one but so
does the reference to Cell 2.

Is there any way I can keep the cell 2 reference fixed whist changing the
rows in column B?

How do I use a calculated constant in multiple cells?
 

Gary's Student,

Many thanks for your suggestion - it will save me a lot of time.

Stuart

"Gary''s Student" wrote:

Let's say your costs are in column B and your calculated sale prices are in
column C

Rather than using something like
=B1*A2 in cell C1 and copy down
use
=B1*$A$2 in cell C1 and copy down

The $ prevents the reference from changing during the copy down
--
Gary's Student


"Stuart Grantham" wrote:

i want to use excel for pricelists but with a facility to change profit
margins. At present I enter the proposed markup in one cell and calculate the
figure I have to use to multiply the cost price by to acheive the profit in
another cell (Cell 2). So far, so easy.

I then enter the product descriptions in one vertical list and their cost
prices in the adjoining column (Column B). To get the sales price I have to
multiply each entry in column B by the figure calculated in Cell 2, but when
I fill down the formula, the row value in Column B increases by one but so
does the reference to Cell 2.

Is there any way I can keep the cell 2 reference fixed whist changing the
rows in column B?