manueltnascimento
New Member
- Joined
- Oct 26, 2016
- Messages
- 3
Hi guys, first post ever
I need to calculate the average of a range of values depending on criteria (thus using AVERAGEIF or similar).
The thing is that the 'criteria_range' isn't next to the 'average_range' and I can't make it work with an INDEX&MATCH inside the AVERAGEIF formula.
»» I need to calculate the average value of a list of values depending on the product family, but I don't have the product family on the main table (i.e. where I do have the values).
»» Imagine I want to find the average for the Family A products of the Sheet1 Table below. How to do it without bringing the Family column to the main table?
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1: SKU_column[/TD]
[TD]...[/TD]
[TD]E1: Values_column[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]...[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]...[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]...[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]...[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1: SKU_column[/TD]
[TD]B1: Prod_Family[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Family A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Family B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Family A[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Family C[/TD]
[/TR]
</tbody>[/TABLE]
Thanks a lot for your time and sorry if I'm sounding confused!
Manuel
I need to calculate the average of a range of values depending on criteria (thus using AVERAGEIF or similar).
The thing is that the 'criteria_range' isn't next to the 'average_range' and I can't make it work with an INDEX&MATCH inside the AVERAGEIF formula.
»» I need to calculate the average value of a list of values depending on the product family, but I don't have the product family on the main table (i.e. where I do have the values).
»» Imagine I want to find the average for the Family A products of the Sheet1 Table below. How to do it without bringing the Family column to the main table?
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1: SKU_column[/TD]
[TD]...[/TD]
[TD]E1: Values_column[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]...[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]...[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]...[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]...[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1: SKU_column[/TD]
[TD]B1: Prod_Family[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Family A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Family B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Family A[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Family C[/TD]
[/TR]
</tbody>[/TABLE]
Thanks a lot for your time and sorry if I'm sounding confused!
Manuel