Running totals using an array of odd cells

[TICKERTYBOO]

Hi Guys,

I am having difficulty being able to set up a simmple formula to create a 'running total' cell, which adds up an array of seperate cells & more importantly when I change to figures/numbers in the sperate cells, they keep on adding up in the running total?

I am using cells: N4 N7 N10 N13 N16 N19 N22 N25 N28 N31 N34 N37 N40 N43 N47 N50

The running total is in cell 'N59'

So say they is the number 10 in each of the long list of cells above (N4 ~ N50) 16 cells x 10 = 160 in the running total.

Then say next month I need to change to amounts in the cells etc: N4 (5) N7 (30) N10 (41) N13(12) N16(27) so the new 'running total' should be (114 + 160) = 274

P.S I cannot use the 'empty cells' in the same column as these have different data in!!

What is an easy formula for this as I have spent way to long trying to figure it out to no avail................please help??

 

[oldbrewer]

This is easy. make A1 in sheet 2 equal to this months total. (=sheet1!N59)
in n59 put =n4+n5+..........n50 +sheet2! A1 +sheet2!B1 +sheet2! C1 etc

the blank cells will not affect the total in month 1 or month 2

you need to remember to copy sheet2!A1 and edit paste special values to lock the value B EFORE you make changes in month 2

 

[TICKERTYBOO]

Hi Old Brewer
Thanks for getting back to me so quickly, but I need to be able to do it ALL on the same sheet as I have more 'sheets' in this workbook than your average Bedding shop!! (30 in total!)

 

[XOR LX]

Hi,

Can I just ask if the last two in your list of cell references (N47 and N50) are correct? It's only since they seem to break the pattern of being every third cell (previous to N47 is N43).

Regards

 

[oldbrewer]

how many columns in the sheet - could you use for example EA1

 

[TICKERTYBOO]

Hi XOR LX,

The last cell in the column is N55 & the running Total is in cell N59. I hope that helps???

 

[TICKERTYBOO]

Hi,

The colums in this particular sheet run up to column 'AH'.

All the sheets in the worksheet have a very similar array of columns & rows, all with the same formulas etc, It's just this one which is driving me around the twist!!! LOL PLEASE HELP???!!!!

 

[oldbrewer]

can you make a new sheet in your workbook called runtot?

then in that sheet create a simple macro that - first of all sets cells(1,3) of runtot to cells(1,3)+1 - then reads N59 in sheet 1 and puts the value in A1 of runtot, then set B1 to equal A1 and all subsequent cells in B column to this month total PLUS last month running total

so in B2 you will have =A2+B1

now you have the running total for each month available to paste back in N59 AFTER you have processed the current month total via this macro

in month 2 run the macro - thus C1 becomes 2 and the value from N59 is pasted to cells(cells(1,3),1) ie A2


if you have more sheets that you need to process, just put extra blocks of code in the macro

 

[XOR LX]

"The last cell in the column is N55 & the running Total is in cell N59. I hope that helps???"

Thanks, but that didn't really answer my question, which was concerned with the apparent pattern in the references you are wishing to sum. Can you re-read and confirm whether it is precisely every third cell that you are concerned with (and therefore that N47 should actually read N46, etc.)? And if not, can you give them all in full?

Regards

 

[TICKERTYBOO]

Hi XOR LX,

Yes it is every third cell, it's just when it gets to the bottom N59 (the actual running total cell) that it has a extra space (row)!! (N4 N7 N10 N13 N16 N19 N22 N25 N28 N31 N34 N37 N40 N43 N46 N49 N52 & N55

Whoops!!! made a bit of a hicup with the row numbers previously!

I hope that helps?????????