Fix values with average

[vmjan02]

I have this data and need to do the average only of the numbers which cells are colour in Yellow, these cells are the abnormal values as per the trend,

eg:- for Row 8 cell Y8 have an abnormal values to fix this it will take the average of X8 & Z8
eg:- for Row 9 cell K9 have an abnormal values to fix this it will take the average of L9 & M9
eg:- for Row 11 cell AH11 have an abnormal values to fix this it will take the average of AF11 & AG11
eg:- for Row 13 cell U13 have an abnormal values to fix this it will take the average of T13 & V13

As it is an hourly trend, i am not able to find the best way to fix it, as it's a very long hours of task, is there a better way to fix this. VBA will also do.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
4	Report Date	Day	Month	Year	Month-Week	Year-Week				00	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23
5	########	Mon	Oct	2024	3	42				2881	2477	2037	1673	1446	1458	1707	1709	1685	1712	1815	1993	2315	2837	3010	2888	2897	2914	3043	3258	3383	3498	3488	3258
6	########	Tue	Oct	2024	3	42				2875	2485	2003	1662	1441	1479	1734	1717	1708	1467	1257	2054	2200	2923	3060	2765	2895	2969	2992	3285	3443	3815	3559	3426
7	########	Wed	Oct	2024	3	42				3070	2587	2015	1659	1350	1467	1612	1827	1719	1935	2013	2498	2666	3011	3198	3112	2985	3001	3104	3308	3356	3490	3300	3231
8	########	Thu	Oct	2024	3	42				3092	2811	2365	1941	1620	1475	1561	1611	1772	2006	2257	2507	2686	3089	1911	3037	3081	3005	2943	3160	3189	3277	3259	3192
9	########	Fri	Oct	2024	3	42				112	2957	2587	2202	1818	1593	1563	1456	1529	1751	2073	2236	2511	3112	3413	3338	3280	3109	3319	3450	3315	3445	3384	3394
10	########	Sat	Oct	2024	3	42				3207	3052	2688	2282	1910	1674	1617	1500	1548	1731	2058	2247	2560	3092	2956	3064	2819	3077	3096	3420	3412	3688	3945	3628
11	########	Sun	Oct	2024	4	43				3081	2651	2233	1789	1518	1523	1757	1745	1717	1725	1805	1955	2264	2655	2616	2745	2780	2868	3078	3137	3415	3566	3347	962
12	########	Mon	Oct	2024	4	43				2899	2557	2098	1734	1489	1477	1465	1718	1590	1663	1781	1937	2394	2930	3124	2845	2995	2960	3356	3987	4025	3620	3730	3020
13	########	Tue	Oct	2024	4	43				3301	2878	2269	1801	1860	1875	2052	2073	2003	1982	818	2188	2618	3207	3395	3207	3212	3165	3414	3695	3905	3973	3813	3630
14	########	Wed	Oct	2024	4	43				3234	2843	2369	1978	1712	1701	1951	1925	1923	1873	1941	2261	2741	3105	3284	3176	3243	3206	3326	3391	3691	3782	3717	3471
Sheet1

 

[Joe4]

So, do you want to replace the numbers in yellow with the average from the readings to the left and right of it?

If so, there are a number of things you will need to define/clarify:

1. What is the rule/threshhold to determine if a value is "abnormal" or not?
I mean, just by looking at row 7, you can see that the numbers vary greatly as you go across.
So you need to define the conditions that must be met to consider a record to be abnormal, as we need something to program against!

2. Is it possible that the first or last value in your list could be an abnormal value?
If so, how is that determined and how is that to be corrected?

3. Is it possible to have two or more "abornmal" values in a row?
If so, how is that determined and how is that to be corrected?

 

[vmjan02]

1. What is the rule/threshhold to determine if a value is "abnormal" or not?
A:- It is the graph trend, ref the screen shoot.
> row 7 data is ok as per the graph it came down and slowly it is coming up
> row 8 data is ok till 13 hour but a hug drop on 14 hour and suddenly on 15 hour it jump up.

> row 12 data is ok as per the graph it came down and slowly it is coming up
> row 13 data is ok till 9 hour but a hug drop on 10 hour and suddenly on 11 hour it jump up.

2. Is it possible that the first or last value in your list could be an abnormal value?
A:- Yes it could be possible if the first record is abnormal, then take the average of next 2 cells and if last cell have abnormal values then take average to pervious 2 cells

3. Is it possible to have two or more "abnormal" values in a row?
A:- Yes it may be possible


I hope i am able to answer it as the best possible way, it will be great if there is way or solution to this.

 

[Joe4]

I really don't know how to quantify a "sharp" drop.
Excel typically deals in formulas, not pictures or words.

Perhaps you can use some of Excel's line "smoothing" features or process for finding "outliers".
I really don't work much with lines or charts, but there is a lot of information that can be found out there with Google searches, such as these:

https://real-statistics.com/time-series-analysis/basic-time-series-forecasting/simple-exponential-smoothing/#:~:text=Excel%20Data%20Analysis%20Tool%3A%20Excel,from%20the%20menu%20that%20appears.

Finding Outliers In Excel: A Detailed Guide - Acuity Training

This detailed guide to finding outliers in Excel includes several formulas and charts you can use. Check out our article and become an expert.

www.acuitytraining.co.uk

 

[vmjan02]

I really don't know how to quantify a "sharp" drop.
Excel typically deals in formulas, not pictures or words.

Perhaps you can use some of Excel's line "smoothing" features or process for finding "outliers".
I really don't work much with lines or charts, but there is a lot of information that can be found out there with Google searches, such as these:

https://real-statistics.com/time-series-analysis/basic-time-series-forecasting/simple-exponential-smoothing/#:~:text=Excel%20Data%20Analysis%20Tool%3A%20Excel,from%20the%20menu%20that%20appears.

Finding Outliers In Excel: A Detailed Guide - Acuity Training

This detailed guide to finding outliers in Excel includes several formulas and charts you can use. Check out our article and become an expert.

www.acuitytraining.co.uk

@Joe4
Its also my concern, as how to fix this, i will come back to this if any logical way, or any vba to get it fixed

 

[Joe4]

If you want us to do something via VBA, I think we need a formulaic way to determine exactly what constitutes a "sharp drop" - that needs to be quantified.
I am not sure if one of those other things I referenced or some other sort of statistical process can identify those for you without coming up with your own definition/rule for that.

 

[hagia_sofia]

It might by a too rough measure but how about using TRIMMEAN?

 

[vmjan02]

It might by a too rough measure but how about using TRIMMEAN?

what is it

 

[hagia_sofia]

TRIMMEAN

 

[vmjan02]

TRIMMEAN

some think with PERCENTILE, ok lets give it a try with .95