Excel changing a formula. I want it to not change. Make it stop?
September 28, 2015 6:24 PM Subscribe
I have a simple spreadsheet for scoring. I have 7 rows of data. In row 8, I have the formula =SUM(G1:G5). I want Excel to give me the sum of the first 5 rows of data, and ignore rows 6 and 7.
So I'm filling in data. I enter a number in row 1, 2, 3, 4, 5... so far so good, the sum is correct. Then I enter a number in row 6 and the formula in row 8 changes itself to =SUM(G1:G6).
Likewise I enter a number in row 7 and again the formula magically changes itself to =SUM(G1:G7). I don't want it to do this. How do I tell it to leave the formula as I entered it? (For the purpose of scoring I have to enter data in rows 6 and 7; these are important for recording but do not get used in the total.) Excel 2010.
Likewise I enter a number in row 7 and again the formula magically changes itself to =SUM(G1:G7). I don't want it to do this. How do I tell it to leave the formula as I entered it? (For the purpose of scoring I have to enter data in rows 6 and 7; these are important for recording but do not get used in the total.) Excel 2010.
Response by poster: Brilliant! It works. Thank you.
posted by evilmomlady at 6:33 PM on September 28, 2015
posted by evilmomlady at 6:33 PM on September 28, 2015
Best answer: If you're on Windows, you can do this by placing the cursor in the cell number (in the formula bar, while the cell is selected) and pressing F4. The first press gives you a $G$5. The second gives you $G5 (so the column letter remains while the row number can change if it's filled to another cell) and the third gives you G$5. So you can do it for cell/row, just cell, or just row.
posted by chesty_a_arthur at 6:57 PM on September 28, 2015 [4 favorites]
posted by chesty_a_arthur at 6:57 PM on September 28, 2015 [4 favorites]
Best answer: Fixed versus Relative cell ranges, for anybody coming along and needing search terms.
posted by notyou at 9:19 PM on September 28, 2015 [5 favorites]
posted by notyou at 9:19 PM on September 28, 2015 [5 favorites]
Response by poster: Thanks, odinsdream, I will try that at our next meet. The spreadsheet is from the league HQ and has some other quirks so I would not be surprised if that is exactly what is happening.
posted by evilmomlady at 6:34 PM on September 29, 2015
posted by evilmomlady at 6:34 PM on September 29, 2015
This thread is closed to new comments.
posted by DoubleLune at 6:29 PM on September 28, 2015 [5 favorites]