How do i average two coloumns for 6 months rolling

[Murphy1985]

Hi,
I need some help with regards a spread sheet,i need to know how to calculate a six month rolling average for hours in AD column and AE column, this average would be entered in to AF1.
the date column is AC the date is not consecutive it does miss days out.

Hope someone can help and if i need to give anymore information just ask.

Chris[/CENTER]

[martesoft]

You didn't post a spreadsheet, but is AE subtracted from AD or what?

[Murphy1985]

You didn't post a spreadsheet, but is AE subtracted from AD or what?

Apologies it didnt seem to want to let me post the spread sheet, AE and AD are hours worked on overtime, so they both need to be added on the six month rolling total.

[royUK]

You should be able to attach a workbook unless it is too big.

[Murphy1985]

sorry about my lack of observation skills and not attaching a copy of file, I have now, its the D6 HRS and the column next to it "hours" that need to be a 6 month rolling average, as basically we can not go over a certain combined overtime average for 6 months.

[martesoft]

I have absoluetly no idea what is supposed to be added to/averaged over what in that spreasdheet. You have numbers all over the place.

[Murphy1985]

Each person has their own over time section with 4 columns "date, D6 hours, Overtime hours, Description", so for each person i.e James Bessant the first one, in Row 2 just above his name, i need a 6month rolling average total, so from when ever the spread sheet is opened and than 6 months previous to that I need the total of their D6 hours and Overtime Hours.

hope this explans it a bit better, sorry.

[martesoft]

Not really, it still doesn't tell me where those overtime hours are, are they in column E, F, both, neither?

Does 6 months mean say from 7th APril or 1st APril or 1st May?

Here is a starter to get the dates that are greater than 1st APril today, but I am still unclear as to what else is to be included

SUMPRODUCT(--(D$6:D$150>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)))

[Murphy1985]

Not really, it still doesn't tell me where those overtime hours are, are they in column E, F, both, neither?

Does 6 months mean say from 7th APril or 1st APril or 1st May?

Here is a starter to get the dates that are greater than 1st APril today, but I am still unclear as to what else is to be included

SUMPRODUCT(--(D$6:D$150>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)))

Both E and F columns are to be included in the 6 month total, and the 6 months refers to 6 months back from now so it is the 7/10/2008 so i want to go from 7/04/2008 and then tomorrow its would be the 8/04/2008 and so on.

[martesoft]

Okay so far so good, but what is 6 months back from say 31st August?

[Murphy1985]

aaaa i see what you meant now, the 28th of Feb would do if that could be done.

[martesoft]

I hope that you meant 29th Feb (for this year) :-)

Here is my latest attempt

=E$5+SUMPRODUCT(--(D$6:D$150>=MIN(DATE(YEAR(TODAY()),MONTH(TODAY())-{6,5},DAY(TODAY())*{1,0}))),E$6:E$150+F$6:F$150)

[Murphy1985]

yea, of course :D, that came back with a total of 99, the six month total for James is actually 77 so far.

[martesoft]

Why is is 77 Chris, tell me what line the numbers to add start at? I also added the 32 D6 hours, is that right?

[Murphy1985]

77 comes from today(7/10/2008) back to the (7/04/2008) so that basically includes dates (5/10/2008) to (5/10/2008) and that total for both columns is 77. so thats D14 to D38.

[martesoft]

Okay, so I added the 32 incorrec tly, but 6 months prior to today does NOT include 5th April nor the 6th April, they are just prior to 6 months ago. That gives D16 throughD26, a sum of 67, which works if you strip off the E$5+ from my formula.

[Murphy1985]

Fantastic works, Excel genius you