Excel Formula

Can anyone suggest a good site to get help with excel probs that have you baffled?
Cheers all

 

If somebody here can't help try this site: Mr. Excel

 

17 Week Rolling Average

Thanks John B
Site looks interesting, but unfortunately can't afford a consultant!
I need a simple way to calculate an employees weekly hours.
E.g. Input an employees hours for a week and then get a warning if their average hours over the last 17 weeks (rolling) including the current week is over 48 hours!
This is due to UK employment law!
Have worked out a way to do this myself but the others in my office would not be able to use it, without it going pear-shaped! So I need a way that is simple for people that are only inputting the weekly hours.
Keep in mind that employees could start at at anytime during or before the last seventeen weeks.
This is a challenge to all excel experts!
I am not holding my breath!
In my experience this would cost me about £1000.00 per day until it is solved
Thanks
Lee

 

Assume the following:

A1 contains the header 'Week number' (or whatever)
A2 to An number the weeks (1, 2, etc. or by date, wwhatever)

B1 contains the header Hours
B2 to Bn contain the hours for week1, week 2, etc,

So the hours worked in the weeks 1 to 17 are in cells B2 to B18

In C18 type =IF(AVERAGE(B2:B18)>48, "Average hours exceed 48 "," ")
Click on C18, place cursor on bottom right of the cell and drag the formula down as far as you want to go. In each subsequent row, the average is calculated over the previous 17 weeks, including the current week (i.e. in C19 the range will be B3 to B19, etc.).


Of course, you can change the text in the formula in the first " " to whatever you want. It will appear in column C if the average hours over the previous 17 weeks (incliding current week) exceeded 48. You can also, if you wish, add suitable text in the second " ". This would appear if the average over the previous 17 weeks was less than or equal to 48.

Also, of course, you can place the formula in any column, it does not have to be C. It would also be possible to develop a formula that would be in a single cell and update itself each week.

 

Thanks
This is similar to what I did but may be a little easier
Thanks
for your time