CODEWITHSUNDEEP
excel
Pedro
Pedro

Reputation: 1

In Excel, how to find a average from selected cells

Need your help. I have an excel sheet with two columns, like this:

his:

I would to find an average of the number in the cells from column 2 that correspond to the numbers in column 1 which end either in 2 or 5. If column 2 has a blank cell or a letter, it should be ignored.

Upvotes: 0

Views: 884

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

If one has the dynamic array formula FILTER():

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

enter image description here

If not then use this array formula:

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

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 1

Related Questions