Telling if Value is within or out Limits

Hello all,

I'v got the following data:

WEIGHT INDEX
160000 43
360000 34
396893 43

Plotted in a graph it looks like this:
Afbeelding 7.jpg

How can I tell if the following values are within the limits (right side of the line) or not.

WEIGHT INDEX
383057 39

On the chart it is obvious but I'd like a true or false answer in a cell

Jonathan78,

Attached is an example workbook based on the criteria you described.
It uses basic algebra for linear equations (y=mx+b where y is Weight, x is Index, m is Slope, and b is Y-Intercept) to reach the True/False conclusion.

In column A is the weight starting in A3 and in column B is the Index starting in B3. It is important to have column A and B sorted by column A ascending (as shown in your post). In E2 is the weight you want to test and in E3 is the Index you want to test.

The first thing that happens is the formula in H3 determines if the limits provided in columns A and B are sufficient to calculate the result. If the limits are not sufficient (for example, a weight is provided that exceeds the maximum weight listed in column A), the calculation won't take place because there is not enough information to provide an accurate answer.

If it is determined that the calculation can happen, cell H3 calculates the appropriate slope with this formula:

=IF(COUNT(E2:E3)<2,"Not enough data",IF(OR(E2>MAX(A3:A5),E2<MIN(A3:A5)),"Data out of range",(INDEX(A3:A5,MATCH(E2,A3:A5)+1)-INDEX(A3:A5,MATCH(E2,A3:A5)))/(INDEX(B3:B5,MATCH(E2,A3:A5)+1)-INDEX(B3:B5,MATCH(E2,A3:A5)))))

Then cell H3 calculates the appropriate Y-Intercept with this formula:

=IF(COUNT(H2)=0,"",INDEX(A3:A5,MATCH(E2,A3:A5))-H2*INDEX(B3:B5,MATCH(E2,A3:A5)))

Now that the helper cells are done, we can find out if the provided weight and index are within the limit with this formula:

=IF(H2="Not enough data","",IF(H2="Data out of range","Invalid Weight and/or Index (Data out of range)",E3>=(E2-H3)/H2))

Using your provided data, the answer is FALSE. Is something like that what you're looking for?