Need to work out occupancy rates

how do you calculate occupancy rate ?

is that the total number of days * the number of rooms = total possible

douglas road for February would be (this year is a leapyear) - so is that 29 days * 13 rooms
OR - what - confused - sorry

douglas road has 3 rows - all with 12/2/24
so what would be the occupancy rate in those rows ?? how many days are they occupied

I just need to work out the occupancy rate and be able to filter the location relating to the information and to update automatically when I update the data

still not following - can you give some expected results .. and WHY

FILTER on location - whats the location is that HOME

are you after the number of people in a room
so is that 13 people cn be accommodated in douglas road
and then you say 3 / 13 = 23%

I thought it was 13 rooms in douglas road and you wanted how many rooms where occupied in a month - as i mentioned in my first reply
so 29 days in Feb - 13 rooms - that means 13* 29 is the max number of rooms available in feb
then how many rooms where filled in the month

you have 3 entries in the data - because each row is recorded for each person , so in the real data - you have 11 rows for friday 17/11/23

11/11 = 100%
BUT
what if you have 3 people in on 1/2/24 and then 10 people in on the 2/2/24 and then 4 people in on the 3/2/24

if you filter on douglas road
that will be 17 rows of entries
3 for the 3 people
10 for the 10 people
4 for the 4 people

whats thats as a % then
3/13
10/13
4/13
OR
becuase all in Feburary
17/13

still not following - hopefully Pete_UK may understand

I know what I am trying to do, it is just explaining it to someone in the written form that is the hard part

I am after the % of the rooms that are being filled.
So if there are 10 rooms taken at Woodland View for example, that would be 91% of the rooms taken
The formula for the occupancy I have worked out it is Rooms Occupied/Rooms Available. It will bring up a 0.0 no which then I converted into a percentage.
The main part is to incorporate that formula whilst filtering out the homes to match the data.
So I need a formula to work out the room occupancy percentage and to filter out different homes.

The "filtering", as you put it, is not difficult at all - you can use this formula in H2:

=SUMIFS($B$4:$B$27,$E$4:$E$27,B$1)

to count all the numbers in column B which relate to Woodland View, and copy it across for the other locations. However, the formula does not give a breakdown by month, nor does it take account of the duplicates that you have in your data - it will give a result of 108 in H2, which is clearly ludicrous.

You need to take account of the actual occupancy for each day within that month, and then compare that with the number of days in each month times the number of available rooms per day. Your data in the pink area does not record the rooms occupied on a daily basis (perhaps your larger list would do so), and you have duplicated data that I don't fully understand, so it is not really possible to give you the occupancy rate from the data you have supplied to us.

Hope this helps.

Pete

just off the top of my head - you could create a NEW range using unique() and even a sort - otherwise its VBA for removing duplicates automatically

=SORT(UNIQUE(B4:F27),4)

But it would be useful to see the new spreadsheet with the calculations and how you are using

where is the new spreadsheet - still has the old one on your first post

So in B2, the formula is =IF(E4="Douglas Road", COUNTIF(D:D, D4), IF(E4="Silver Birch", COUNTIF(D:D, D4), IF(E4="Woodland View", COUNTIF(D:D, D4), 0)))
In C2 it is =RIGHT(D4,LEN(D4)-FIND(" ",D4))
In D2 it is =CHOOSE(WEEKDAY(H4), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") & " " & TEXT(H4, "dd/mm/yyyy") (H4 is part of my master file)
in E2 it is =IF(M4="Douglas Road", "Douglas Road", IF(M4="Silver Birch", "Silver Birch", IF(M4="Woodland View", "Woodland View", "Other"))) (AGAIN E2 is in my Master File)
In F2 it is =IF(H4<>"", TEXT(H4, "mmmm"), "") (H4 is in part ofmy master file)

dont see any of those formulas

and still I'm not following -Sorry

is that now 3 rooms occupied OR as you said before 3 people

if now back to rooms
then if you put a date you are interested , and real dates in the column F - then you could
use the date to filter the months - and also the HOME to the HOME you want to measure

Based on the month - that can multiple the days in month - by the number of rooms for that HOME
AND sum all the ROOM Occupied by HOME and the month

NOT perfect here , as the month is text
but something like
=SUM(FILTER(B4:B19,(E4:E19=E2)*(F4:F19=F2)))/(B2*29)

as i say real dates would be better - i will work on later , if time

OK<
so I have changed to real dates
Now you put in the HOME you want to measure - in E2 (yellow)
and the date you are interested in - so the 1st of a month and year - 1/2/23
then it will calculate the occupancy
=SUM(FILTER(B4:B19,(E4:E19=E2)*(F4:F19=F2)))/(INDEX(B2:D2,MATCH(E2,B1:D1,0))*DAY(EOMONTH(F2,0)))
based on
How many entries for that HOME for that MONTH
SUM(FILTER(B4:B19,(E4:E19=E2)*(F4:F19=F2)))

Then divide that by the number of rooms available and days in that month
/(INDEX(B2:D2,MATCH(E2,B1:D1,0))*DAY(EOMONTH(F2,0)))

column I , J , K etc - just show those working as you change the criteria

BUT its not 3% as you mention earlier

Douglas is 0.8%
NOTE the spelling was wrong in C1 or wrong in E

not following again

however a bit complex to what I am currently trying to achieve

so post some expected results - STILL NOT following
Column A, Need the occupancy rate as a %
So how is that actually calculate in column A - its just 1 row

Rooms Occupied / Rooms available.

so is that simply

you have a space at the end of woodland view - i removed

=B4/INDEX($B$2:$D$2,MATCH(E4,$B$1:$D$1,0))

works out the percent (not sure it means much) NOT occupancy as i understand it

otherwise

for row 4 to 19 - put what the % you think it is and why

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I am providing the link because you are a new member. However, the first thing you should do before posting as a new member here or anywhere is to read the rules.

https://www.mrexcel.com/board/thread...ilter.1254338/

It is now all sorted.

Many Thanks for your help with this and your patience. It is very much appreciated

It is now all sorted.

What was sorted / solution for others looking and for me to understand as well please