Compute average prices of distinct values in a column

Question

I have an excel dataset like this:

item	price_dollar_kg
apple	1
apple	1.5
apple	1.8
apple	1.3
banana	30
banana	1.8
banana	2
banana	143
banana	143

But then with about 30,000 entries for about 100 food items. For each item, I want to compute the average price without the outliers (3*IQR). Is there a way in which I can let excel automaticly place the functions I need everytime entries for another food item start in column "item" and let the cell references in these functions adapt to this? Or is there any other way I could easily compute the prices I need?

Answer 1

I wasn't sure what you meant by 3 * IQR, so the solutions below assume the range is 1Q - IQR and 3Q + IQR for a total span of 3 * IQR.

The first step is the covert you data to a table. In my example, it is call Table2.

If you have a version of Excel Office 365 that supports LET and dynamic arrays then you can enter =UNIQUE(Table2[Item]) into D2 to get the list of unique items.

Then in E2 enter the following.

=LET(itemList,Table2[Item],
     priceList,Table2[price_dollar_kg],
     item,D2,
     quartiles,QUARTILE(FILTER(priceList,itemList=item),{1,3}),
     IQR,SUM({-1,1}*quartiles),
     avgRange,quartiles+{-1,1}*IQR,avgNoOut,AVERAGEIFS(priceList,itemList,item,priceList,">="&INDEX(avgRange,1),priceList,"<="&INDEX(avgRange,2)),
     IF(item="","",avgNoOut))

Then copy E2 down to E3:E201. This will allow up to 200 unique items. I couldn't figure out a way to get the formula to spill automatically with the length of the unique list so I made this compromise.

The results looks like this.

If you version of Excel doesn't support LET or dynamic arrays, you can do the following.

Set Cell D2 = A2.

Set Cell D3 to =IFERROR(INDEX(Table24[Item],MATCH(0,COUNTIF($D$2:D2,Table24[Item]),0)),""). Be sure to press Ctrl + Shift + Enter. This needs to be an array formula to work correctly. More info on this formula is here.

Then copy the formula down to E4:201. Again, this gives you room for 200 unique items.

Set Cell F2 to =IF($D2="","",QUARTILE(IF(Table24[Item]=$D2,Table24[price_dollar_kg],""),1)).

Set Cell G2 to =IF($D2="","",QUARTILE(IF(Table24[Item]=$D2,Table24[price_dollar_kg],""),3)).

Set H2 to =G2-F2; I2 to =F2-H2; J2 to =G2+H2.

Set E2 to =IF(D2="","",AVERAGEIFS(Table24[price_dollar_kg],Table24[Item],D2,Table24[price_dollar_kg],">="&I2,Table24[price_dollar_kg],"<="&J2)).

Copy E2:J2 to E3:J201. The final result looks like this.