• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Doing mean of several columns matching the proper data

mikelowski

mikelowski

New Member
Hi, fellows

I have columns where every row is a country. I have to do the mean for every matching country in the columns. Problems is every column has different countries and I don't know how to do the mean of the matching countries. Maybe you will see it clearer in the example file.

Thanks and regards
 

Attachments

Hi,

Can you explain your problem with some examples on the data. What is the purpose of countries listed on row 2?

Regards,
 
If you want say the average of say Column Brazil where it matches Chile in Column G
=AVERAGEIFS(OFFSET($D$4:$D$55,,1+MATCH("Brazil",$E$2:$N$2,0)),OFFSET($D$4:$D$55,,MATCH("Brazil",$E$2:$N$2,0)),"Chile")
will do the job
 
My purpose is to get a mean of the column countries, called Latinamerica and then get the countries in rows with highest scores. Problem is there are different countries depending on the column.
 
Hi ,

Can you look at your file and say whether you need the average of the countries highlighted in RED row-wise or column-wise or in any other fashion ?

Narayan
 

Attachments

Nope, I need the average of common countries from the countries in the columns.

For example, if there were 13 common countries, then averaging them with every respective score in the column countries.
 
Hi,

Copy all countries to a single coulmn and create a pivot which will give you a unique list of all countries which you can paste to column O.

Then in Column P use the below formula (Change range according to your requirement)

=(SUMIF($E$4:$E$34,$O4,$F$4:$F$34)+SUMIF($G$4:$G$55,$O4,$H$4:$H$55)+SUMIF($I$4:$I$34,$O4,$J$4:$J$34)+SUMIF($K$4:$K$34,$O4,$L$4:$L$34)+SUMIF($M$4:$M$34,$O4,$N$4:$N$34))/(COUNTIF($E$4:$E$34,$O4)+COUNTIF($G$4:$G$55,$O4)+COUNTIF($I$4:$I$34,$O4)+COUNTIF($K$4:$K$34,$O4)+COUNTIF($M$4:$M$34,$O4))
 
Last edited:
Hi, guys

Sorry, because english is not my first language. What I have to do is to find the common countries among those 5 columns, so somehow finding duplicates present in all columns and discard the rest.
 
@mikelowski

It will be better if you explain your problem with a example in the data. Say for France is in all five column, than what?

@Hui Thanks , but unfortunately OP wants something else.

Regards,
 
Then we keep France. And like this with the rest. If one country is not present in some column, then must be deleted.
 
You must log in or register to reply here.
Back
Top