Excel formula for calculating average of duplicates

I have certain repeated values in the first column. I want to average out those values based on the number of times they are repeating themselves. I want the results in the adjacent columns.

Something like below:

• There are 10 12100 values, so average is 1210
• There are 5 4550 values, so average is 910

2 Answers

Your use of average is not entirety correct, but I think I get what your after.

Assuming you data is in column A, use this in B1 and drag down:

=A1/COUNTIFS(A:A,A1)

FYI the average of 10 values all equalling 12100 is actually 12100.

5

@PeterH if you see in the attached picture 3615 is repeated 4 times but it gives 451, instead of giving me 903.75 four times.

enter image description here

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy