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.

Oct 9, 2022 in Others 525 views

## 1 answer to this question.

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.

• 63,700 points

## How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

## How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

## How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE

## In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

## Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

## How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

## How to remove borders from cells in a range in Excel using VB.net?

range.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle ...READ MORE