Calculating the occupancy rate [SOLVED]

[MalR]

Hi guys. I have an accommodation business. The attached file shows part of our excel booking system. The numbers indicate cabin numbers and where a cell is hashed or coloured it means that that cabin has been booked for that day. Empty or white cells mean that the cabin is vacant.
I routinely calculate the occupancy rate for each day by deducting the booked or coloured cells from the total of 14 cabins. I do this manually because it cannot be automated due to the fact that the cells could be coloured in any colours. It is not possible to make them all the one colour.
I am wondering if a formula could calculate the white cells rather than the coloured cells? In that way I could automate my calculation for occupancy. Please bear in mind that each cell will have a cabin number in text in it.
Thanks for your help
MalR

 

[p45cal]

You could have a udf and use it thus on the sheet:
=Occupancy(J5:J16)
backed up by a function in code:

Function Occupancy(myRange As Range)
For Each cll In myRange.Cells
  If cll.Interior.ColorIndex = xlNone Then zz = zz + 1
Next cll
Occupancy = 1 - zz / myRange.Cells.Count
End Function

as in the attached.

 

[jeffreyweir]

@MalR Out of curiosity, what do the different colors represent? Is there any chance you might also want to report on them? If so, it's simple to amend the function that @p45cal has provided to accommodate this, if you'll pardon the pun.

 

[MalR]

Thanks Jeff. The colours have to be different so we can see physically the start and the end of each booking. If they were the same colour I would have to differentiate the start/end with perhaps a vertical line but it means bringing in another set of keystrokes. (Not impossible if it can be simplified). Often there are date changes before the customer arrives. In reality this was the easiest way to originally do it and it has stuck since then.
If there is an easier way I am open to suggestion.

 

[NARAYANK991]

Hi ,

Since you are doing the cell coloring manually , is it not time-consuming ?

If you can use a colored border for the occupied cells , it would be easier and could be automated , using either VBA or CF.

Narayan

 

[MalR]

Hi Narayan. Good to hear from you again and thanks for another contribution.
I have no problem changing to make it easier to do the calculation but to draw borders and colour them involves quite a few stages. They also have to be bold. I am not sure and have not checked but is there a key stroke sequence that can make a group of highlighted cells be given a bold border and then to colour the border? I would need at least three different coloured border options. I prefer to do it this way than to introduce another macro.
Coloured cells do make it easy to view on the worksheet and can be adjusted easily. In the end, I upload this worksheet to our website where customers understand that coloured cells mean the cabins are booked. I think a border would cause confusion. You can see this on www.oceanviewtouristpark.net.au/Availability. It makes sense that there should be a distinct advantage to start doing borders.
Happy to hear your response
Regards
MalR

 

[NARAYANK991]

Hi ,

My comment on using borders was only with a view to automating it ; if you have no issue with doing the cell colors manually , then the problem comes down to counting the cells which are not colored.

Doing this using the old GET.CELL macro function is probably worse than doing it with a macro , since a macro is a standard feature of Excel , whereas the old XLM functions such as GET.CELL are no longer supported , though they still work.

How to do it with the macro function is explained here :

http://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color

Narayan

 

[MalR]

Thanks again Narayan. It looks good. I am tied up for a few days so I will try it and get back to you.

 

[MalR]

Thanks for all your help guys. I will be tied up for a week or so but wanted to let you know I will be working on it when I get a chance. I have enough information from this forum to allow me to do some experimenting.
It was most helpful
MalR

 

[MalR]

You could have a udf and use it thus on the sheet:
=Occupancy(J5:J16)
backed up by a function in code:

Function Occupancy(myRange As Range)
For Each cll In myRange.Cells
  If cll.Interior.ColorIndex = xlNone Then zz = zz + 1
Next cll
Occupancy = 1 - zz / myRange.Cells.Count
End Function

as in the attached.

 

[MalR]

