Advancing my knowledge

[xyzabc198]

OK, I have an excel spreadsheet.
On the spreadsheet down collum E2-E23 I have numbers ranging from 1 - 50, and then in E24 it adds up all the numbers between E2 and E23 with a simple formula.

Problem is, New lines are added and removed every day, so I have to readjust which collumns it extends between. Is their any way to say to add up lines 3-24 and to increase area 1 line everytime a line is added and decrease by 1 every time a line is removed?

 

[Yard]

If your formula is something like

=SUM(E2:E23)

then if a row is inserted between rows 2 and 23 then the formula will be adjusted automatically (and the sum will now be in E25 rather than E24).

Are you asking a different question?

 

[xyzabc198]

well for one particular area (the examples I gave were not accurate)

I have =SUM(D84:D115) and every time I try to add an area between D114 and D115 I have to redo it

 

[Richard Schollar]

If you're inserting a row when E24 is selected then your formula won't update to include the newly added row 24 (at least doesn't seem to in my testing) so you could use:

=SUM($E$3:INDEX($E:$E,ROW()-1))

As your formula in E24.

 

[xyzabc198]

Thanks Richard, worked a miracle :D

 

[lapta301]

Richard

I have spotted this thread which is of interest to me and wonder if I could trouble you to explain to a pretty basic user how this formula works.

I must say I wondered why xyzabc198 didn't want to include the value in E2 but I am sure he has his reasons.

Many thanks

Lapta301

 

[xyzabc198]

Yes I do have reasons, but it would take me about half an hour to explain lol

 

[Richard Schollar]

Sure:

In the formula =SUM($E$3:INDEX($E:$E,ROW()-1))

It look spretty much like a standard SUM formula eg =SUM($E$3:E24) except that the E24 part is substituted with

INDEX($E:$E,ROW()-1)

So we can deduce that the INDEX function actually returns a cell reference rather than just a simple value. It returns the cell in the E column which is on ROW()-1. ROW() returns the row of the cell in which the formula resides, so subtracting 1 from this gives the cell immediately above the formula cell. Hence the formula works.

Make sense?

 

[lapta301]

Richard

Many thanks for such a quick and succinct reply which to my surprise I understand.

xyzabc198 - fair comment.


Regards to you both from Surrey, what a small world.


Lapta301