rounding of values to specific range values

Hello,

I need to round a calculated width of a steel bar to up next available size in my quoting spreadsheet...

The available sizes are 0.5", 0.75", 1", 1-1/2", 2", 3", 4", 5", 6"

If I had only 7 sizes I could use an IF statement which would be cumbersome but doable... or if the sizes were repeating by eg. 1/2" increments I could you =ROUND(A1/0.5,0)*0.5...

but how do you do it for this range - I don't know...

Pete

Hello Pete
Working from the sample data you've given, maybe something like the attached example might work for you. It uses VLOOKUP and the INDEX / MATCH functions. The lookup table has to be sorted high to low for the MATCH function to work with the (-1) Match Type.

Hope this helps.
DBY