Round to numbers in a list

  • Thread starter Thread starter Jay3253
  • Start date Start date
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
 
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.
 
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))
 
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)))
 
Back
Top