Rolling Totals

[rbuckwalter]

This is prbably a fairly simple question, but I'm setting up an attendance spreadsheet for work and want to always be able to keep a rolling 90 day total of hours missed from whatever todays date is.

What would be the simplest way to accomplish this? Data will be in clumn format with date in comun a and hours missed for that dat in column B (or subsequent columns after B for other Staff)

Example: Date in column A and hours missed for that date in Column B. I'm only interested in the hours missed in the last 90 days from whatever todays date is though no matter how far back I have attendance data. Anything over 90 days would drop off the total.

Any help would be appreciated.

Thanks!!
[/img]

 

[acw]

Hi

You could build a dynamic range and use the sum formula. If you have today's data in the last row of column A (say row 100), then you want to sum the range B11:b100 (or whatever your last column of data happens to be).

You can use the match function to get the row of today's date and use the offset command to provide the sum range.

Along the lines of:
=sum(offset(b1,match(today(),a1:a500,0)-90)ffset(b1,match(today(),a1:a500,0),0))

(note above not tested)
Tony

 

[acw]

Hi

Revision to the formula

=SUM(OFFSET(B1,MATCH(TODAY(),A1:A500,0)-90,0):OFFSET(B1,MATCH(TODAY(),A1:A500,0)-1,0))

Tony

 

[JMaysun]

I tried using this formula, but I keep getting reference errors. Either #NA or #REF!. Can anyone tell me what I am doing wrong, or specifically which cells this formula is supposed to reference. Thanks a bunch!