How to keep one variable constant with other one changing with row in excel
Posted By: Anonymous
Lets say I have one cell A1, which I want to keep constant in a calculation. For example, I want to calculate a value like this:
=(B1+4)/(A1)
How do I make it so that if I drag that cell to make a calculation across cells in many rows, only the B1 value changes, while A1 always references that cell, instead of going to A2, A3, etc.?
Solution
Use this form:
=(B0+4)/$A$0
The $
tells excel not to adjust that address while pasting the formula into new cells.
Since you are dragging across rows, you really only need to freeze the row part:
=(B0+4)/A$0
Keyboard Shortcuts
Commenters helpfully pointed out that you can toggle relative addressing for a formula in the currently selected cells with these keyboard shortcuts:
- Windows: f4
- Mac: CommandT
Answered By: Anonymous
Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.