admin管理员组

文章数量:1028034

I have seen there are already a few questions on Stack that ask questions similarly but not in this particular way....

I am looking to for an Index match match that is able to return an array so I can average it.

In a perfect world I could use an index match match for concrete and 1/1/2024 and it would return me an array of 171 and 231 of which I could grab the average of those two....

I would like to stay away from VBA and because I have such a large data set would love a formula that is efficient. I have tried some other array formulas found here:

You will be a legend in the office if you can figure it out. It seems simple but looks can be deceiving.

Also the data can not be changed or manipulated. These two sit on different sheets.

DATA

Concrete Concrete METALS METALS
DATE Cast-In-Place Concrete Concrete (Ready-Mix) Iron and Steel Stairs, Staircases, Fire Escapes, Gates, Fences
1/1/2024 171 231 360 362
2/1/2024 171 232 362 365
3/1/2024 172 234 365 369
4/1/2024 173 234 365 368
5/1/2024 174 235 367 370
6/1/2024 175 237 369 374
7/1/2024 175 239 373 378
8/1/2024 175 239 373 379
9/1/2024 176 240 375 380
10/1/2024 177 239 373 375
11/1/2024 178 244 379 384
12/1/2024 178 244 379 384

I have seen there are already a few questions on Stack that ask questions similarly but not in this particular way....

I am looking to for an Index match match that is able to return an array so I can average it.

In a perfect world I could use an index match match for concrete and 1/1/2024 and it would return me an array of 171 and 231 of which I could grab the average of those two....

I would like to stay away from VBA and because I have such a large data set would love a formula that is efficient. I have tried some other array formulas found here: https://exceljet/formulas/return-array-with-index-function

You will be a legend in the office if you can figure it out. It seems simple but looks can be deceiving.

Also the data can not be changed or manipulated. These two sit on different sheets.

DATA

Concrete Concrete METALS METALS
DATE Cast-In-Place Concrete Concrete (Ready-Mix) Iron and Steel Stairs, Staircases, Fire Escapes, Gates, Fences
1/1/2024 171 231 360 362
2/1/2024 171 232 362 365
3/1/2024 172 234 365 369
4/1/2024 173 234 365 368
5/1/2024 174 235 367 370
6/1/2024 175 237 369 374
7/1/2024 175 239 373 378
8/1/2024 175 239 373 379
9/1/2024 176 240 375 380
10/1/2024 177 239 373 375
11/1/2024 178 244 379 384
12/1/2024 178 244 379 384

AVERAGE DATA

DATE Concrete METALS
1/1/2024
2/1/2024
3/1/2024
4/1/2024
5/1/2024
6/1/2024
7/1/2024
8/1/2024
9/1/2024
10/1/2024
11/1/2024
12/1/2024
  • arrays
  • excel
  • indexing
  • excel-formula
Share Improve this question edited Mar 19 at 20:38 Mayukh Bhattacharya 27.8k9 gold badges29 silver badges42 bronze badges asked Mar 19 at 20:23 analystanalyst 91 silver badge4 bronze badges 2
  • 1 Sumifs divided by countifs? – P.b Commented Mar 19 at 20:30
  • There is an AVERAGEIFS function, as well. – tinazmu Commented Mar 19 at 22:08
Add a comment  | 

1 Answer 1

Reset to default 3

Use FILTER instead for the column choice:

=AVERAGE(FILTER(INDEX(Sheet1!$A:$E,MATCH($A2,Sheet1!$A:$A,0),),Sheet1!$A$1:$E$1=B$1))

Sheet1

Sheet2

I have seen there are already a few questions on Stack that ask questions similarly but not in this particular way....

I am looking to for an Index match match that is able to return an array so I can average it.

In a perfect world I could use an index match match for concrete and 1/1/2024 and it would return me an array of 171 and 231 of which I could grab the average of those two....

I would like to stay away from VBA and because I have such a large data set would love a formula that is efficient. I have tried some other array formulas found here:

You will be a legend in the office if you can figure it out. It seems simple but looks can be deceiving.

Also the data can not be changed or manipulated. These two sit on different sheets.

DATA

Concrete Concrete METALS METALS
DATE Cast-In-Place Concrete Concrete (Ready-Mix) Iron and Steel Stairs, Staircases, Fire Escapes, Gates, Fences
1/1/2024 171 231 360 362
2/1/2024 171 232 362 365
3/1/2024 172 234 365 369
4/1/2024 173 234 365 368
5/1/2024 174 235 367 370
6/1/2024 175 237 369 374
7/1/2024 175 239 373 378
8/1/2024 175 239 373 379
9/1/2024 176 240 375 380
10/1/2024 177 239 373 375
11/1/2024 178 244 379 384
12/1/2024 178 244 379 384

I have seen there are already a few questions on Stack that ask questions similarly but not in this particular way....

I am looking to for an Index match match that is able to return an array so I can average it.

In a perfect world I could use an index match match for concrete and 1/1/2024 and it would return me an array of 171 and 231 of which I could grab the average of those two....

I would like to stay away from VBA and because I have such a large data set would love a formula that is efficient. I have tried some other array formulas found here: https://exceljet/formulas/return-array-with-index-function

You will be a legend in the office if you can figure it out. It seems simple but looks can be deceiving.

Also the data can not be changed or manipulated. These two sit on different sheets.

DATA

Concrete Concrete METALS METALS
DATE Cast-In-Place Concrete Concrete (Ready-Mix) Iron and Steel Stairs, Staircases, Fire Escapes, Gates, Fences
1/1/2024 171 231 360 362
2/1/2024 171 232 362 365
3/1/2024 172 234 365 369
4/1/2024 173 234 365 368
5/1/2024 174 235 367 370
6/1/2024 175 237 369 374
7/1/2024 175 239 373 378
8/1/2024 175 239 373 379
9/1/2024 176 240 375 380
10/1/2024 177 239 373 375
11/1/2024 178 244 379 384
12/1/2024 178 244 379 384

AVERAGE DATA

DATE Concrete METALS
1/1/2024
2/1/2024
3/1/2024
4/1/2024
5/1/2024
6/1/2024
7/1/2024
8/1/2024
9/1/2024
10/1/2024
11/1/2024
12/1/2024
  • arrays
  • excel
  • indexing
  • excel-formula
Share Improve this question edited Mar 19 at 20:38 Mayukh Bhattacharya 27.8k9 gold badges29 silver badges42 bronze badges asked Mar 19 at 20:23 analystanalyst 91 silver badge4 bronze badges 2
  • 1 Sumifs divided by countifs? – P.b Commented Mar 19 at 20:30
  • There is an AVERAGEIFS function, as well. – tinazmu Commented Mar 19 at 22:08
Add a comment  | 

1 Answer 1

Reset to default 3

Use FILTER instead for the column choice:

=AVERAGE(FILTER(INDEX(Sheet1!$A:$E,MATCH($A2,Sheet1!$A:$A,0),),Sheet1!$A$1:$E$1=B$1))

Sheet1

Sheet2

本文标签：

版权声明：本文标题：excel - Is there an Index, Match, Match formula that can return an array so I can take the average of that array? - Stack Overfl 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1744433185a2096795.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。