Averages problem

**shorie7** · 09-09-2012, 06:57 AM

I was wondering if anyone could help me. Its a tricky one, so bear with me...
I am a lorry driver, and I am not allowed to work over 48 hours on average over a 17 week period. What I am trying to do, is be able to add this weeks working hours into excel, so it will count up this weeks hours, and my last 16 weeks hours, and give me my average hours worked over 17 weeks. Without having to re-type all the previous weeks hours in. I have searched everywhere, but with no joy. Any ideas? Thank you

**daddylonglegs** · 09-09-2012, 07:22 AM

If you have weeks off during the period are they still included?

If so then assuming dates in A2:A100 and hours worked in B2:B100 try this for the last 17 week average

=SUMIF(A2:A100,">"&TODAY()-17*7,A2:A100)/17

**shorie7** · 09-09-2012, 07:53 AM

Yes weeks off are included. But where would i enter the new weeks work and in what column/row do i put the above formula? Im at a loss here totally on what goes where

**daddylonglegs** · 09-09-2012, 08:27 AM

How is your data set up now, do you have a column with dates (one for each week) and another with the hours worked for that week, or is it something different?

**shorie7** · 09-09-2012, 08:47 AM

I have set nothing yet, for i dont know where to start. What i want is say at C1, enter my current weeks work here. dates could be stored in A3 to A20, with hours worked stored each week in B3 to B20. When i enter my NEW weeks work at C1, it needs to put this at B3, and delete the bottom row,(at B20), and then give me a new 17 week average calculation. This continues then so on every week. Its like dropping one week down every time i enter a new weeks work. I need it cos over a 17 week average, I cannot go over a 48 hour week. I can work upto 56 hours a week, but not do more than 48 hours average over a 17 week period. Confusing i know. The mind boggles.

**daddylonglegs** · 09-09-2012, 09:19 AM

OK, what I'm suggesting is that you enter all historical data in columns A and B (even if that goes back more than 17 weeks) with the latest date at the top. Then for each new week you can insert a row at row 3 and enter the latest week's date and hours.

Then you use my formula to get the average for the last 17 weeks only. There was an error in my initial suggestion

, try this version in C1

=SUMIF(A:A,">"&MAX(A:A)-17*7,B:B)/17

That will give you the sum of the hours for the last 17 weeks and divide by 17 to give the average. Because it uses the latest date to calculate it will automatically update the average every time you insert a new entry - adding the new week to the average but discarding the week that was 17th but is now 18th.

I'm assuming that you enter data once a week on the same day.

Do you have at least 17 weeks historical data to start with or do you want to calculate the average for whatever you have if there are less than 17 weeks? For the latter try changing to this formula

=SUMIF(A:A,">"&MAX(A:A)-17*7,B:B)/COUNTIF(A:A,">"&MAX(A:A)-17*7)