Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A math/algorithm puzzle!
Hi all...
Using VB in a appplication that needs to solve a bit of a math/algorithm problem, and thought the good people here would have interesting ideas on how to solve it... Without going into too much detail, basically the code must find/suggest the best pairs of numbers from a single list, and be able to exclude "outliers". Pairing would be the best pairings possible by closest value. Example: data: 25 22 31 24 25 22 28 18 23 I would want to be able to adjust the sensitivity of the code to start to trim outliers from the data, like "18" would seem to be the biggest troublemaker in creating similar pairs... resulting in a list like this: 31 28 25 25 24 23 22 22 excluded: 18 The largest delta is three in this case... but you can see how the logic/sorting might become complex, especially when the list of data becomes much larger. Thoughts? Thx, SMS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A math/algorithm puzzle!
Why not just have VBA sort your data and then compare the differenc between teh first and second numbers with the difference between th last and second to last numbers in your list? This would identify you greatest outlier and it could be handled accordingly -- bhofset ----------------------------------------------------------------------- bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880 View this thread: http://www.excelforum.com/showthread.php?threadid=38069 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A math/algorithm puzzle!
rci
At first glance... Why not work out the mean and standard deviation of your numbers and say... a) exclude any number greater or less than 3 s.d.'s (as outliers) b) try pairing numbers by their distance above and below the mean e.g check for numbers within +/- 0.5 sd of mean and pair them, then look at numbers +/- 0.51 to 1sd from mean, then 1.01 to 1.5sd etc. Just a preliminary thought... Alex "rci" wrote: Hi all... Using VB in a appplication that needs to solve a bit of a math/algorithm problem, and thought the good people here would have interesting ideas on how to solve it... Without going into too much detail, basically the code must find/suggest the best pairs of numbers from a single list, and be able to exclude "outliers". Pairing would be the best pairings possible by closest value. Example: data: 25 22 31 24 25 22 28 18 23 I would want to be able to adjust the sensitivity of the code to start to trim outliers from the data, like "18" would seem to be the biggest troublemaker in creating similar pairs... resulting in a list like this: 31 28 25 25 24 23 22 22 excluded: 18 The largest delta is three in this case... but you can see how the logic/sorting might become complex, especially when the list of data becomes much larger. Thoughts? Thx, SMS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A math/algorithm puzzle!
Another suggestion:
FIrst sort the numbers. This will put close numbers next to each other. Then calculate the differences, for example A1 to A2, A2 to A3, etc. and select the smallest differences for the pairs. Outliers would have large differences from both their neighbors. -- Gary's Student "Alex" wrote: rci At first glance... Why not work out the mean and standard deviation of your numbers and say... a) exclude any number greater or less than 3 s.d.'s (as outliers) b) try pairing numbers by their distance above and below the mean e.g check for numbers within +/- 0.5 sd of mean and pair them, then look at numbers +/- 0.51 to 1sd from mean, then 1.01 to 1.5sd etc. Just a preliminary thought... Alex "rci" wrote: Hi all... Using VB in a appplication that needs to solve a bit of a math/algorithm problem, and thought the good people here would have interesting ideas on how to solve it... Without going into too much detail, basically the code must find/suggest the best pairs of numbers from a single list, and be able to exclude "outliers". Pairing would be the best pairings possible by closest value. Example: data: 25 22 31 24 25 22 28 18 23 I would want to be able to adjust the sensitivity of the code to start to trim outliers from the data, like "18" would seem to be the biggest troublemaker in creating similar pairs... resulting in a list like this: 31 28 25 25 24 23 22 22 excluded: 18 The largest delta is three in this case... but you can see how the logic/sorting might become complex, especially when the list of data becomes much larger. Thoughts? Thx, SMS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Algorithm Challenge | Excel Worksheet Functions | |||
Can you help!!!!! New Puzzle | Excel Discussion (Misc queries) | |||
algorithm creation | Excel Programming | |||
help with algorithm | Excel Programming | |||
Need help with algorithm | Excel Programming |