Formula to return the highest value of a serie without considering abnormal values

[onche414]

Hello,

I have a quite good knowledge of Excel but I am struggling with a formula and maybe some expert here can help me.

I have a list of costs per months (700 lines) and I am trying to find a formula which will return the highest value of the series (spread in months) but without considering "abnormal values".
As an example:

I highlighted in yellow what would be the "abnormal" values in this example.

So for the first line the formula would give me 2.862, the second line 3.156, the third line 3.098 etc

Thank you for your help !

 

[arthurbr]

Could you please define the criteria for "abnormal values"? Thx

 

[onche414]

Could you please define the criteria for "abnormal values"? Thx

Hello,

That would be the amount that is way above or way below the others amounts in the serie

 

[saboh12617]

Hello,

I think you can use the STDEV.S function in combination with the AVERAGE, to measure the deviation on your samples.

A solution would be for each line to calculate both the STDEV.S and the AVERAGE, then compute for each value:
IF(ABS(val - AVERAGE) <= STEDV.S; take into account; out of bounds)


I think you can adapt to your sheet the formulas, as you did not provide an example.

Using Excel365 you have the MAXIFS formula, in which you can put the ABS(val - AVERAGE) <= STEDV.S argument.
Otherwise you would probably need to use SUMPRODUCT .

 

[saboh12617]

Hum, i come back to you as i am not satisfied with the previous answer i gave you. It took for granted that for each serie there would have been a value out of the boulds to exclude. But in this case it was easier to just take the second maximum.

Another approach is to use the AVERAGEIFS function.
First you measure the average of your serie, without the maximum, ie. AVERAGEIFS(serie, "<" & MAX(serie), serie) " refered after as calcAVRG
Then you measure, for each value, its distance to this average, ie. ABS(val-calcAVRG) " refered as dist
Then you measure the average distance, without the maximum, ie AVERAGEIFS(serie_dist, "<" & MAXserie_dist), serie_dist) " refered after as distAVRG
You can then multiply this distAVRG by a coeficient. I found out 3 is a good one, probably because it's close to Pi. Doing so allows you to find a treshold, above which values are considered out of bounds.
ie. treshold = distAVRG * 3

And finally you compare, for each value, the distance calculated to this treshold.

I agree the steps are a bit long and tedious, probably there is a better way, but at least it manages to completely step away from the values and only look at gaps between them. It absorbs the gaps until a certain point, allowing you to only detect values "out of the range" if they exist. And otherwise let your serie untouched. I tried with series of 6 values with average value between 2 and 100 and the results were consistent.

 

[saboh12617]

Examples below :

							averages without the max
values	6,3​	5,8​	5,7​	4,9​	6,5​	6​	5,74​
dist	0,56​	0,06​	0,04​	0,84​	0,76​	0,26​	0,336​
aveg,d*p	1,008​	1,008​	1,008​	1,008​	1,008​	1,008​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​
							averages without the max
values	10​	7,3​	8,1​	7,6​	7,2​	7,9​	7,62​
dist	2,38​	0,32​	0,48​	0,02​	0,42​	0,28​	0,304​
aveg,d*p	0,912​	0,912​	0,912​	0,912​	0,912​	0,912​
acceptability	FALSE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​
							averages without the max
values	1,2​	1,1​	1,3​	1​	1,6​	2,9​	1,24​
dist	0,04​	0,14​	0,06​	0,24​	0,36​	1,66​	0,168​
aveg,d*p	0,504​	0,504​	0,504​	0,504​	0,504​	0,504​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	FALSE​
							averages without the max
values	50​	60​	70​	55​	75​	88​	62​
dist	12​	2​	8​	7​	13​	26​	8,4​
aveg,d*p	25,2​	25,2​	25,2​	25,2​	25,2​	25,2​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	FALSE​
							averages without the max
values	50​	60​	70​	55​	75​	80​	62​
dist	12​	2​	8​	7​	13​	18​	8,4​
aveg,d*p	25,2​	25,2​	25,2​	25,2​	25,2​	25,2​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​

 

[onche414]

Examples below :

							averages without the max
values	6,3​	5,8​	5,7​	4,9​	6,5​	6​	5,74​
dist	0,56​	0,06​	0,04​	0,84​	0,76​	0,26​	0,336​
aveg,d*p	1,008​	1,008​	1,008​	1,008​	1,008​	1,008​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​
							averages without the max
values	10​	7,3​	8,1​	7,6​	7,2​	7,9​	7,62​
dist	2,38​	0,32​	0,48​	0,02​	0,42​	0,28​	0,304​
aveg,d*p	0,912​	0,912​	0,912​	0,912​	0,912​	0,912​
acceptability	FALSE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​
							averages without the max
values	1,2​	1,1​	1,3​	1​	1,6​	2,9​	1,24​
dist	0,04​	0,14​	0,06​	0,24​	0,36​	1,66​	0,168​
aveg,d*p	0,504​	0,504​	0,504​	0,504​	0,504​	0,504​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	FALSE​
							averages without the max
values	50​	60​	70​	55​	75​	88​	62​
dist	12​	2​	8​	7​	13​	26​	8,4​
aveg,d*p	25,2​	25,2​	25,2​	25,2​	25,2​	25,2​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	FALSE​
							averages without the max
values	50​	60​	70​	55​	75​	80​	62​
dist	12​	2​	8​	7​	13​	18​	8,4​
aveg,d*p	25,2​	25,2​	25,2​	25,2​	25,2​	25,2​
acceptability	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​

Hello,

Thank you for your help, your first method with the STDEV worked really good so I am going with it !

However, just out of curiosity, I tried your second method and I came up with :

Now I guess what should I do is to exclude each amount (from dist) above this treshold right ?

 

[saboh12617]

Hello,

Thank you for your help, your first method with the STDEV worked really good so I am going with it !

However, just out of curiosity, I tried your second method and I came up with :

View attachment 114229

Now I guess what should I do is to exclude each amount (from dist) above this treshold right ?

Yes exactly, in your case it would be :

in AH :

=MAX(IF(AG1>Y1:AD1;D1:I1;0))

Which is an arrayformula, so enter with CTRL+SHIFT+ENTER, and "1" to adapt to your row number of course.
As i was saying, my 1st answer is fine as long as you know you will always have 1 value out of bounds for each list. If not, this solution should be more robust.

 

[onche414]

Yes exactly, in your case it would be :

in AH :

=MAX(IF(AG1>Y1:AD1;D1:I1;0))

Which is an arrayformula, so enter with CTRL+SHIFT+ENTER, and "1" to adapt to your row number of course.
As i was saying, my 1st answer is fine as long as you know you will always have 1 value out of bounds for each list. If not, this solution should be more robust.

Thank you very much !