OK...take a look at the attached. I've only entered the data for the two periods in 2015 for which you had data in your table. I have used the Format as Table functionality. If this format is amenable to you, you will see how powerful that functionality and it will further enable other powerful tools too such as the previously mentioned Pivot Tables. But lets start with this and I'll go over a few details about what this can do.
* As you can see, the table is nicely color formatted. This is all automatic. If you insert, add, or delete rows or columns, they will automatically format. If you prefer different formatting scheme, you can select from several at Home: Format as Table.
* I did not include the fields for Occupancy or Average RR. Should you want to add these or any other field you can do it a few ways. First, the cell to the right of the Total header in the first row, type "Occupancy" and hit Enter. Excel will automatically create a new column. You can also insert a column (or row) in the middle of the table and selecting insert from the popup context menu.
* Here's a thing a I like a lot. In the first cell under Occupancy, enter a formula. Anything just to check it out. When you hit enter, Excel copies that formula through all the rows in the columns. Neat huh?
* To add more rows at the end of the table, simply grab and drag the little red triangle in the bottom right of the rightmost cell in the totals row.
* The totals row allows you to summarize the displayed dat in many ways: Count, Sum, Min, Max, Avg, etc. To select any one of these, click on the cell below the data for which you want summary calculation. A drop down button will appear to the right. Click it and select the function you want.
* These calculations will update accordingly to the data displayed through your filter choices. Perhaps you only want to see and summarize the data for period 2. Click the drop down button to the right of the Period header, uncheck 1, and click OK. Only the period 2 data are displayed and the summary calculations update automatically.
That's it for now. If this seems like a way you might want to go, we can delve further into what you would like to do...pivots, charts, etc.
|