Pivot Tables Calculated Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need some assistance in a Pivot table. I am dealing with a hotel inventory spreadsheet that has the following column
A) Hotel Name B) Room Type C) BlockType D - K) Room Dates 06/01/2004, 06/02/2004 ETC ... The data that appears in Block Type is either "Rooms Blocked" or "Rooms Sold" under eacn date is the count for each date Example

"Hotel Name", "Room Type", "Block Type", "06/01/2004", "06/02/2004", "06/03/2004", "06/04/2004", "06/05/2004" ...
"Hyatt Regency", "1 Bdrm Suite", "Rooms Blocked", "150", "175", "200", "200", "150
"Hyatt Regency", "1 Bdrm Suite", "Rooms Sold", "125", "160", "190", "195", "130
.... more hotel names and room type

I am creating a pivot table that breaks on HotelName, Room Type and Block Type. I'm getting the counts just fine, but I would like to also add some calculated fields such "Rooms Available" which should be calculated as the "Rooms Blocked" minus the "Rooms Sold" and have this show for each date. I would also like this calculated field to appear on the pivot table for each room type and each date

My subtotals by hotel and room type should not sum( "Rooms Blocked"+"RoomsSold") but should sum "Rooms Blocked" and a separte sum for "Rooms Sold" for each hotel/room type combination

Next, I would like to be able to calculate some percentages for each date. Example "200" is the maximum "Room Block" for any of the dates. I would like to calculate the percentage for each date of "Rooms Blocked" / Max ("Rooms Blocked") Can this be done and show up on the pivot table

Many thanks in advance for your assistance
 
John

To add the calculated field go to the pivot table, put cursor anywhere in you data (numbers) then go to Insert on the toolbar and click insert calculated field. You will be able to choose the fields and operations you want to perform. You will be prompted to give the field a name (Rooms Avail)

Rows: should be Hotel name, Room Type, Block Typ
Columns: should be date
Data: should be Rooms Blocked, Rooms Sold, and your newly created Rooms Avail
You can check these by right clicking anywhere in the pivot table and selecting wizard and clicking layout. This is a good place to change your subtotalling options. Double click on the field in question and verify how it is totalling

Hope it help

Matt
 
Back
Top