Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
I should, of course, have said:
=IF(INDIRECT("C4")<1,0,$K$4-INDIRECT("C4")) I'd said earlier that the SUM is unnecessary in this case. If he wants unnecessary functions we could, of course, throw in a few more and use =IF(INDIRECT("C4")<1,0,SUM(MIN(AVERAGE(MEDIAN(PROD UCT(MAX($K$4-INDIRECT("C4")))))))) :-) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
I am sorry for the confusion. I am not very computer literate. Just learning.
I have uploaded a sample file.This is the first time I have done that so I don't know if it will work. If so then you can get a better idea of what I need to accomplish. I removed the sum from some formulas. Some of the places it seems to be necessary. All I can say if that it worked. I have not tried the new formulas that you posted. There are several problems but the members of this group have helped a lot. I don't know which thing I need to copy in this message. This link should be blue writing. I don't know what is the matter. http://www.freefilehosting.net/download/3b8i3. "David Biddulph" wrote: I should, of course, have said: =IF(INDIRECT("C4")<1,0,$K$4-INDIRECT("C4")) I'd said earlier that the SUM is unnecessary in this case. If he wants unnecessary functions we could, of course, throw in a few more and use =IF(INDIRECT("C4")<1,0,SUM(MIN(AVERAGE(MEDIAN(PROD UCT(MAX($K$4-INDIRECT("C4")))))))) :-) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
I tried the formula. It works for row 4. I would like to drag it down 3 more
rows so I don't have to manually adjust the c4, k4 to c5, k5. Also if I try to move in new info I got a lot ref errors. Thanks, I hope you are able to look at the file so we can clear up the confusion. jeel "David Biddulph" wrote: I should, of course, have said: =IF(INDIRECT("C4")<1,0,$K$4-INDIRECT("C4")) I'd said earlier that the SUM is unnecessary in this case. If he wants unnecessary functions we could, of course, throw in a few more and use =IF(INDIRECT("C4")<1,0,SUM(MIN(AVERAGE(MEDIAN(PROD UCT(MAX($K$4-INDIRECT("C4")))))))) :-) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Absolute $ for a number
One more thing I forgot--I saved the file to Excel 97-2003 thus the xls
extension instead of the xlxs. I actually am working in Excel 2007. I know there are some differences but I have been told by several people that if you don't have 2007 you wouldn't be able to read the file. I can save it in the 2007 and upload it again. I see that the link works in my previous message so things will be clearer. Thanks, jeel "jeel" wrote: I tried the formula. It works for row 4. I would like to drag it down 3 more rows so I don't have to manually adjust the c4, k4 to c5, k5. Also if I try to move in new info I got a lot ref errors. Thanks, I hope you are able to look at the file so we can clear up the confusion. jeel "David Biddulph" wrote: I should, of course, have said: =IF(INDIRECT("C4")<1,0,$K$4-INDIRECT("C4")) I'd said earlier that the SUM is unnecessary in this case. If he wants unnecessary functions we could, of course, throw in a few more and use =IF(INDIRECT("C4")<1,0,SUM(MIN(AVERAGE(MEDIAN(PROD UCT(MAX($K$4-INDIRECT("C4")))))))) :-) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max absolute number | Excel Worksheet Functions | |||
Display absolute value of complex number | Excel Discussion (Misc queries) | |||
Convert displayed number to absolute value | Excel Discussion (Misc queries) | |||
How do I convert hh:mm:ss to an absolute number (26:01:32 to 26)? | Excel Discussion (Misc queries) | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) |