Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
Hi,
As a starting point (irrelevant of the fact I won't get a value, because I'll apply the principal once I've got it working), my spreadsheet contains a cell with the function '=sum(a2*a1)'.
If I add a new row, the function isn't carried over. How do I ensure it is, and how do I ensure it only iterates the first value e.g. '=sum(a2*a1)' becomes '=sum(a3*a1)' ?
Cheers,
try =sum(a3*$a$1)
Just 'drag' the formula down, if you use the $ sign in front that cell reference is fixed.
e.g.
if the formula in row 2 is
=sum(a2*$a$1)
and you drag the formula down to row 3 it becomes
=sum(a3*$a$1)
>> try =sum(a3*$a$1)
That worked. I didn't know about the $ feature. Cheers.
Not sure what you're trying to achieve.
If you're trying to add up a block of cells the function is
=SUM(A1:A2)
If you then add a row between 1 and 2, the function should update to SUM(A1:A3).
If you want to sum a set of numbers up to a given row, use =SUM($A$1:A2) then paste or fill into cells. The $ makes it a fixed reference; default is a relative reference to the position of the cell in the formula.
In cell B1
- if you use $A1 it tells Excel to pull the number from Column A in the row above
- if you use A$1 it will pull the number in row one from whatever column you put the reference into
- if you use $A$1 it will always return the value for cell A1
You can use =SUM($A$1:A2) in, say B2 and then it will fill into B3 as SUM($A$1:A3), int B4 as ($A$1:A4) etc
- if you put
Pressing F4 "locks" the cell ie puts the "$"
Next question:
If I add a row, the function from the previous cell isn't automatically copied into the next cell. I'd have to copy and paste the function. Is this the case or can I set excel to automatically copy the function across? Note that I dragging isn't an option - this has to be as user friendly as possible...
When you say "adding a row" are your simpleton users literally inserting a row in the sheet or are they just typing in the cells in the next row down below the current last one?
My simpleton users are inserting a new row. You see, I'm introducing the concept of a 'spreadsheet' into the office so it's all pretty exciting for them.
Does the row have to be at the bottom or can it be at the top?
I might have to email you the spreadsheet 🙂
Ok, the new lines would be at the bottom.
monsta - do that, i'll look at it later. away from my desk at the moment
google relative and fixed cell address in excel for an explanation of what $ does.