average values based on values in another column

[bjcowen90000]

Hey, I have an excel document in which the numbers 1-20 repeat in column A.
Column B has numbers that I want to average. Is there a formula that can average all the values in B that correspond to an A value of 1? And then for 2?

Basically, I want a C column,
C1 will contain the averaged values in column B that correspond to an A column value of 1.
C2 will contain the averaged values in column B that correspond to an A column value of 2.

Thanks for your help.

 

[AliGW]

I believe you have asked this question on another forum, so the etiquette is to provide a link back to your thread in the other forum so that members here can see what has already been suggested.

 

[AndyPandy27]

Hi bjcowen90000,

I believe you can do this using AVERAGEIFS.

Assuming your repeating 1 - 20 in Col A goes down to Row 100:

In C1: =AVERAGEIFS(B1:B100,A1:A100,1)
In C2: =AVERAGEIFS(B1:B100,A1:A100,2)

Good luck,

AP

 

[Joe4]

Cross-post link: averaging every nth column working for all but 1 case

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[JohnnyB1]

In the spreadsheet below I would like to average the values in column C when the values in column E are the same. Thus, when column E is 1, average the values C4:C8. Put that average in F8. When column E is 2 average C9:C10. Put the average in F10, and so on until I have an average for each unique value in column E. Thanks for your help, JohnnyB



-- removed inline image ---