Figure out what you really want and then let me know. "We" should be able to
find the next largest or the next lowest(as my formula already does) but you
need to be clear about what you want!
OK, I have several columns of numbers. One column is torque values as I
tighten the fastener and thye do not go quite in ascending order. In other
words, the general trend is ever increasing until the bolt strips, but along
the way a torque value might be a little less than the one that proceeded it
and a value may be duplicated several times. In another colum is clamping
force values that correspond to the torque value in the adjacent Torque col.
What I am trying to accomplish: Obtain a given clamp load value for
predetermined torque value. So if a power tool was set to 12Nm for instance,
it would keep tightening the bolt, until 12Nm was reached. Since the exact
12 tq value that I am looking for may not be in the col., the actual tq value
might be a little larger than what I am looking for. I would really like to
be able to interpolate for the clamp load at exactly the tq. I am aiming for.
So interpolation between the clamp load values that correspond to the torque
values just past my aim point and the value in the prior (up 1) row.
However, typically even with out interpolation, obtaining the clampload that
corresponds to torque value just prior to, or past the desired value will
suffice. (past preferred)
Sample of data for A1:A10
1
7
8
9
10
11.9
12.1
14
15
16
B1:b10
In b1:b10 will be misc values, typically asending, so lets use 1 thru 10
With the lookup value in D1
List of numbers is in the range of A1:A10
Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER:
=IF(D1="","",IF(COUNTIF(A1:A10,D1),D1&"
exsists",MAX(IF(A1:A10<D1,A1:A10))))
If the lookup value is 12.0 and it's in the list the formula will return:
12.0 exsists
If 12.0 does not exsist the formula will return the the largest value that
is less than 12.0
Biff
I tried this, but if 12 did not exist, I got the largest (max) value in the
list. I did not put the formula into an array, hoiwever, I wasn't quaite
sure why or how to do.
Thanks for your help so far - already this is a big improvment over what I
had to do! At the very least, I can now search and see is the number I
desire is in the list. If it is, it is now no problem to get the value in
the next col. that corresponds to the aim value in the 1st col.