Exclude abnormality

**lpn** · 11-13-2008, 10:12 AM

Hi

I have a long set of data (like 1500 rows or more - its for example stock prices). In those data sometimes there is a bad dataprovider, so I get a very wrong price and therefore I cant make a proper graph of it. How can I somehow smooth that or exclude the bad data points (I dont wanna do it with the axis on the graph).

LPN

**NBVC** · 11-13-2008, 10:14 AM

One way is that you can overwrite the "bad" entries with the formula =NA() this will put the #N/A error in the cell and won't be plotted on the chart.

**JBeaucaire** · 11-13-2008, 10:15 AM

Is the bad data in one specific column? You could add another column to interpret that column and make corrections, supressing bad data altogether and then chart this helper column.

Or if the corruption is in a specific format, perhaps a macro to clean the data up prior to charting?

**Shijesh Kumar** · 11-13-2008, 10:16 AM

Hi,

You can use Data Validation to stop BAD data entry and you can get a smooth graph.

Click on
Data -- > Validation.

**lpn** · 11-13-2008, 10:38 AM

ok, I might not have been specific. The column where I have the data will change on an ongoing basis, so I need an automatic function - I cant just put in NA() manually.

**NBVC** · 11-13-2008, 10:42 AM

Then use a "helper" column with an If() statement that says, if the value is ok, then use it otherwise stick an #N/A.

e.g. =If(A1=condition_for_true,A1,NA())

enter your actual condition in the italisized part.

Then plot that helper column

**lpn** · 11-13-2008, 10:51 AM

I have tried this but I cant find a solution with an IF statement that works. Is there something called trim average (I mean an average where you might take out the top and bottom 25 %).

**NBVC** · 11-13-2008, 11:16 AM

There is a Large() and Small() function....

e.g. =Large(A1:A100,25) will tell you the 25th largest number and =Small(A1:A100,25) will tell you the 25th smallest number.

.. then you can use an IF() statement like =IF(OR(A1<Large($A$1:$A$100,25),A1>Small($A$1:$A$100,25)),A1,NA())

adjust ranges to suit and copy down

**lpn** · 11-13-2008, 11:23 AM

I have attached a data example. It should be very easy to see the bad data - pls. let me see if you can do something to it.

**NBVC** · 11-13-2008, 12:03 PM

Put this formula in D4 and copy it down the list...

Code:

=IF(OR(C4>=LARGE(C:C,25),C4<=SMALL(C:C,25)),NA(),C4)

You will see some #N/A's ....but maybe you need to increase that 25% marker because a lot of big ones still fall within...

**lpn** · 11-14-2008, 10:02 AM

Hmm it was a good try, but I cant get it to work properly. If you find any other ideas, pls. let me know.

**NBVC** · 11-14-2008, 10:05 AM

Can you show us how it did not work?

**lpn** · 11-14-2008, 10:16 AM

Look at attachment.

**NBVC** · 11-14-2008, 10:37 AM

Well, the #N/A's didn't get plotted as far as I can see...

And to get rid of the out-of-wack numbers, you may have to increase the 25 to 50 or higher..

e.g. =IF(OR(D3>=LARGE(D:D,50),D3<=SMALL(D:D,50)),NA(),D3)

or just pick a number out of the hat (like 500 as your max) and use a formula like:

=IF(D3>=500,NA(),D3) so that all the ones over 500 don't get plotted...

If these don't work... then it's a job for Andy Pope (our resident Chart Guru)

**lpn** · 11-14-2008, 11:00 AM

The problem is that the numbers change, so I cant make a fix that above 500, cause in other circumstances that should be above 1000 maybe, and in others again its 200.

Thread: Exclude abnormality

Thread Tools

Display

Exclude abnormality

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions