CODEWITHSUNDEEP
pythonexcelcsv
Alter
Alter

Reputation: 3464

Average columns based on a key column

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

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

Upvotes: 0

Views: 230

Answers (1)

canyon289
canyon289

Reputation: 3495

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

Upvotes: 1

Related Questions