How to select stock sizes from user input

[sjb72]

Hi!

First time poster here. I use Excel version 14 on Microsoft Office Professional 2010.

I'd like the spreadsheet to automatically select an appropriate material blank size based on the maximum diameter inputted by the user. The stock size needs to be a minimum of 0.5mm larger than the max diameter. There is a long list of sizes, but rather unhelpfully they don't go up in equal increments. If you're interested, the sizes are 80 90 100 115 120 130 140 150 160 170 180 200 220 240.

I'm guessing I need to use IF and THEN type of logic, but I don't have experience with using it on Excel (and I have never used macros or visual basic). How should I go about doing this?

The next stage is to select the length. This is a bit more straight forward, but it will still require me to use a command that I don't know the name of.

The blank length is based on the max width of the part. The width needs to be rounded up to the nearest whole millimeter, then 4 mm gets added on. So 12.7 becomes 17, 11.1 becomes 16 and 18.0 becomes 22. How do I go about doing this one?

Many thanks in advance,

Stephen

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? Nobody wants to have to create your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Glenn Kennedy]

For the diameters:
=INDEX($G$2:$G$15,MATCH(A2+0.5,$G$2:$G$15,1)+1)

For the lengths:
=ROUNDUP(A5,0)+4

see sheet for context.

[sjb72]

Ok, here goes!

[AliGW]

Have a look at Glenn's suggestion in post #3.

[sjb72]

Thanks for the method, Glenn!

How do the two commands work? I can sort of see how the first one works, but am unsure what the +1 at the end is needed for

[Glenn Kennedy]

easy one first:

=ROUNDUP(A5,0)+4 - round A5 up to zero decimal places and add 4.

=INDEX($G$2:$G$15,MATCH(A2+0.5,$G$2:$G$15,1)+1)

MATCH returns a row number that corresponds to the largest value that is less than or equal to A2+0.5. The values in the list must be placed in ascending order. the +1 means then go to the next value down the list. INDEX returns the corresponding value, from the row number.



You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.