**Birthday Rooms occupancy.**

I am running birthday rooms business. I have a database with reservations. It consists of such values as Birthday Date, Birthday Hour Slot (Whole number in 24-hour format from List from selected values; Ex 10, 13, 16, 19), Type of Birthday Room (List from selected values; Ex. Small, Big). Every reservation is listed in the new row and given a unique ID.

I also know the maximum availability of rooms and time slots for every day, so derivative values as such are calculated (they are explained in detail in the given Coda document)

What I want to do is to calculate and chart the average occupancy rate (in %):

- for every day
- for every birthday hour slot
- for every week/month
- for a given period

To do so I have to divide the real occupancy by maximum occupancy (max number of slots per day). That is not a problem to do with the days when there is occupancy. The calculations have been done in the Coda document attached. BUT there are some days and some hour slots on some days that are completely empty thus NOT reflected in the raw data. Therefore I miss calculating those days/hour slots and get averages that reflect only the days and hour slots that I have had birthday celebrations thus they show better occupancy than it reality it exists.

So my question is this: how can I â€śaccount forâ€ť the missing days, missing hour slots in such a case. Filling by hand it very difficult (I have over 1000 rows) so I am looking for an automated solution. The file is here:

If you could be so kind and share your ideas, I would truly appreciate that.