I'm working on a spread sheet and I am using the following Formula:
=SUM(B5*(1+B4/12)-B3)

I want to use the autofiller to repeat the formula on the following rows
ONLY replacing B5 with the next sequential number, but keeping B4 and B3 as
constants as I drag the auto filler down. for instance:

B6 =SUM(B5*(1+B4/12)-B3)
B7 =SUM(B6*(1+B4/12)-B3)
B8 =SUM(B7*(1+B4/12)-B3)
B9 =SUM(B8*(1+B4/12)-B3)

When I highlight the cell and drag the auto filler down, I get :
B7 =SUM(B6*(1+B5/12)-B4)

I tried manually entering the formulas listed above on B6 through B9 and
highlighting all 4 and then dragging the auto filler down, to allow excel to
recognize the pattern that only the first cell reference was changing and not
the other two, but it continues to change all three. Is there any way to
lock B4 and B3 in the above formulas so only the first one changes?

Hi Dan,
replace your formula with
=SUM(B5*(1+$B$4/12)-$B$3)
and the use autofill, your task will be achieved

click yes below if it works


"Dan" wrote:

I'm working on a spread sheet and I am using the following Formula:
=SUM(B5*(1+B4/12)-B3)

I want to use the autofiller to repeat the formula on the following rows
ONLY replacing B5 with the next sequential number, but keeping B4 and B3 as
constants as I drag the auto filler down. for instance:

B6 =SUM(B5*(1+B4/12)-B3)
B7 =SUM(B6*(1+B4/12)-B3)
B8 =SUM(B7*(1+B4/12)-B3)
B9 =SUM(B8*(1+B4/12)-B3)

When I highlight the cell and drag the auto filler down, I get :
B7 =SUM(B6*(1+B5/12)-B4)

I tried manually entering the formulas listed above on B6 through B9 and
highlighting all 4 and then dragging the auto filler down, to allow excel to
recognize the pattern that only the first cell reference was changing and not
the other two, but it continues to change all three. Is there any way to
lock B4 and B3 in the above formulas so only the first one changes?

Worked great! Thanks alot. Clicked "yes"

"YESHWANT" wrote:

Hi Dan,
replace your formula with
=SUM(B5*(1+$B$4/12)-$B$3)
and the use autofill, your task will be achieved

click yes below if it works


"Dan" wrote:

I'm working on a spread sheet and I am using the following Formula:
=SUM(B5*(1+B4/12)-B3)

I want to use the autofiller to repeat the formula on the following rows
ONLY replacing B5 with the next sequential number, but keeping B4 and B3 as
constants as I drag the auto filler down. for instance:

B6 =SUM(B5*(1+B4/12)-B3)
B7 =SUM(B6*(1+B4/12)-B3)
B8 =SUM(B7*(1+B4/12)-B3)
B9 =SUM(B8*(1+B4/12)-B3)

When I highlight the cell and drag the auto filler down, I get :
B7 =SUM(B6*(1+B5/12)-B4)

I tried manually entering the formulas listed above on B6 through B9 and
highlighting all 4 and then dragging the auto filler down, to allow excel to
recognize the pattern that only the first cell reference was changing and not
the other two, but it continues to change all three. Is there any way to
lock B4 and B3 in the above formulas so only the first one changes?

good ............... tks for the response

"Dan" wrote:

Worked great! Thanks alot. Clicked "yes"

"YESHWANT" wrote:

Hi Dan,
replace your formula with
=SUM(B5*(1+$B$4/12)-$B$3)
and the use autofill, your task will be achieved

click yes below if it works


"Dan" wrote:

I'm working on a spread sheet and I am using the following Formula:
=SUM(B5*(1+B4/12)-B3)

I want to use the autofiller to repeat the formula on the following rows
ONLY replacing B5 with the next sequential number, but keeping B4 and B3 as
constants as I drag the auto filler down. for instance:

B6 =SUM(B5*(1+B4/12)-B3)
B7 =SUM(B6*(1+B4/12)-B3)
B8 =SUM(B7*(1+B4/12)-B3)
B9 =SUM(B8*(1+B4/12)-B3)

When I highlight the cell and drag the auto filler down, I get :
B7 =SUM(B6*(1+B5/12)-B4)

I tried manually entering the formulas listed above on B6 through B9 and
highlighting all 4 and then dragging the auto filler down, to allow excel to
recognize the pattern that only the first cell reference was changing and not
the other two, but it continues to change all three. Is there any way to
lock B4 and B3 in the above formulas so only the first one changes?

Have you missed something out of the formula?
You've calculated B5*(1+$B$4/12)-$B$3 and then you've told Excel to use the
SUM function but you haven't told it what to add to (B5*(1+$B$4/12)-$B$3)
Did you just mean =B5*(1+$B$4/12)-$B$3 ?
Excel help for the SUM function will tell you what it does, and what its
syntax is.
--
David Biddulph

YESHWANT wrote:
Hi Dan,
replace your formula with
=SUM(B5*(1+$B$4/12)-$B$3)
and the use autofill, your task will be achieved

click yes below if it works


"Dan" wrote:

I'm working on a spread sheet and I am using the following Formula:
=SUM(B5*(1+B4/12)-B3)

I want to use the autofiller to repeat the formula on the following
rows ONLY replacing B5 with the next sequential number, but keeping
B4 and B3 as constants as I drag the auto filler down. for instance:

B6 =SUM(B5*(1+B4/12)-B3)
B7 =SUM(B6*(1+B4/12)-B3)
B8 =SUM(B7*(1+B4/12)-B3)
B9 =SUM(B8*(1+B4/12)-B3)

When I highlight the cell and drag the auto filler down, I get :
B7 =SUM(B6*(1+B5/12)-B4)

I tried manually entering the formulas listed above on B6 through B9
and highlighting all 4 and then dragging the auto filler down, to
allow excel to recognize the pattern that only the first cell
reference was changing and not the other two, but it continues to
change all three. Is there any way to lock B4 and B3 in the above
formulas so only the first one changes?