Smoothing Data

[rcarmichael]

Good evening,
I am trying to remove outliers from a list of values, replacing them with a smooth transition from those values which are not outliers.
Is there an Excel Formula or Power Query method I can use to achieve this?
Example:

Original Value	Smoothed Result (apx.)
28	28
30	30
27	27
29	29
115	31
32	32
40	40
119	38
132	39
37	37
27	27
30	30

Any help is greatly appreciated.
Sincere regards,

 

[Cubist]

This almost gives you what you want in 365.

Book1
	A	B
1
2	28	28
3	30	30
4	27	27
5	29	29
6	115	30.5
7	32	32
8	40	40
9	119	38.5
10	132	38.5
11	37	37
12	27	27
13	30	30
Sheet3

Cell Formulas
Range	Formula
B2:B13	B2	=LET( n,$A$2:$A$13,m,A2,x,XMATCH(m,n), out,1.5*(QUARTILE.INC(n, 3)-QUARTILE.INC(n, 1)), t,TAKE(n,x-1),a,XLOOKUP(TRUE,t<out,t,,,-1), d,DROP(n,x),b,XLOOKUP(TRUE,d<out,d), IF(m>out,AVERAGE(a,b),m) )

 

[rcarmichael]

Thank you @Cubist

 

[Cubist]

You're welcome. Minot changes and this gives you exactly what is shown in the OP.

Book1
	A	B
2	28	28
3	30	30
4	27	27
5	29	29
6	115	31
7	32	32
8	40	40
9	119	39
10	132	38
11	37	37
12	27	27
13	30	30
Sheet3

Cell Formulas
Range	Formula
B2:B13	B2	=LET( n,$A$2:$A$13,m,A2,a,B1, out,1.5*(QUARTILE.INC(n, 3)-QUARTILE.INC(n, 1)), d,DROP(n,XMATCH(m,n)),b,XLOOKUP(TRUE,d<out,d), IF(m>out,ROUND(AVERAGE(a,b),0),m) )