CODEWITHSUNDEEP
excel
Wyatt Ernst
Wyatt Ernst

Reputation: 25

Match Two Columns and Get Average From Third Column Displaying In Fourth Column

Okay, this question is pretty complicated and may not be possible.

I have Zip codes in Column A (on average the same zip code is repeated 5 times in column A, with 2600 unique zip codes), I then have a number representing students in Column B (in some cases this number is sometimes 0), Then in Column C I have the unique Zip Codes Listed out.

I am trying to match Column A & C together and get an Average of Column B placed in Column D.

I tried: =INDEX(B:B,MATCH(C2,A:A,0)) found in another answer, but this only get's me the first number that shows up in Column B and not the Average for those zip codes.

I have attached an Image of what it would look like (with me manually doing the math)

Image of Ideal Solution Manually Entered

Upvotes: 2

Views: 307

Answers (1)

EoinS
EoinS

Reputation: 5482

Try this:

=AverageIf(A:A,C2,B:B)

This is a great formula that does basically exactly what you are trying to do.

more info

The formula was designed to do exactly what you are trying to do. It performs Sumif/Countif. This will scan Col A for all rows that match your cell C2. Then it takes the average of all Col B values for those matched rows.

You could drag the formula down for all unique Zip Codes in column C.

Upvotes: 1

Related Questions