When writing formulas I use the $ to make it stay the same when I drag it down.
If a number is part of a formula, is there a way to make a number stay the
same when dragging down? Thanks jeel

If you have a formula like =4*B1 (or =4*B$1, or 4*$B$1, or =4*$B1), the
number 4 in the formula should not change when you drag it down, so I don't
understand your question.
--
David Biddulph

"jeel" wrote in message
...
When writing formulas I use the $ to make it stay the same when I drag it
down.
If a number is part of a formula, is there a way to make a number stay the
same when dragging down? Thanks jeel

Sorry, I was not clear. I have this formula in cell d4
=IF($C$4<1,0,SUM($K$4-$C$4)). If I move the data in cell
a4, b4, c4 to cell a11, b11, c11 the formula in d4 changes to
=IF($C$11<1,0,SUM($K$4-$C$11)). I want the formula in cell d4 to stay the
same. This sort of like a small table. 8 columns wide and 7 rows deep. There
are 16 of these small groups to a page in my report. I want to move the info
in columns a,b,c and have the formulas in d,e,f,g,h stay the same. I don't
know much about tables. Is that the answer?Thank you, jeel

If you have a formula like =4*B1 (or =4*B$1, or 4*$B$1, or =4*$B1), the
number 4 in the formula should not change when you drag it down, so I don't
understand your question.
--
David Biddulph

"jeel" wrote in message
...
When writing formulas I use the $ to make it stay the same when I drag it
down.
If a number is part of a formula, is there a way to make a number stay the
same when dragging down? Thanks jeel

With =IF($C$4<1,0,SUM($K$4-$C$4)) in D4 if I drag it down to D11, or copy to
D11, it remains unchanged, so I don't know how you've got it to change to
=IF($C$11<1,0,SUM($K$4-$C$11)).
If you change the references from absolute to relative by removing the $
signs before the 4, and start with =IF($C4<1,0,SUM($K$4-$C4)), then it would
change to =IF($C11<1,0,SUM($K$4-$C11)) as you copy it down, butr that isn't
what you say you've done.

Another question is why you've got the SUM function in that formula? What
do you think SUM($K$4-$C$4) does for you that $K$4-$C$4 wouldn't? You may
wish to look in Excel help to remind yourself what SUM does.
--
David Biddulph

"jeel" wrote in message
...
Sorry, I was not clear. I have this formula in cell d4
=IF($C$4<1,0,SUM($K$4-$C$4)). If I move the data in cell
a4, b4, c4 to cell a11, b11, c11 the formula in d4 changes to
=IF($C$11<1,0,SUM($K$4-$C$11)). I want the formula in cell d4 to stay the
same. This sort of like a small table. 8 columns wide and 7 rows deep.
There
are 16 of these small groups to a page in my report. I want to move the
info
in columns a,b,c and have the formulas in d,e,f,g,h stay the same. I don't
know much about tables. Is that the answer?Thank you, jeel

If you have a formula like =4*B1 (or =4*B$1, or 4*$B$1, or =4*$B1), the
number 4 in the formula should not change when you drag it down, so I
don't
understand your question.
--
David Biddulph

"jeel" wrote in message
...
When writing formulas I use the $ to make it stay the same when I drag
it
down.
If a number is part of a formula, is there a way to make a number stay
the
same when dragging down? Thanks jeel

David

Jeel is not dragging the D4 formula down column D. He is cutting and pasting
A4:C4 down to A11:C11

This will definitely change the formula in D4 as described.

My question to Jeel is........if you cut the data from A4:C4 why have a formula
in D4 referencing those cells?

You have left something out that I can't put my finger on.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 15:24:19 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

With =IF($C$4<1,0,SUM($K$4-$C$4)) in D4 if I drag it down to D11, or copy to
D11, it remains unchanged, so I don't know how you've got it to change to
=IF($C$11<1,0,SUM($K$4-$C$11)).
If you change the references from absolute to relative by removing the $
signs before the 4, and start with =IF($C4<1,0,SUM($K$4-$C4)), then it would
change to =IF($C11<1,0,SUM($K$4-$C11)) as you copy it down, butr that isn't
what you say you've done.

Another question is why you've got the SUM function in that formula? What
do you think SUM($K$4-$C$4) does for you that $K$4-$C$4 wouldn't? You may
wish to look in Excel help to remind yourself what SUM does.

Ah, yes. I understand what you're saying, Gord, but am equally confused as
to why the OP is doing it that way.

If he wants the formula to say unchanged in that situation, then perhaps:
=IF(INDIRECT("C4")<1,0,SUM($K$4-INDIRECT("C4")))
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
David

Jeel is not dragging the D4 formula down column D. He is cutting and
pasting
A4:C4 down to A11:C11

This will definitely change the formula in D4 as described.

My question to Jeel is........if you cut the data from A4:C4 why have a
formula
in D4 referencing those cells?

You have left something out that I can't put my finger on.


Gord Dibben MS Excel MVP

On Wed, 30 Jan 2008 15:24:19 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

With =IF($C$4<1,0,SUM($K$4-$C$4)) in D4 if I drag it down to D11, or copy
to
D11, it remains unchanged, so I don't know how you've got it to change to
=IF($C$11<1,0,SUM($K$4-$C$11)).
If you change the references from absolute to relative by removing the $
signs before the 4, and start with =IF($C4<1,0,SUM($K$4-$C4)), then it
would
change to =IF($C11<1,0,SUM($K$4-$C11)) as you copy it down, butr that
isn't
what you say you've done.

Another question is why you've got the SUM function in that formula? What
do you think SUM($K$4-$C$4) does for you that $K$4-$C$4 wouldn't? You may
wish to look in Excel help to remind yourself what SUM does.