• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

extract the average amount from sheet 2 in microsoft excel and display it on sheet 1

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!

Mark

example-sheet.xlsx
0
accommodation
Asked:
accommodation
  • 4
  • 4
  • 2
1 Solution
 
TigerManCommented:
Hi accommodation
this is a sumif/countif solution
the formulae can be dragged down to any number of rows
example-hotels.xlsx
0
 
barry houdiniCommented:
You can use AVERAGEIF function, i.e. this formula in D2 copied across and down

=AVERAGEIF(Sheet2!$A:$A,$A2,Sheet2!C:C)

regards, barry
0
 
barry houdiniCommented:
See example attached using that AVERAGEIF formula

regards, barry
27501365.xlsx
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
accommodationAuthor Commented:
Hi, I think the sum-if-count-if solution works best, would it be possible to count the number of reviews for each property also for each property and display this as a number on sheet one as demonstrated on the attached file?
sum-if-count-if.xlsx
0
 
TigerManCommented:
hi
certainly ... its a straight countif that can be copied down at your leisure
countif-example2.xlsx
0
 
accommodationAuthor Commented:
Great Stuff!
0
 
barry houdiniCommented:
>I think the sum-if-count-if solution works best

Hello accommodation, it's your choice, of course, but I'm not sure how you come to that conclusion. Doesn't AVERAGEIF give you the same results with a shorter formula using less functions?

AVERAGEIF function doesn't exist in Excel versions earlier than 2003 so if you want "backwards compatability" or the ability to share with 2003 users then TigerMan's solution would probably be preferable

regards, barry

0
 
TigerManCommented:
hi barry, just fwiw, it doesn't exist in 2003 either ...
0
 
TigerManCommented:
he may have tried averageif and found it didn't work :)
0
 
barry houdiniCommented:
Hello TigerMan,

Yes, you're right - I meant to say it doesn't exist in versions before 2007. I note that the file that was attached is an xlsx file which means at least Excel 2007 so I would expect AVERAGEIF to work...although I note that SUMIF/COUNTIF results could differ from AVERAGEIF if there are instances where the criteria is found in one column but the column to average is blank.

Nevertheless you posted an excellent working solution, TigerMan

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now