Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
constant in formulas
i have excel 2003 and am learning how to use it i have written a formula
and am using g4 as constant part of the formula .when i try to copy the formulato the rest of the column g4 changes to g5, g6 ,g7 how do i keep g4 as constant? example the formula is =g4*a7*4 when i copy to cell directly below the formula changes to g5*a7*4 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
constant in formulas
If you don't want part of a reference to change, put a $ character before that part of the reference. Excel won't change it when you copy it down. E.g., =$G$4 change neither row nor column =G4 change both row and column =$G4 change row but not column =G$4 change column but not row Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 14:10:00 -0700, ed wrote: i have excel 2003 and am learning how to use it i have written a formula and am using g4 as constant part of the formula .when i try to copy the formulato the rest of the column g4 changes to g5, g6 ,g7 how do i keep g4 as constant? example the formula is =g4*a7*4 when i copy to cell directly below the formula changes to g5*a7*4 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
constant in formulas
Thank You Chip you just made everything easier for me
"Chip Pearson" wrote: If you don't want part of a reference to change, put a $ character before that part of the reference. Excel won't change it when you copy it down. E.g., =$G$4 change neither row nor column =G4 change both row and column =$G4 change row but not column =G$4 change column but not row Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 18 Oct 2008 14:10:00 -0700, ed wrote: i have excel 2003 and am learning how to use it i have written a formula and am using g4 as constant part of the formula .when i try to copy the formulato the rest of the column g4 changes to g5, g6 ,g7 how do i keep g4 as constant? example the formula is =g4*a7*4 when i copy to cell directly below the formula changes to g5*a7*4 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
constant in formulas
Hi,
Here is a discussion from another post I made today: As an additional piece of info: =C3*(D2/100) can be written =C3*D2/100 no need for parens here. If my memory serves me division and multiplication are said to be distributive, which means their order is unimportant, so you don't need the parens. Primer on relative and absolute cell references By default almost all Excel formulas are relative - they adjust when you copy them. For example =SUM(A1:A10) in cell A11 is copied to B11 it becomes =SUM(B1:B11) its references are relative to the location of the formula. You can make any portion of a reference absolute by putting a $ in front of it. An absolute reference does not change when you copy it. Cell references come in 4 variations: A1 relative $A$1 absolute $A1 mixed - the A is absolute, the 1 is relative A$1 mixed - the A is relative, the 1 is absolute These ideas are a core component of Excel's formulas and everyone should master them! When you copy a formula down a column, in a formula reference like =SUM(A1:D1), the row numbers change but the column letter do not. To make the row numbers fixed, absolute, you can modify the formula to read =SUM(A$1:D$1). We might call this absolute but actually its mixed, but since we are copying up or down a column and not left or right, all portions of the references remain unchanging. Why should you know this? If you write a formula and copy it and then find you need to go back and modify it to get it to refer to the correct cells, it usually means you don't understand the concept of absolute/relative cell references. But more importantly you are working too hard, editing those formulas. If this is helpful, please click the Yes button. -- Thanks, Shane Devenshire "ed" wrote: i have excel 2003 and am learning how to use it i have written a formula and am using g4 as constant part of the formula .when i try to copy the formulato the rest of the column g4 changes to g5, g6 ,g7 how do i keep g4 as constant? example the formula is =g4*a7*4 when i copy to cell directly below the formula changes to g5*a7*4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to set a cell value to be constant in formulas? | Excel Worksheet Functions | |||
creating a "constant" number to be used in formulas | Excel Discussion (Misc queries) | |||
Constant loan payments vs. constant payments of principal | Excel Worksheet Functions | |||
How to keep formulas constant | Excel Discussion (Misc queries) | |||
Constant? in VBA | Excel Discussion (Misc queries) |