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
  • 70,606
  • 8
  • 39
  • 70
Matt
  • 4,140
  • 9
  • 40
  • 64

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
  • 30,391
  • 4
  • 21
  • 36
  • Brilliant thanks! Is there a comparable formula for google sheets as I have to do the same thing there as well – Matt Mar 01 '21 at 04:02
  • 1
    You can also use: `=AVERAGE(FILTERXML(""&TEXTJOIN("",,B2:J2)&"","//s[position()>=last()-3]"))` – JvdV Mar 01 '21 at 10:07
  • 1
    @JvdV It `//s[position()>=last()-3]` returns 4 node while `//s[position()>=last()-2]` return 3 nodes from last. So, I think `//s[position()>=last()-2]` this will be correct. – Harun24hr Mar 01 '21 at 10:17
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
  • 70,606
  • 8
  • 39
  • 70