This is not so much a charting problem as a number problem.
You can use a formula, as NBVC points out, to test each value against a min/max value. The real issue is how to decide what the min/max value should be.
I'm not understanding why you want to exclude 'bad' points.
I can see the large and small extremes cause spikes in your line but are they valid spikes? If not why are they in the data set and what causes them.
For information using NA() with regards to line charts simply removes the markers. The line will be interpolated between nearest 2 valid data points.
Hi Andy. No I understand its not a charting problem, but exactly a number problems. The spiking points are not valid, and its caused because of a bad dataprovider (I dont know if you have worked with financial data - sometimes you just simply get a wrong update, that cause bad data). But I think the only way I can get close to something usefull is the formula where I make a cut off some certain high numbers. One last question - there is not a way where I can calculate an average over a certain array but exclude the highest x-numbers? Then I think I better could do a trick with some IF formulas.
The workbook contains a formula that calculates an average based on a min/max range.
I have also included a line that misses data where if the maximum value within a 5 cell spread is greater than the average the point is excluded.
As you can see neither are really that good at smoothing the data.
If you search google for data smoothing you will find some very complex approaches for handling spikes in data sets.
Hi
Look at attached, I think I have found a solution - its not nice, but it might work. I havent tested it on different dataseries, so not sure if it fully works.
Sry - here is the attachment.
It may work with other data sets.
But I think you need an approach that is more statistically sound.
I don't know what that formula is but the 'internet' must. You can not be the only one having to deal with 'bad' data sets.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks