software

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?

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 Studentquot;Stuart Granthamquot; wrote:

gt; i want to use excel for pricelists but with a facility to change profit
gt; margins. At present I enter the proposed markup in one cell and calculate the
gt; figure I have to use to multiply the cost price by to acheive the profit in
gt; another cell (Cell 2). So far, so easy.
gt;
gt; I then enter the product descriptions in one vertical list and their cost
gt; prices in the adjoining column (Column B). To get the sales price I have to
gt; multiply each entry in column B by the figure calculated in Cell 2, but when
gt; I fill down the formula, the row value in Column B increases by one but so
gt; does the reference to Cell 2.
gt;
gt; Is there any way I can keep the cell 2 reference fixed whist changing the
gt; rows in column B?

Gary's Student,

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

Stuart

quot;Gary''s Studentquot; wrote:

gt; Let's say your costs are in column B and your calculated sale prices are in
gt; column C
gt;
gt; Rather than using something like
gt; =B1*A2 in cell C1 and copy down
gt; use
gt; =B1*$A$2 in cell C1 and copy down
gt;
gt; The $ prevents the reference from changing during the copy down
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Stuart Granthamquot; wrote:
gt;
gt; gt; i want to use excel for pricelists but with a facility to change profit
gt; gt; margins. At present I enter the proposed markup in one cell and calculate the
gt; gt; figure I have to use to multiply the cost price by to acheive the profit in
gt; gt; another cell (Cell 2). So far, so easy.
gt; gt;
gt; gt; I then enter the product descriptions in one vertical list and their cost
gt; gt; prices in the adjoining column (Column B). To get the sales price I have to
gt; gt; multiply each entry in column B by the figure calculated in Cell 2, but when
gt; gt; I fill down the formula, the row value in Column B increases by one but so
gt; gt; does the reference to Cell 2.
gt; gt;
gt; gt; Is there any way I can keep the cell 2 reference fixed whist changing the
gt; gt; rows in column B?