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.