Weighted Avg excluding outliers

[MattTom]

Greetings,
I have two columns of data:
A) Stock P/E
B) Port Weight

The data is susceptible to outliers so I'm using TRIMMEAN to calculate the average P/E. Now I'd like to calculate a weighted average that also excludes outlier values. Assuming I can't simply delete the outliers from the data set, is it possible to arrive at a weighted average using TRIMMEAN or some other excel function?

Thanks!

 

[Fazza]

What about this approach? Note this is not my field of expertise. I've kept it simple but maybe you could improve it if it works.

one cell with the % to exclude at each end. Like 5%. Then formulas
=PERCENTILE.INC(the 5% cell, the range of stock P/E values)
=PERCENTILE.INC(1 - the 5% cell, the range of stock p/E values)

now add a helper column
=AND(p/e cell > the first new formula, p/e cell < the second new formula)*1
this will give 0 for the data to exclude & 1 for those to include

now sumproduct for the result,
=SUMPRODUCT(all three columns)/SUMPRODUCT(port column & new column)

HTH