Average columns based on a key column

Question

I'm trying to average values in columns E, F, G ... based on which key they have in column A.

I have an excel formula that works, I got it from adapting this post: =AVERAGEIF(A:A,0,E:E). However, there are potentially n keys (up to 100000), and m columns of values (up to 100)

My problem is that typing in that formula for each variation isn't logical. Is there a better way to do this; so that it takes into account the varying groups and columns?

NOTE

The first 7 rows are junk (they contain info about the file)
The data is in CSV format, I am only using Excel to open it
I am currently looking into modifying this script, but as I am unfamiliar with python so it may take some time

EXAMPLE
Column A is the group, column E are the values to be averaged. Column F contains the output averages (this is a little messy because it doesn't show which groups the averages are for, but they are just in ascending order, ie: 0, 1, 2)
enter image description here

What do you mean by "n groups"? n rows (up to 100k)? And what about "m columns", what does that mean? and where would you like the results output? — Amit
– Amit, Commented May 21, 2015 at 19:58
n groups (up to 100k), means that there are n unique group names which should each have their own set of averages. m columns is the number of columns that have values that should be averaged. the columns aren't combined, so if there are 5 columns of values, there will be 5 columns of averages — Alter
– Alter, Commented May 21, 2015 at 20:02
@canyon289 yes, I'm trying to group the values by the value in column A. However, after I group them, I want to average them as well — Alter
– Alter, Commented May 21, 2015 at 20:03

canyon289 · Accepted Answer · 2015-05-21 20:04:49Z

1

If I understand you correctly I would use a Pivot Table to summarize your data. You can group by column A and then get the means of the rest of columns.

On the ribbon interface click "Insert" and select "Pivot Table". You'll then be prompted to select your data and set a location.

After that you should see a window on the right asking for fields. Drag the Column A field to the Row Labels list, and then the columns you want averages of to the values list. You might need to change the value field settings from the default sum to average which is what you want.

https://support.office.com/en-AU/Article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

answered May 21, 2015 at 20:04

canyon289

3,5955 gold badges36 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alter Over a year ago

I like this solution, it worked on the first go. It can be a bit of a nuisance because I have to drag each of the columns I'm averaging into the "values" box (takes a while when there's 50+ of them)

canyon289 Over a year ago

I agree with you. The fastest way to do this hands down is to learn a bit of R or Python/Pandas and use that. It'll get what you need done in two or three lines of code. It's overkill if this is all you need but having to do what you did got annoying enough for me to learn.

Collectives™ on Stack Overflow

Average columns based on a key column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related