Average non-adjacent cells (with criteria)

[MonicaK]

Hi,

I am stuck on creating a formula for the below and would be really grateful for some assistance:

The table has 4 columns. Column A is text e.g. Apples, Bananas etc. Columns B,C,D are currency. More than one row in column A will contain Apples.

I want to find all rows which state Apple then sum the corresponding figures in columns B and D only and divide that sum by the count of Apples to obtain a correctly averaged figure.

I looked at averageif but couldn’t figure out how to use column B and D without C, and I wasn’t sure if the average would calculate correctly.

Any ideas?

Thanks,

Monica

 

[Eric W]

Hi Monica, welcome to the MrExcel forum!

AVERAGEIFS should work fine for you:

Book1
	A	B	C	D	E	F
1	Fruit	Amt1	Amt2	Amt3
2	Apple	1	2	3		9.666667
3	Pear	2	4	5
4	Apple	3	6	7
5	Banana	4	8	11
6	Cherry	5	10	13
7	Banana	6	12	17
8	Apple	7	14	19
9
10
11
12	Fruit	Amt1	Amt2	Amt3
13	Apple	1	2	3		9.666667
14	Pear	2	4	5
15	Apple	3	6	7
16	Banana	4	8	11
17	Cherry	5	10	13
18	Banana	6	12	17
19	Apple	7	14	19
Sheet8

Cell Formulas
Range	Formula
F2	F2	=AVERAGEIFS(D2:D8,A2:A8,"Apple")
F13	F13	=AVERAGEIFS(Table1[Amt3],Table1[Fruit],"Apple")

I wasn't sure if you had a generic table, or an actual Excel table, so I showed it both ways. AVERAGEIF (without the S) would also work with a little different format. Hope this helps!

 

[MonicaK]

Thank you for taking the time to respond Eric.

That formula seems to only calculate the average for column D, whereas I was looking to include the figures in both column B and column D. Using your figures I want it to calculate Apples as 1+3+3+7+7+19=40 divided by the 3 Apple rows = average of 13.33 per row. Is that possible?

I do have an excel table using Micrsoft365.

Thank you.

Monica

 

[Peter_SSs]

Welcome to the MrExcel board!

I do have an excel table using Micrsoft365.

Is this what you want then?

23 07 09.xlsm
	A	B	C	D	E	F	G
1	Fruit	Amt1	Amt2	Amt3
2	Apple	1	2	3		Apple	13.33333333
3	Pear	2	4	5
4	Apple	3	6	7
5	Banana	4	8	11
6	Cherry	5	10	13
7	Banana	6	12	17
8	Apple	7	14	19
Average

Cell Formulas
Range	Formula
G2	G2	=LET(f,FILTER(CHOOSECOLS(Table1,2,4),Table1[Fruit]=F2),SUM(f)/ROWS(f))

 

[MonicaK]

Welcome to the MrExcel board!


Is this what you want then?

23 07 09.xlsm
	A	B	C	D	E	F	G
1	Fruit	Amt1	Amt2	Amt3
2	Apple	1	2	3		Apple	13.33333333
3	Pear	2	4	5
4	Apple	3	6	7
5	Banana	4	8	11
6	Cherry	5	10	13
7	Banana	6	12	17
8	Apple	7	14	19
Average

Cell Formulas
Range	Formula
G2	G2	=LET(f,FILTER(CHOOSECOLS(Table1,2,4),Table1[Fruit]=F2),SUM(f)/ROWS(f))

 

[MonicaK]

Yes, perfect! Thank you Peter. I am actually using it on Sheet1 cell A1 as:

=LET(f,FILTER(CHOOSECOLS(Sheet2!$A:$E,2,4),Sheet2!$A:$A=“Apple”),SUM(f)/ROWS(f))

Are you able to assist with the next calculation I need to do where I am adding in another criteria? Column E has text in it, say Frog, Goat etc. and I want to calculate as above but for rows showing both Apple (Sheet2!$A:$A=“Apple”) and Frog (Sheet2!$E:$E=“Frog”)

Thank you.

Monica

 

[Peter_SSs]

Is your data in a formal table or not?

 

[MonicaK]

My data is the result of a Query and appears to be in a table as the Table Design button shows a table name.

 

[Peter_SSs]

Then does my previous formula work if you apply the relevant table name and column heading?
I'm asking because it is pointless and inefficient if you are asking Excel to check over 1 million rows to see if they are "Apple" if your actual data is only a few hundred or a a few thousand rows.

 

[MonicaK]

Hi,

In reality my table is column A to Z and up to 100 rows long. I modified your original formula to fit the name of my table and sheet and that works. However, now, on a completely separate workbook I now need to pull the same data but with an additional criteria. That criteria is listed on my new workbook and is also contained within the original table. I was hoping to add a vlookup up at the front of your formula but can’t seem to do that so I looked at adding another criteria after “Apple” criteria in your formula but that didn’t work either. Apologies for not being able to upload a copy of my data as it is sensitive.

To simplify matters, using the earlier example table of A to D if it now had a column E populated with either Frog or Goat then the next formula I need should calculate the average of B and D where E=Frog and A=Apple.

Thanks for yours assistance.

Monica