SQLite Forum

Get column rank from values in selected row
Login
The general problem is "interpolation" and "extrapolation".

You need the two data points left and right of the given weight for the specified age. You then assume that the graph is a straight line in between the two data points and compute the percentile coordinate from the weight coordinate.

E.g. for age 3 and weight 8.75 you find (25,8) and (50,9); since 8.75 is 3/4 the way from 8 to 9, the result should be 3/4 of the way from 25 to 50 which is 43.75

In the case of an outlier, you would need to extrapolate using the two closest entries (which both lie on the same side of the given weight) and pretend the graph is a straight line there too.

E.g. for age 3 and weight 11.25 you find (75,10) and (90,11); since 11.25 is 1/4 past 11, the result should be 1/4 the difference between 75 and 90 higher, yielding 93.75.

This would be "linear interpolation" and "linear extrapolation". Adding more data points improves the accuracy of the result. Or you can take advantage of the fact that you are dealing with statistics, which means that the age/weight distribution follows the classical "bell curve". The difference is that it makes the math of interpolation more difficult, but yields better results for a smaller number of input points (median and standard deviation suffice).