Fix values with average

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,132
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
4Report DateDayMonthYearMonth-WeekYear-Week000102030405060708091011121314151617181920212223
5########MonOct2024342288124772037167314461458170717091685171218151993231528373010288828972914304332583383349834883258
6########TueOct2024342287524852003166214411479173417171708146712572054220029233060276528952969299232853443381535593426
7########WedOct2024342307025872015165913501467161218271719193520132498266630113198311229853001310433083356349033003231
8########ThuOct2024342309228112365194116201475156116111772200622572507268630891911303730813005294331603189327732593192
9########FriOct202434211229572587220218181593156314561529175120732236251131123413333832803109331934503315344533843394
10########SatOct2024342320730522688228219101674161715001548173120582247256030922956306428193077309634203412368839453628
11########SunOct202444330812651223317891518152317571745171717251805195522642655261627452780286830783137341535663347962
12########MonOct2024443289925572098173414891477146517181590166317811937239429303124284529952960335639874025362037303020
13########TueOct202444333012878226918011860187520522073200319828182188261832073395320732123165341436953905397338133630
14########WedOct2024443323428432369197817121701195119251923187319412261274131053284317632433206332633913691378237173471
Sheet1
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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?
 
Upvote 0
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.



1729879565297.png
 
Upvote 0
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:

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
 
Upvote 0
Joe4 said:
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:

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
Click to expand...
@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
 
Upvote 0
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.
 
Upvote 0
It might by a too rough measure but how about using TRIMMEAN?
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Average Function
Replies
8
Views
365
Peter_SSs
Peter_SSs
I
  • Solved
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
846
Peter_SSs
Peter_SSs
D
  • Question Question
Automating selecting cells to create average
Replies
2
Views
555
Draff
D
montecarlo2012
  • Solved
vba=Loop through each row in the data range.(B2:F10000)
Replies
9
Views
2K
montecarlo2012
montecarlo2012
L
  • Solved
Using IF inside FILTER command to modify a value
Replies
1
Views
521
Ludwig
L

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top