Average non-adjacent cells (with criteria)

MonicaK

New Member
Joined
Jul 8, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Monica, welcome to the MrExcel forum!

AVERAGEIFS should work fine for you:

Book1
ABCDEF
1FruitAmt1Amt2Amt3
2Apple1239.666667
3Pear245
4Apple367
5Banana4811
6Cherry51013
7Banana61217
8Apple71419
9
10
11
12FruitAmt1Amt2Amt3
13Apple1239.666667
14Pear245
15Apple367
16Banana4811
17Cherry51013
18Banana61217
19Apple71419
Sheet8
Cell Formulas
RangeFormula
F2F2=AVERAGEIFS(D2:D8,A2:A8,"Apple")
F13F13=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!
 
Upvote 0
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
 
Upvote 0
Welcome to the MrExcel board!

I do have an excel table using Micrsoft365.
Is this what you want then?

23 07 09.xlsm
ABCDEFG
1FruitAmt1Amt2Amt3
2Apple123Apple13.33333333
3Pear245
4Apple367
5Banana4811
6Cherry51013
7Banana61217
8Apple71419
Average
Cell Formulas
RangeFormula
G2G2=LET(f,FILTER(CHOOSECOLS(Table1,2,4),Table1[Fruit]=F2),SUM(f)/ROWS(f))
 
Upvote 0
Welcome to the MrExcel board!


Is this what you want then?

23 07 09.xlsm
ABCDEFG
1FruitAmt1Amt2Amt3
2Apple123Apple13.33333333
3Pear245
4Apple367
5Banana4811
6Cherry51013
7Banana61217
8Apple71419
Average
Cell Formulas
RangeFormula
G2G2=LET(f,FILTER(CHOOSECOLS(Table1,2,4),Table1[Fruit]=F2),SUM(f)/ROWS(f))

 
Upvote 0
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
 
Upvote 0
My data is the result of a Query and appears to be in a table as the Table Design button shows a table name.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top