probability of analisys based on historical data

Originally Posted by MrShorty

It seems like the simplest measure of "probability of occupancy" would be the fraction occupied in the current data set. Pseudocode =count(years occupied for a given date)/total number of years the data covers.

I may not been clear in my post. I need to determine occupancy per each day based on several years of data. It may take several steps to accomplish. I was thinking of doing this:
1. since I only have arrival and departure dates a macro will be needed to fill the rest of the dates in-between.
2. create a pivot table to display a number depending on occupancy. For example, I should see that a given day was occupied 3 times in 7 years. I can make a decision to adjust pricing based on that data.
3. Macro to loop to next row

Unless someone knows a simpler way, I could use help in creating that code.

Why don't you copy paste all the years into one Sheet, such that the days are the rows (E.g. 1 Jan, 2 Jan, 3 Jan.....) and the years the columns (2005, 2006, 2007....), then give a 1 to occupied days and a 0 to non-occupied days and then sum the rows (E.g. B2 to B8) and divide by 7 (the number of years). To the cell summing up (Say B9) you can format the cells such that if occupancy is for example below 0,40 then it's coloured red, if it's between 0,4 and 0,7 let's say yellow and >0,7 green.

Finally, you could create a table in which the days in which the days in red appear or order the days of the year from lowest occupancy to highest occupancy. This way in an eye glimpse you can tell in which days it may be convenient to adjust the price (i.e. the first of the list or the red ones).



It's a simple way of doing it. I'm sure there's a cleverer way of doing it though.

Thank you for your help. Your explanation is simple and clear.