I would like a formula to display the average from the filtered values in Excel. For example, I have a data with 4 columns. Column A-Product, Column B-Week, Column C-number of unit tested and Column D-Yield. In column A, I have hundreds of product that could belongs to several group. As of now, I have created another column Average showing the average yield value for each product by using the AVERAGEIF function "=AVERAGEIF([Product],[@[Product]],[Yield])".I manage to get the average yield value for each products.
But now, I want to randomly group few products and see the average value. The group should not be pre-determined. For example, I want to select X3000, A and AL using the filter function. The formula should give me the average for this three products which is 0.6786 not the average of individual product. The same formula should work if I want to select only product X3020 and A by using the filter function in Excel.
This is the example of data that I'm working on right now.
PRODUCT|WEEK|#TESTED|YIELD|AVERAGE
X3000|WEEK-12|2|100%|1
X3000|WEEK-13|1|100%|1
X3020|WEEK-6|7|71.43%|0.7712
X3020|WEEK-10|11|82.81%|0.7712
A|WEEK-12|7|0%|0.6667
A|WEEK-13|2|100%|0.6667
A|WEEK-14|1|100%|0.6667
AL|WEEK-7|16|75%|0.3750
AL|WEEK-9|2|0%|0.3750