Hotel occupancy in Excel: rooms occupied versus capacity
A hotel occupancy sheet is one row per date and room type. You type how many rooms of that type you can sell that night, and how many were occupied. The rate is occupied divided by capacity when capacity is not zero. Vacant rooms are capacity minus occupied. A flag says over when occupied is above capacity, full when the two numbers match, and open when rooms remain. This sheet is not a restaurant cover count, a guest folio, or a housekeeping task list. If the question is whether the night went above the rooms you said you had, the row answers that. A guest name can sit in a note. It is not what you count in the morning. The rate is a formula. You do not type a percent by hand and also type the room counts. If the two disagree, the meeting argues about the cell instead of the night. Format the rate as a percentage. Before you trust the rate, check capacity. Zero capacity returns a blank rate, not an error. Text in the capacity or date cell will not calculate like a number. Correct the cell, then read the rate again. You also type a floor in one cell, for example 0.70 if you want to see nights under 70 percent. That floor is yours. The sheet does not know what a good night is. COUNTIFS counts rows marked below that floor, and rows marked over. The trial lasts 7 days, with no card. Elyx can write the rate, the vacant count, and the flag on the file you already keep. You still type the rooms.
The Problem
The occupancy number fails when it lives in a message. Someone writes that Tuesday looked full. The next morning nobody can say full against which capacity, for which room type, or whether three rooms were out of order and should not have been in the capacity. A sentence without the two counts is not a rate. A second failure is a sheet that tries to be the folio, the breakfast count, and the staff rota. If you add a guest name column you then filter, a cover count, and a shift, the morning question splits. Keep this sheet to the date, the room type, the capacity you typed, and the occupied count you typed. Other facts can be a note. They are not the columns you divide. A third failure is deleting last month to make the file look short. Filter the date if you want this month only. Deleting history removes the check that yesterday's rate used the same formula.
Benefits
Each night and room type is one row, with capacity and occupied rooms you typed.
The rate is occupied divided by capacity, and it stays blank when capacity is zero.
Vacant rooms are capacity minus occupied, including a negative number when you are over.
A flag marks over, full, or open from those two counts, not from a word you retype.
COUNTIFS counts nights under a floor you typed, and nights marked over.
Step-by-Step Tutorial
Create the list and the header
Name the sheet Occupancy. In row 1 type the headers: Date, Room type, Capacity, Occupied, Rate, Vacant, Flag, Below, Note. Date is a real date. Room type is a short name you reuse. Capacity and Occupied are whole numbers. Rate, Vacant, Flag, and Below are formulas. Note is optional. Freeze row 1. Do not merge cells. Put the floor you care about in K1 as a decimal: 0.70 means 70 percent. Label it in J1.
If you turn the range into an Excel table, column names are easier to read. The formulas below also work on a plain range. A table will expand the formulas when you add a row.
Lock the room type names
Select the Room type column from row 2 downward. Use Data, Data Validation, List. The source is a small range on a sheet named Lists, with the type names you actually sell. People must pick a name. Double and double room are two types to COUNTIFS. Extra spaces count too. COUNTIFS is not case sensitive, but a trailing space does not match. Keep out-of-order rooms out of capacity rather than inventing a type called closed.
Put the list on its own sheet so a paste into Occupancy does not overwrite the allowed names.
Enter one night you can check by hand
On row 2, type a date you know, one room type, a capacity such as 50, and an occupied count such as 40. Leave Rate, Vacant, Flag, and Below empty until the formulas exist. If you do not yet have last night's numbers, use 50 and 40 anyway so you can predict 80 percent, 10 vacant, and the flag open before you trust the sheet. Do not paste a guest list into Note and then try to count it. One sentence is enough if you need to remember that four rooms were out of order and already removed from capacity.
Occupied should be the rooms you are counting as sold or stayed, using the same rule every day. Write that rule next to K1. Changing the rule mid month changes the rate without changing the formula.
Write the rate so zero capacity stays blank
In the Rate column, blank the cell when capacity is zero or blank. Otherwise divide occupied by capacity. With Capacity in C2 and Occupied in D2, the formula is =IF(OR(C2="",C2=0),"",D2/C2). Format the column as Percentage with no decimals, or with one decimal if you need it. If the cell shows 0.8, it is still right and the format is wrong. If the cell shows a date, the format is a date: change it to Percentage. If the cell shows an error, C2 is text or the formula was overwritten with a typed percent. Do not type the percent on top of the formula. The percent is the result.
=IF(OR(C2="",C2=0),"",D2/C2)A capacity of zero is a blank rate, not zero percent. Zero percent would mean you had rooms and sold none. No rooms available is a different fact.
Write vacant rooms and the flag
Vacant rooms are capacity minus occupied, including when occupied is higher. With the same columns, use =IF(C2="","",C2-D2). A result of 10 means ten rooms were empty. A result of -2 means two rooms above the capacity you typed. Format the column as a number. The flag reads those two counts and does not ask you to retype a word. Use =IF(C2="","",IF(D2>C2,"over",IF(D2=C2,"full","open"))). Over means occupied is strictly greater than capacity. Full means they match. Open means occupied is lower. A blank capacity leaves the flag blank so it does not look open.
=IF(C2="","",IF(D2>C2,"over",IF(D2=C2,"full","open")))Copy Vacant and Flag together down the column. If you copy only the flag, vacant stays empty and the morning sheet looks half finished.
Mark nights under the floor you typed
Below compares the rate to the single floor in K1. The formula is =IF(E2="","",IF(E2<$K$1,"below","ok")). The dollar signs keep K1 fixed when you fill the column. If you forget them, row 3 reads K2 and every rate looks below. A blank rate stays blank here too. Do not hardcode 0.70 in the formula. Change K1. Type 0.70 or 70 percent, not the bare number 70, or every real rate looks below it.
=IF(E2="","",IF(E2<$K$1,"below","ok"))The floor is a choice you typed. It is not a target the sheet invented. If you do not want this column yet, leave it out. The rate still stands.
Count over and below for the morning
Above the list, or on a small sheet named Morning, count the flags. Nights marked over are =COUNTIFS(G:G,"over"). Nights marked below for the room type named in M2 are =COUNTIFS(B:B,M2,H:H,"below"). Zero-capacity rows stay out because their Below cell is blank. Start the ranges at row 2 if you want to be sure the header never matches. Write the rule next to the count.
=COUNTIFS(B:B,M2,H:H,"below")Filter is for reading rows. COUNTIFS is for the number. Do not count visible rows by hand after a filter and then type that number over the formula.
Check three rows before you share the rate
Pick three rows yourself. For 40 occupied and 50 capacity, the rate is 80 percent, vacant is 10, and the flag is open. If your floor is 0.70, Below is ok. For 50 and 50, the flag is full and vacant is 0. For 52 and 50, the rate is 104 percent, vacant is -2, and the flag is over. Capacity 0 must leave Rate, Flag, and Below blank. If any check fails, do not send the COUNTIFS total. Fix the format, the floor cell, or a text capacity, then read the rate again.
Write the three checks in a note on the Morning sheet if more than one person updates the night.
Template Features
Rate that refuses a zero capacity
Occupied divided by capacity, blank when capacity is zero or empty. You do not type the percent.
=IF(OR(C2="",C2=0),"",D2/C2)Vacant rooms, including a negative
Capacity minus occupied. A negative number is the count of rooms above the capacity you typed.
=IF(C2="","",C2-D2)Flag from the two counts
Over, full, or open. The word comes from the comparison, so it cannot drift away from the rate.
=IF(C2="","",IF(D2>C2,"over",IF(D2=C2,"full","open")))Below a floor you control
One cell holds the floor. The column marks below or ok. Change the cell, not every formula.
=IF(E2="","",IF(E2<$K$1,"below","ok"))Morning counts
COUNTIFS reads the flag and the room type. The number on the Morning sheet is that count.
=COUNTIFS(G:G,"over")Concrete Examples
A night you can divide by hand
You open yesterday. One row is the doubles. You want the rate before you trust the column.
Capacity: 50. Occupied: 40. Floor in K1: 0.70.
Result: Rate is 80 percent. Vacant is 10. Flag is open. Below is ok, because 0.80 is not under 0.70. If the rate shows 0.8, change the format to Percentage. The 80 percent is a result, not a target.
A full night and a night over capacity
Two rows, same room type, two dates. You want full and over to mean different things, and you do not want to type the words yourself.
Row A: capacity 50, occupied 50. Row B: capacity 50, occupied 52.
Result: Row A: rate 100 percent, vacant 0, flag full. Row B: rate 104 percent, vacant -2, flag over. COUNTIFS on the word over includes row B and not row A. A full night is not an over night.
Zero capacity is not zero percent
That type is closed for the night. You type capacity 0 because there is nothing to sell. You do not want the rate to read 0 percent, which would look like an empty hotel.
Capacity: 0. Occupied: 0. Floor: 0.70.
Result: Rate is blank. Flag is blank. Below is blank. The over count ignores the row. The below count ignores the row. Vacant is 0 if both numbers are zero. If you had typed occupied 2 against capacity 0, vacant would be -2 and the flag would be over, which is a signal to fix the capacity before the meeting.
Pro Tips
Keep one row per date and type
Two rows for the same date and the same room type will both count. Add the occupied rooms on one row. Do not split a type because two people typed it. If you need a building, add a Building column and include it in COUNTIFS. Do not bury the building in the room type name.
Out of order rooms change capacity, not the rate formula
If four rooms cannot be sold, reduce capacity by four and say so in Note. Do not lower occupied to fake a rate. Occupied is what you counted as stayed or sold. Capacity is what you could sell. Mixing them makes the next person unable to audit the night.
Percent format versus a typed 80
The formula returns a fraction. Percentage format shows it as a percent. If someone types 80 into the rate cell, they replaced the formula. Undo, and put the formula back.
Copy the sheet at month end if you must archive
If the list gets long, copy the sheet and name the copy with the month. Leave the formulas as they are. Point the Morning counts at the current sheet only.