In Excel how to find a average from selected cells

0 votes

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

his:

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 by Kithuzzz
• 28,900 points
168 views

1 answer to this question.

0 votes

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))

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

enter image description here

answered Oct 9, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8 in Others by narikkadan
• 53,520 points
118 views
0 votes
1 answer

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

answered Oct 24, 2022 in Others by narikkadan
• 53,520 points
150 views
0 votes
1 answer

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

answered Dec 19, 2022 in Others by narikkadan
• 53,520 points
110 views
0 votes
1 answer

Excel: How to analyze data in a table that contains multivalue cells

 The below formula will create a unique ...READ MORE

answered Jan 10 in Others by narikkadan
• 53,520 points
44 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
532 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

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

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
479 views
0 votes
1 answer

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

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

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,501 views
0 votes
1 answer

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

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,014 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 53,520 points
279 views
0 votes
1 answer

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

answered Jan 5 in Others by narikkadan
• 53,520 points
86 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP