Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M., Computer Support Specialist

Category: Microsoft

Satisfied Customers: 7482

Experience: Computer Software Specialist for more than 10 years

49766785

Type Your Microsoft Question Here...

Jess M. is online now

Sorry started yesterday - this is the question. I use a

This answer was rated:

★★★★★

Sorry started yesterday - this is the question. I use a spread sheet to calculate average hours for workers over a twelve week peiod - straight forward BUT if a person does not work in a week the twelve week peiod is extended by the number of non working weeks - they are completely ignored. I was able to use Average and Offset - it sort of worked but then I got stuck. If an employee has just started work the average is calculated over the number of weeks worked maybe just one or two. That s it - I will not send sheet basically three columns Date Hours worked in week and rolling 12 week average. That simple but I am stuck

Can you send a dummy file with sample data so that I can check the actual formulas you are using? This is also to determine the structure of your data so that I can provide the needed formula to fit your needs. Just remove the names of the people on the sheet and other sensitive info.

Let me clarify things first. The average covers 12 weeks. If on that 12 weeks, the person has started working, for instance, he started working on the 7th week, the average SHOULD compute from 7th to 12th week, not from 1st to 12th week. Is that correct?

Jess M. and other Microsoft Specialists are ready to help you

Sorry I tried to reply yesterday but your server rejected my response. Chaos since then. To answer your question.If the employee has only worked 6 weeks the average is calculated on the number of weeks worked but 'capped' at 12 if that is the word. I have been charge a extra £22 not sure why not vital now. We as a company have used you on many occasions - mainly legal I would like to join fuyll time but with general email - more worried about this formula. Thanks for your help Terry

Thank you for writing back with that information. In that case, it will be the average for 6 weeks only. For example:

Wk1 = 48; Wk2 = 48; Wk3=48; Wk4=48; Wk5=48; Wk6 = 48; Wk7 through Wk12 = 0; the total for 6 weeks is 288 hours. What is your desired average? 288/6weeks? or 288/12 weeks?

thank you average for 6 weeks 288/6 but if week 4 not worked you can only assume average for 5 because you can't go back before they started. Don't blame me blame HMRC :)

I don't understand the last part of your reply. Are you saying that if he worked for weeks 1, 2, 3, no week 4, then 5 and 6, the average should be for 5 weeks only. Is that correct?

Sorry Jess,that is correct and the ongoing problem. If somebody does not work a week the missing week is ignored in the calculation BUT the last 13 (ie 12 +1) weeks totals are divided by twelve. if two weeks are not worked the last 14 weeks totals are divided by twelve etc. Sorry

Ah ok, I understand. Based on your sample data, how will Excel know that someone did not work for a particular week? Which column to look up? IS it column D (days worked in week) with value of 0?

0 or more likely no data since- Could insist 0 if that would help. Several clerks will complete the spread sheets. It is because people work irregular hours.

Therefore, the actual task of the formula is to locate blank rows or those with 0 under hours worked, then continue to lookup until 12 rows (representing 12 weeks are found, and then do the average for those 12 weeks with non-zero values. Is that correct?

thanks for your efforts . Problem from my point of view was new starters as we have discussed. Happy to pay more for your efforts. Always appreciated. T

Thank you. This is quite complex scenario. Few more questions before I dig in to working on this:

Is it possible to reformat your data to show date as column headers?

I believe in your sample data, you are working on column H, what are your expected values when done or calculated manually? Should Excel only show the average EVERY 12 non-zero weeks? What about the weeks in between? Please check this screen shot.

1 Could show date as column headers - only problem out of kilter with everything else we do. Depending date when holiday is taken that is the hours perday that is used to calculate the holiday pay due ie the whole table is a rolling average. eg if i week 11 the guy has accrued 7 day holiday he will be paid 7 days x 7.5 hours x payrate. as per your screen shot. Normally we can calculate manually but some big employers have numerous holidays taken at this time of the year. The average is always a rolling average of the preceeding twelve weeks counting back if necessary 13,14 weeks etc weeks to exclude zero weeks. You understand the problem with new starters. Terry

Customer:replied 8 months ago.

Are you based in UK? Could phone if it would help?

Good Morrning, Jesse, Not chasing too hard but I will have to manually prepare soem sheets this week. How is it going? Is there light at the end fo the tunnel? Terry

Thank you for writing back. As I reviewed your requirement, it is really a rolling average and that can be done by Excel. However, if you want to EXTEND the range for particular average computation due zero work on particular weeks, that cannot be done using the rolling average formula.

However, there are workarounds, so please tell me which is feasible or useful to you so that I can start working on it.

First, we can create a "helper sheet" that shows a copy of the data of your original sheet but with ALL the rows with blank or zero hour removed. In that way, we can use the regular rolling average formula.

Second, we will use the regular rolling average formula in a column, and next to that, we will add a helper column that will calculate the rolling average for non-zero rows. That is, the result shown be will the average for 12 weeks, and if the range is below 12, the result will be blank.

Ok. So I am basing on the sample data you gave me. A helper sheet will be created and all data will be copied there, but all rows with blank or 0 under column E (Ave hours in day) will be removed. Please give me a moment for this.

Jess, I have taken further advice It would seem that it is possible to create the appropriate formulae to include the various 'what ifs, without too many problems. Also will we be able to create the helper sheet automatically each week for each worker - otherwise no use? Can I have a reply ASAP T

Don't worry too much - it is based around bad law https://www.gov.uk/calculate-your-holiday-entitlement/ Casual or irregular hours. This web page is worked on 28 nominal days our clients give 30 days.We have to inform workers each week total accrued hours - have many,many workers. if you have a brainwave let me know but don't panic T