# Excel find average of column for each X rows of data

I have a large dataset filled with multiple data points. I've included a picture which consists of the following:

``````    column A = IDs
column B = Colors
column C = Data points need to average
``````

I need the average of only the first 2 rows of data (column C) where the ID (column A) = 1 and the color (column B) = Red. Then I would need the average of the next 2 rows of data with the same criteria. Then I would do the same to find the average where ID = 1 and Color = Blue. Then I would do the same where ID = 2 and Color = Red.

Normally I would do an averageifs formula but since I only need the first X rows of data to be averaged then I'm not sure how to tackle this problem. One additional caveat is that not every ID has an equal number of data points. So ID 1 Color Red could have 4 data points while ID 2 Color Blue could have 6 data points. Any help would be appreciated.

• You want to keep averaging pairwise until all data points for each ID/Color group have been calculated? Will the count of a given ID/Color group always be even? – Bandersnatch Feb 18 '18 at 23:51
• So would the first result be 2.75 (rows 2 & 4), and the second result be 2.2 (rows 6 & 8)? Third result = 2.25 (rows 3 & 5)? – fixer1234 Feb 19 '18 at 10:07
• @RajeshS, see my comment above asking the OP for verification. That would seem to be the only interpretation consistent with the wording (which is a bit unclear). – fixer1234 Feb 19 '18 at 10:42
• @fixer1234,, by mistake i've posted the comment here,, now please read below my solution next to yours between us. – Rajesh S Feb 19 '18 at 11:00

Thank you, Iliana, for the clarification. The key to solving your problem is to (figuratively) divide your data up into groups of four rows. Then a formula can average the two values for each color in that group.

You've explained that a given ID could have 7 to 10 rows of data. So not all groups will have four rows. The following approach uses a helper column to designate groups of four (or fewer) rows for each ID.

The table below has 7,8,9 and 10 rows, respectively, for the four ID's shown, and this formula, filled down from D2 in the table below, divides the data for each ID into groups of 4 or less, and designates the groups with a number from 0 to 2:

`=IF(COUNTIF(A\$2:A2,A2)>8,2,IF(COUNTIF(A\$2:A2,A2)>4,1,0))`

This formula counts the number of occurrences of each ID as it is filled down. If the count is > 8, it sets the helper to 2. If the count is > 4, the helper is set to 1. If the count is 4 or less, the helper is set to 0.

Now the averages can easily be calculated. This formula gives the average of the outcome values for each unique ID, Color, and Helper combination:

`=AVERAGEIFS(C\$2:C\$35,A\$2:A\$35,A2,B\$2:B\$35,B2,D\$2:D\$35,D2)`

Finally, duplicate values can be eliminated with a little extra effort. This formula counts the number of ID/Helper combinations in a range that "grows" as the formula is filled down. If the count is 3 or more, a blank is returned instead of the average.

`=IF(COUNTIFS(D\$2:D2,D2,A\$2:A2,A2)<3, AVERAGEIFS(C\$2:C\$35,A\$2:A\$35,A2,B\$2:B\$35,B2,D\$2:D\$35,D2),"")`

Thank you again for clearly explaining what you are trying to accomplish. I hope you find this helpful. Best of luck.

• Interesting, I've never seen the formula used for the int column before. Can you explain how this works? I tried searching but didn't find anything. In answer to your questions: 1) No, there will not always be an even number 2) 7-10 datapoints for each combinations 3) If the helper column works then I am fine leaving it there. 4) Duplicate averages are okay – Iliana Feb 20 '18 at 2:25

You can use a combination of the SUMIFS and COUNTIFS function to calculate AVERAGE for the pairs of ID and Colour in terms of creating Data Points(Match Rows), where COUNTIFS creates Data Point

``````=SUMIFS(C2:C17,A2:A17,"1",B2:B17,"RED")/COUNTIFS(A2:A17,"1",B2:B17,"RED")

=SUMIFS(C2:C17,A2:A17,"1",B2:B17,"BLUE")/COUNTIFS(A2:A17,"1",B2:B17,"BLUE")

=SUMIFS(C2:C17,A2:A17,"2",B2:B17,"RED")/COUNTIFS(A2:A17,"2",B2:B17,"RED")
``````