The best group of cells within specific value

[soreno]

Hello forum,
I'm trying to find the best position from about two months recording of GPS coordinates with mobile phones (so the file is huge)
What I am trying to do is to pick out the records (column D) with the best group of cells within 100 meters based on ItemID (column C) just to get rid of large errors in GPS positions. I have sorted column C and D in ascending order and from this try to find the group of values that fit best for the 100 meter value.
I tried this formula

Please Login or Register  to view this content.

but this works only if the first value always is correct and that is not always the case as the GPS signal can drift a lot (bad weather, signal-shadow, battery status, etc). The distance in meters is calculated from where the point is supposed to be and with this data I'm trying to see if I need to update with better coordinates later, using some sort of mean or median number. There can be big drifts within each itemID, it can be only a few that is within 100 meters, perhaps when there is a very few (< 10) in that case if possible extend the value to 200 meter at is most or else all from this ItemID should have errors and be ignored in my next step. I hope I explained well enough, the attached file is sample from the data I am using and the validation column (column E) is from the code above I been using that does not work for my purpose.

Thanks

[AlphaFrog]

To start, you could use this simplified formula to replace your existing formula

Please Login or Register  to view this content.

Aside from that, I think you are just asking to calculate the distance from the AverageIf value...

Excel 2003 or earlier..

Please Login or Register  to view this content.

Excel 2007 or later...

Please Login or Register  to view this content.

[soreno]

Tried both formulas (2003 and 2007), it gives the wrong result if you check row 134 to 164 where row 164 is "out of range" but the rest are within the range of 100 meters (20,6 lowest and 43,4 the highest). I get "ERROR" for all with ItemsID of 135548, only one is correct and that is the last one in that series, the rest should be value "OK"...

[AlphaFrog]

Try a median if ...

Confirm with Ctrl+Shift+Enter then copy down the column...
=IF(ABS(D2-MEDIAN(IF($C$2:$C$864=C2,$D$2:$D$864,"")))>100,"ERROR","OK")

It's not perfect. I'm not a mathematician. That's about as good as I can get.

[soreno]

Thanks, its not perfect but much better than what I have tried , it does cross over the range of 100 meters... guess I have to accept that. I will try on my large setup to see what happens first...

[soreno]

Using the formula kills the application, I guess it take up too much memory and you have an never ending spinning wheel.
Could vba solve this? Or I have to split the file in several smaller versions for a fix...