1

enter image description here

enter image description here

I have been trying to average out values in column 1 into the values as mentioned in column 2. For example if: there are 10 12100 values, so individual average is 1210 There are 5 4550 values, so average is 910

I have used the formula 

=A1/COUNTIFS(A:A,A1)

But, it does not seem to work for the repeated values in one column. The second picture explains the problem. It is taking into consideration all the duplicates in the list and then giving me an average. What I want instead is the average of the numbers repeating consecutively. Implying, if 3615 repeats 4 times in column A1 to A4 I should get 903.75 in B1 to B4. Also, If it is repeating again in A24 to A28, I should be able to get 903.75 in B24 to B28.

But, now it is giving me 451.88.

cybernetic.nomad
  • 5,415
  • 12
  • 25
Sheenam
  • 11
  • 3

1 Answers1

0

Add a helper column.

You'll create a new column to give the consecutively repeated chunks an ID number.

If your data as shown starts from A2, set B2 to 1 and add this formula in B3, then drag/copy down:

=IF(A3=A2,B2,B2+1)

This detects a change in value in your data and increments or repeats the ID number as appropriate. That means the formula in your average column is similar to as you have it, except the helper column is the subject of the condition in the COUNTIF. Add this formula in C2 and drag/copy down:

=A2/COUNTIF($B$2:$B$14,B2)

voila

Alex M
  • 932
  • 6
  • 17