In Excel how to find a average from selected cells

Question

I have an excel sheet with two columns, like this:

I'm trying to calculate the average of the numbers in the cells in column 2 that match the numbers in column 1 that either end in 2 or 5. Column 2 should not be used if any of the cells are blank or contain a letter.

Answer 1

If one has the dynamic array formula FILTER():

=AVERAGE(FILTER(B2:B15,ISNUMBER(MATCH(--RIGHT(A2:A15),{2,5},0))))

If not then use this array formula:

=AVERAGE(IF(ISNUMBER(MATCH(--RIGHT(A2:A15),{2,5},0)),B2:B15))

As it is an array formula, exiting edit mode requires pressing Ctrl-Shift-Enter rather than Enter.