Need to work out occupancy rate and to filter

[Jake05]

Hello
I hope someone can help me, I am very new to excel and have been thrown in the deep end

In Column A, I need to work out the occupancy rates for rooms. The total rooms available at any 1 time are in Cells B2, C2 & D2
The current rooms occupied on a date are in column B
However I need to filter from 3 different homes that are in column E so it matches the homes

I need to now calculate the room occupancy rate as a %

I have a formula so in this case it would be = Rooms Occupied / Rooms available. However as I have 3 sperate homes with differing availabilites, I need to filter the homes as well as sort out the occupancy rates.
Is there a formula I can use that would help me with this?

So in this example, the total rooms available per home are in columns B2, C2 and D2
Column B is the rooms occupied on that date
To get the room occupancy it would be =B4/$B$2. This would give me the occupancy as a decimal which would need to be converted into a %.

But as I have multiple homes in the table with varying total rooms available, I need to create a formula that shows the correct occupancy rate % per home whilst filtering the homes so the records match

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need to work out occupancy rates
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[awoohaw]

I do not follow your prose description. Can you post an xl2bb mini worksheet (add in link below). Or at least a table of your data.
You refer to column B twice (B2 and "Column B"), with B2 containing a number (I guess it is the rooms available in a location, but unsure of which), and column containing dates occupied, but you don't describe what location and the number of rooms occupied.
please clarify.