BHBMHT
asked on
Is there a way to make a static variable in a division formula in Excel 2007
I am trying divide multiple cell by the same cell, but when I go to drag down my autofill box, it changes the 2nd value (which I want to remain static).
For example, I want
=B1/C33
=B2/C33
=B3/C33
...all the way to B30
When I copy the first two cell and try to drag it down (so it populates the rest), it changes the C33 value as well. Is there a way to make it so it stays static?
2nd part of the question is when I do a sort, it changes the C33 part again, thus messing up the division again. So if there is a way to have it stay static (I would want the first variable to change becuase it would be on a new line once sorted)?
For example, I want
=B1/C33
=B2/C33
=B3/C33
...all the way to B30
When I copy the first two cell and try to drag it down (so it populates the rest), it changes the C33 value as well. Is there a way to make it so it stays static?
2nd part of the question is when I do a sort, it changes the C33 part again, thus messing up the division again. So if there is a way to have it stay static (I would want the first variable to change becuase it would be on a new line once sorted)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Using the $ simbol will fix the coordinate of the row or the column.
Example:
C$33 - will fix the row number, no matter where you copy the formula, it will mantain the reference to row 33
$C33 - will fix the column letter.
$C$33 - will fix the row number and column letter
jppinto
Example:
C$33 - will fix the row number, no matter where you copy the formula, it will mantain the reference to row 33
$C33 - will fix the column letter.
$C$33 - will fix the row number and column letter
jppinto
ASKER
That is EXACTLY what I was needing. Thanks!
if you copy C33, column and row info will shift, by putting $ signs in front of column, row or both, you fix that dimension.
In your case, you would use
=b1/$C$33
and could copy it wherever you want. The B1 part would shift, but C33 is fixed by the $ signs.
Thomas