p45cal, I have pasted your code into a new module in my spread sheet. The clips in the file attached show the vba and they also show that when I call up "=Occupancy" and tell it what cells to calculate it returns #VALUE!.
The code works in the spread sheet that you attached and I can run extra rows and it still works but when I copy/paste (cntrl+c, cntrl+v) the code into the worksheet where I want it I get the error message.
Google says the cells are not formatted correctly if I get this error but they are formatted as "general" the same as your spread sheet.
Is this enough information for you or do you need a section of my worksheet?
Thanks in advance for your help on this
MalR

 

[p45cal]

I'd need to see a section of your worksheet.

 

[MalR]

Thanks very much for your help p45cal.

 

[p45cal]

2 things about that file:
1. the only formula (cell G25) contains an incomlete formula:
=Occupancy
when it needs to be
=Occupancy(D7:D20)
or similar.

2. There is no User-Defined Function code in that file - in fact no code at all! So I get the error #NAME?
The code supplied in msg#2 of this thread needs to be available to the formula.

 

[MalR]

Thanks for the quick reply p45cal. I just supplied a section of the worksheet and thought maybe you could add your code. In other words I give you the sheet and you code it up in case I was doing something incorrect.
I have enclosed the same worksheet with the code in it now. It still produces the error #VALUE!. Hoping you can assist with this error.
Thanks again

 

[p45cal]

You have a Name in the file also called Occupancy. Delete it.

 

[MalR]

Thanks. Not sure exactly what you mean. I renamed the worksheet and the file. Is this any better?

 

[p45cal]

In the formulas tab of the ribbon click on Name manager then delete the name Occupancy.

 

[MalR]

Great! I never would have found this solution. I will use this macro daily.
One last thing. It displays 0.5 instead of 50%. I can format the cells in the worksheet that it displays in but is there any way in the body of the macro eg properties that I can set it? If so can you indicate the steps to get into that area to change it. I cannot see anywhere to do this.

 

[p45cal]

No. It's a function which only returns a value . It's up to you to format that value as you will.

 

[MalR]

OK. Thanks you gave me a greater understanding on macros. This is a great macro. I am going to file it as my "p45cal macro" along with my Narayan, Nebu and SirJB7 ones!
Case closed now.

 

[MalR]

p45cal could you look at this workbook please. Sometimes the macro will not work

 

[p45cal]

This is going to be a bit more convoluted; at first you wanted a count based on just colour, now you want to include patterns. Once someone has added a colour and/or a pattern and changes it back to look white without a pattern, it can be done in several ways: make the cell white instead of no fill, make the pattern style solid (not none), make the pattern colour Automatic, or make it white, etc.
This makes it difficult to tell programmatically whether a cell looks white/no pattern, or not.
Yoiu could stick just with only colours (not patterns) to determine occupancy, and/or you could use the ribbon's Home tab, Cells section, Clear dropdown and choose Clear Formats to remove occupancy, when everything will be reset to no fill, no colour, no pattern colour, no pattern style.
I'll look at it tomorrow.

 

[MalR]

Thanks. There should have been a hatch in the original file. The hatch warns us of certain things. There are not many hatches used and I can use an alternative method of highlighting if it helps. That might be easiest.
You have been most helpful and I had no idea what the macro was doing and how it worked. I make all my macros using the recorder. Now I know more about it I am sure I can modify my practices to accommodate it. It seems to read some colours with hatches Ok but stumbles on others. I will play with that. The colours are manually selected. The colours have no relevance other than to designate start and end of booking. That is why there are different colours. You can understand the importance of colours from our website oceanviewtouristpark.net.au. Tab is Availability.
Comments are routinely used and are essential. In all cases there will be a comment, a colour and text in the same cell. It appears that the macro might not like comments? That part needs to work or make the macro throw an error message so I can do a manual calc.
You have given me plenty of time and supplied a code that I will use one way or another. I spent weeks trolling the net and found nothing like it. It is obviously quite complicated. So if you have other projects I understand and I thank you for what you have supplied.

 

[p45cal]

Cell comments are not causing this problem.
On sheet4, the disparities are caused by P7, O7, D12, E12 amongst others.
Can you confirm that you want a hatched cell without colour to count as occupied accommodation?