Round to numbers in a list
 

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

--
Message posted from http://www.ExcelForum.com

Round to numbers in a list
 

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jay3253 " wrote in message
...
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 will
do. The rod size I get in this equation I would like it to look at a
list of the correct rod sizes and round up to the next size on the list
we use.

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

Thank you
Jason


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.592 / Virus Database: 375 - Release Date: 18/02/2004

Round to numbers in a list
 

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))

--
Regards
Frank Kabel
Frankfurt, Germany

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
will
do. The rod size I get in this equation I would like it to look at a
list of the correct rod sizes and round up to the next size on the
list we use.

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

Thank you
Jason


---
Message posted from http://www.ExcelForum.com/

Round to numbers in a list
 

How do I do this if it is in ASENDAIANG order?


---
Message posted from http://www.ExcelForum.com/

Round to numbers in a list
 

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)))



--
Regards
Frank Kabel
Frankfurt, Germany

How do I do this if it is in ASENDAIANG order?


---
Message posted from http://www.ExcelForum.com/