0

If I have a range in excel, say A2:I2, that is used to store the results from a monthly competition, what formula do I use in excel to return the average of the three most right values? The formula needs to account for not all columns being filled, i.e. one player might have 5 results, one might have 9, and one might have 1.

enter image description here

Thanks

JvdV
  • 41,931
  • 5
  • 24
  • 46
Matt
  • 3,838
  • 9
  • 36
  • 61

2 Answers2

3

You can combinedly use TEXTJOIN() and FILTERXML() like below.

=AVERAGE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,B2:J2)&"</s></t>","//s[position()>" & COUNTA(B2:J2)-3 &"]"))

enter image description here

  • Here "<t><s>"&TEXTJOIN("</s><s>",TRUE,B2:J2)&"</s></t>" will construct a valid XML string.
  • XPATH parameter "//s[position()>" & COUNTA(B2:J2)-3 &"]" will return last 3 nodes to calculate Average by AVERAGE() function.
  • COUNTA(B2:J2)-3 will detect how many nodes have before last 3 nodes so that we can return rest of nodes means always last 3 nodes by FILTERXML() formula.

More about FILTERXML() here by JvdV Extract substring(s) from string using FILTERXML

Edit: For google sheet you can use below formula.

=AVERAGE(FILTER(B2:J2,(COLUMN(B2:J2)-Column(A2))>IF(COUNTA(B2:J2)<=3,0,COUNTA(B2:J2)-3)))
Harun24HR
  • 12,232
  • 3
  • 14
  • 26
2

If one has Microsoft365, you could also use:

enter image description here

Formula in K2:

=AVERAGE(INDEX(SORTBY(FILTER(B2:J2,B2:J2<>""),SEQUENCE(1,COUNT(B2:J2)),-1),SEQUENCE(MIN(3,COUNT(B2:J2)))))
JvdV
  • 41,931
  • 5
  • 24
  • 46