-1

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 

picture for example

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
0

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.

enter image description here

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
-1

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")

I hope this help you.

  • This doesn't do what was asked in the question. – fixer1234 Feb 19 '18 at 10:08
  • @fixer1234,, this is the best possible method to create Data point using Countifs to Calculate Average. Suggest if any other method is in your mind. – Rajesh S Feb 19 '18 at 10:23
  • The question is about pairs of rows. Your approach calculates all qualifying rows (e.g., 4 rows for "1" & "red" rather than two separate results). – fixer1234 Feb 19 '18 at 10:26
  • @fixer1234,, yes I know,, but you look first two Rows had common ID but 2 different colours, and colours are in alternation, so it doesn't make any scene to calculate Average. What do you think,,, am I correct? – Rajesh S Feb 19 '18 at 10:58
  • 1
    @RajeshS I appreciate the answer but it does not answer my problem. The problem is I need the first 2 rows of data for ID = 1 Color = Red and then I need the next 2 rows of data for ID = 1 Color = Red. I want this separated but I'm not sure if there's an excel formula for this which worries me since I have so many rows of data. :s – Iliana Feb 19 '18 at 17:49

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.