Automated recurring calculation. No-one at my job can help me with this.

Mr Sandman

New Member
May 23, 2017
Hello everyone,

I would first like to thank you all for the help that I found on this forum, reading various threads as I was discovering Excel, working (and being stuck) on my project.

Before we get to the good stuff, let me introduce myself and tell you why I am writing this post.

My name is Antoine, I work as a receptionist in a hotel in Belgium, and I am currently enrolled in a yearly program (along with 9 other people nationally) from my company, aimed at making us progress in the corporate ladder quicker than usual.

The program lasts 1 year and is comprised of several challenges, meet-ups etc.

For the first challenge, we had to find a "BHAG" (Big Hairy Audacious Goal.. I didn't choose the name but you can see how fond of acronyms we also are in Belgium) which would benefit us, the employee, but also the company. It had to be challenging etc, getting out of our "comfort zone".

So I choose to work on a problem that I thought needed fixing, I knew it would be technically challenging (knowing nothing about Excel before starting it) but that was the point.

Here it is:

Each day, the morning shift receptionist has to assign rooms to clean to specific workers, typically 28rooms for an 8hours shift, 20rooms for a 6 hours shift.

The Rooms are either:

"DA" = departure = the sheets have to be changed, new towels etc. the room thoroughly cleaned for the next guest = more work generally than staying.


"OS" = Staying = only the floor and the bathroom must be cleaned, as well as giving new towels = less work generally than departure.

In the old way, we simply had to assign for instance "Room N°1 to Room N°20 = Worker N°1" - "Room N°20 to Room N°40 = Worker N°2" etc.

Which meant that if there was a group of guests staying for several nights, the worker n°1 could have 18 OS and 2 DA to clean, whereas her coworker would have to do 16 DA and 2 OS.

By now you can see the problem. :eek: This system is highly inefficient, with an unequal distribution of the actual work on a day to day basis. All of that creates tensions every single day when the workers arrive and compare their sheet to the others.

My idea was to automate the assignment in a way that is fair for everyone, by calculating the daily “Index” = Amount of work required to do on a specific day, depending on how many rooms must be cleaned, and which status they have (either DA or OS).

Here is a link to the file :

So far - thanks to many tutorials - I have managed to get a decent result, and the basic principle works.
But in order to make it a viable alternative to human input, I need your help to figure out how to express my last needs in formulas, and to know if it is possible in the first place.

On the first sheet, you can see the values corresponding to each room status.
You can see that the easiest rooms to clean are the Double OS of the second floor, with a score of “10” – compared to the smoking triple DA which corresponds to a score of “20”
(Yes, we still have smoking rooms and yes, it attracts the type of customers that you don’t necessarily want in your hotel, leaving the rooms very very dirty.
Only these values would change if I were to implement my program in more than the pilot hotel.

Next you have the “Groundfloor” Sheet, the way it works is you write “DA” or “OS” in the Status column C7:C37 to get the value for each. FIG1 —

Same thing for Floor 1 and Floor 2

You then get the Index Sum on cells H7:H37

Once it’s done, you input the number of hours each worker are here for today - 8 or 6 depending on their contract - in the cells O30:O46. FIG3 —

You can see that 110 rooms must be cleaned today in my configuration, and since only 4 workers are here today, the remaining rooms should be put “HS” = Blocked, so that they can be cleaned the next day, or whenever there will be enough cleaning staff + the need to have all rooms cleaned because the hotel will be fully booked.

The “Housekeeping Team” part of the sheet then indicates which score each worker much reach, calculated by the daily index divided by the number of working hours for that day, multiplied by 6 or 8 depending on the contract.

The “Per hour” score (W14) should rest at a maximum of 55 (to be determined), above which each worker would have to clean more rooms than her contract allows, and we need to prevent that.

Check the formulas in the Column I, this is where it all happens.

It assigns a number by checking if the index sum corresponds to the target score for each worker, giving 0 if the room is already clean. FIG4 —

Question 1 :

All this is fine and works as intended, the next step (that is indispensable to be truly viable) is to pass this calculation through stages, where each stage, the sheet checks if one name appears in several sheets, which means that the worker must do 2 floors, and I want the sheet to re-do the allocation after subtracting one room to the worker’s score, to account for the extra work that is preparing a second trolley (which equals one room worth or so).

Since there is no way to know if a worker is going to do 2 floors before first assigning, the sheet would have to re-calculate after each worker to check all the logical criteria, and do the subtraction if need be, then re-allocate, re-check, etc.

I have no idea how to express this on Excel and couldn’t find anything to help me on the internet. This is where (hopefully) you guys and girls come in. :confused:

Question 2 :

The second thing I need the sheet to do (and that is probably as complex as the first) is to Block “HS” the rooms that exceed the maximum index given by number of workers present.

For instance, if the hotel was fully booked the day before, and there had to be 123 rooms cleaned, but only a maximum of 116 rooms could be cleaned given who works that day, the remaining 7 rooms would have to be put out of order “HS”. FIG6 —


Only Departure rooms “DA” can be put out or order, since staying customers “OS” must get new towels and have their room refreshed.


Ideally, it would block rooms in such a way that would prevent worker to do 2 floors, for instance it would block rooms 31 and 32 so that Malika only works on the First Floor.

Again, I have no idea if it is even possible to write such conditions in excel, my whole post is basically to get answers to these two questions.

Thanks for those who spent time to read this tedious explanation, if you have any questions regarding some of the formulas, I’ll be happy to answer them!

(P.R.A.S. stands for Procedural Room Allocation Sheet, by the way :wink:)

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back