• 100% Satisfaction Guarantee
Jess M., Computer Support Specialist
Category: Microsoft
Satisfied Customers: 8828
Experience:  Computer Software Specialist for more than 10 years
49766785
Jess M. is online now

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

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.

Customer: replied 1 year ago.
Please find attached Dummy file cleaned up - took out my many efforts to deal with missing weeks etc

Thank you. Please give me some time to check the problem and provide a working formula.

Customer: replied 1 year ago.
Sorry as Accountants PI will not allow us to securely connect. No problem with time

Thanks. No problem with remote access, you already sent a dummy file so I have something to work on.

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?

Customer: replied 1 year ago.
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
Customer: replied 1 year ago.
i have not accepted Remote Assistance

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?

Customer: replied 1 year ago.
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?

Customer: replied 1 year ago.
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?

Customer: replied 1 year ago.
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?

Customer: replied 1 year ago.
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:

1. Is it possible to reformat your data to show date as column headers?
2. 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.

http://filesxpress.com/d-c0e6e5d1

Customer: replied 1 year ago.
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 1 year ago.
Are you based in UK? Could phone if it would help?

Thank you for that information. I am not from the UK so I cannot not call. Please give me moment to work on this.

Customer: replied 1 year ago.
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

Hi 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.

Customer: replied 1 year ago.
For the time being I can accept your first proposal although not ideal. Please provide details as soon as possible. Many thanks.Terry

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.

Customer: replied 1 year ago.
Ok Thats great.
Customer: replied 1 year ago.
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

Terry,

The sample sheet that you gave me, it is for ONE worker, right?

Customer: replied 1 year ago.
the average weekly pay per company will have 30 workers T
Customer: replied 1 year ago.
Jess if you are struggling and want to call it a day, that's fine. T

So are you saying you have 1 file with 30-worker sheets?

Customer: replied 1 year ago.
each sheet would be for one worker Variable hours each worker.

For a single file with 30+ sheets, having these helper sheets will make the file 60+ sheets total..

Customer: replied 1 year ago.
there is an easier way - was almost there. Forget it No prob with pennies to date. Thanks for trying. T

OK. I will try to build a macro for this as this has challenged me... I will let you know how it goes.

Customer: replied 1 year ago.
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

OK I will check on that