Excel help

[gehenna]

Hello Everyone

​​​I need some help with Excel, please.

I have a financial spreadsheet, and enter data weekly. I am trying to work out how to keep a running total of the last 52 weeks (ie a year) but cannot fathom out how to do it .

Does anyone have any ideas please?

Thanks

Gehenna

[DIBO]

not perfect, but you could start with this:

col. A col. B

date1 amount1
date2 amount2
date3 amount3 =SUMIF(A:A,">"& (NOW()-365),B:B)

[Fareastdriver]

Column A=date; column B; +data; column C +amount.. Column D -data; column E - amount. F daily/weekly total.
Go to Line 1 of your totals and ensure that it is Zero. Assuming you have formatted the column for cash the use this formula in the F2 box.
=F1+C2-E2 enter.
This will bring the result into the F2 box. To continue with the column highlight the box and ease the curser to the bottom RH corner of the box. This will bring up a cross and you can drag the box down and where you stop it will give you the total for the column.

Subsequently you only have to highlight the last result and pull it down. Should you want to be really lazy pull the result a few lines below those that you have used and it will automatically totalise until you reach it

[gehenna]

Thanks very much for your replies.

Fareastdriver; I may have not quite got it right, but trying what you suggest will not remove data which is more than a year old, if I enter data weekly. Is there any way I can programme excel to only add the rolling last 52 entries?

Thanks again

[Jhieminga]

Have you tried the formula DIBO posted? Just put this in a cell somewhere and change the 'A:A' and 'B:B' references to reflect the column letters of the relevant ones in your spreadsheet containing dates and weekly amounts. It should always calculate a total based on the last 365 days.

Just a thought, but you can also do something like this (based on the same two column approach and assuming that you've got an entry for every week):
Put this formula in the third column at the end: =SUM(OFFSET(B53, -51,0):B53)
If the cell you're putting this in is say on the 65th row, change B53 to B65 (both instances). You can copy this formula down to the rows below and it should give you a total for the last 52 rows. If there are less than 52 rows in your sheet, it will give you an error message as the result. If you're getting an error message when entering the formula, check if you're using a system set up to use a comma as a decimal separator. If that is the case, you will need to use semicolons instead of commas in the formulas.

[gehenna]

Jhieminga

Thank you very much for your post.

I'll give that a try this evening!

Gehenna

[ExGrunt]

Hi,

If you have the information in the layout of DIBO's post - dates in column A, values in column B. You could use this array formula (not in either column A or B which must only have data):

=SUM((A:A>=(MAX(A:A)-365))*(B:B))

You create an array formula by pressing: CTRL-SHIFT-ENTER together. If you have done it correctly the formula will appear in {} in the formula bar - ie:
{=SUM((A:A>=(MAX(A:A)-365))*(B:B))}

What this is doing is taking the largest date value in column A and deducting 365 to get the date a year ago and then using matrix multiplication to select all of the values which have dates that are newer.

HTh

EG

[gehenna]

Just wanted to say a Big 'Thank You' to all of you who helped with my question above; problem solved!

Gehenna