I have a formula in Excel that needs to be run on several rows of a column based on the numbers in that row divided by one constant. When I copy that formula and apply it to every cell in the range, all of the cell numbers increment with the row, including the constant. So:
B1=127
C4='=IF(B4<>"",B4/B1,"")'
If I copy cell C4 and paste it down column C, the formula becomes
=IF(B5<>"",B5/B2,"")
=IF(B6<>"",B6/B3,"")
etc.
when what I need it to be is
=IF(B5<>"",B5/B1,"")
=IF(B6<>"",B6/B1,"")
etc.
Is there a simple way to prevent the expression from incrementing?
Best Answer
There is something called 'locked reference' in excel which you can use for this, and you use
$
symbols to lock a range. For your example, you would use:This locks the
1
inB1
so that when you copy it to rows below,1
will remain the same.If you use
$B$1
, the range will not change when you copy it down a row or across a column.