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
accommodationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.