Round to numbers in a list

J

Jay3253

I have an quation that gives me a number for the size of a steel rod.
The steel rods we use are only certain sizes so not just any size wil
do. The rod size I get in this equation I would like it to look at
list of the correct rod sizes and round up to the next size on the lis
we use.

NOTE: The sizes we use are not uniform so there is NOT an equa
distance between rod sizes.

Thank you
Jaso
 
K

Ken Wright

Use INDEX and MATCH. Assuming your list of sizes is in A1:A10, sorted in
DESCENDING order, ie with the largest figure in A1 and smallest in A10, and
assuming the value you were looking up is in say C1:-

=INDEX(A1:A10,MATCH(C1,A1:A10,-1))

If you have a formula that returns the value you want to look up, you can always
combine it with this, ie

=INDEX(A1:A10,MATCH(Your_Formula,A1:A10,-1))

What the formula does is to find the smallest value that is *greater* than or
equal to the value being looked up, hence it will give you the minimum size of
pipe you need to be able to get the job done.
 
F

Frank Kabel

Hi
maybe this will help you:
Assumptions:
- column A of your list stores the rod-size values
- the column is sorted descending!!
- cell B1 stores the size to lookup

=INDEX(A1:A100,MATCH(B1,A1:A100,-1))
 
F

Frank Kabel

Hi
think you mean ascending? Two ways:
1. Resort the list :)
2. A little bit kludgy but give it a try

=IF(ISNA(MATCH(B1,A1:A100,0)),INDEX(A1:A100,MATCH(B1;A1:A100,1)+1),INDE
X(A1:A100,MATCH(B1,A1:A100,0)))
 
Top