In the bank we are using the average price from six vendors. But we now and then entcounter wrong prices due to the fact that one or more vendors some time publish an incorrect price and this affects the average price. I seek inspiration/suggestions to most effecient/correct way and which formula/logic to use to exclude these price outliers. Here below you see an example of the problem. The prices in red text are considered outliers.

Remaining challenge when only three vendors publish a price and when one of these is a outlier. How do I remove/exclude this outlier? Note: SAND=True and FALSK= False.

Hope someone can help us out. Many thanks in advance ;-)

Kind regards

Soren Sig Mikkelsen

CodePudding user response：

Solution1:

Easiest of way to remove is via defining the inter quartile range...

In below screenshot you can see the formulas with calculated values...

Logic is very simple, calculate the inter quartile range of your data & see if any of the 6 value is outside the upper/lower limit, If so then it's outlier...

Solution2:

Using Average & Std Deviation