I have 2 sheets in my Microsoft Excel Database. The first sheet contains information about hotels such as hotel name, hotel address, and a property code which is unique to each hotel.
The second sheet contains the property code and customer votes for each hotel. The customer grades each hotel (scale of 1 - 10) on Rooms, Staff, Food, Recommend to a Friend, Checking in, Cleanliness and Bar.
All of the information for each hotel (Hotel Name, Hotel Address and Property Code) is displayed in one row on sheet one. I would also like to add the average customer grades to sheet one for Rooms, Staff, Food, Recommend to a Friend, Checking in, Cleanliness and Bar.
Sheet 2 contains the unique property code to identify each hotel. As each Hotel has votes from many different customers each hotel has more than one entry on sheet 2. I would like to calculate the average score for Rooms, Staff, Food, Recommend to a Friend, Checking in, Cleanliness and Bar.
To provide clarity I have attached a sample excel file with demo data.
So for example on the first line hotel “HGT56RT” has 4 reviews. If you look at sheet 2 you can see that the customers have rated the rooms 1,3,2,2. So the average room rating for hotel “HGT56RT” is 2. (1+3+2+2 = 8 / 4 = 2)
Obviously the real system has many more hotels and reviews..
Any help would be appreciated!