Excel, Rounding Function Using a List

[ColtMaverick]

I have a spreadsheet setup that uses several lists to automatically generate answers based on the data entered. In specific, I am doing sheave calculations where the data 99% of the time does not give a sheave size that matches any size on the list, i.e. answers come with 2 decimal places whereas list only runs in sets of 6, 6.5, 7, 7.5, etc.

What I would like to know is how to set up a cell (Sheave Dia.) so that it calculates the answer and rounds it to the closest sheave size in a list that I put on a second sheet. I have listed an example below along with a brief list to get the idea of what I am trying to solve. The cell in question should be where the 10.33 is. This cell needs to calculate 10.33 and automatically round up or down to 10.5 or 10, respectively. My thoughts are that this will use nested IF statements along with some form of ROUND function.


Desired SPM 8
Reducer Ratio 29.04
Prime Mover RPM 1125
Unit Sheave dia. 50
Sheave dia. 10.33


2nd sheet List

Sheave Size
9
9.5
10
10.5
11

Thanks in advance for any help.

 

[PowerEngineer]

It sounds as if you are thinking about writing a macro to do the job, but (if I understand the problem correctly) it can also be accomplished with a few cell formulas.

Let's say you enter the calculated sheave diameter into cell A5, and the allowed sheave sizes (9, 9.5, 10, 10.5, 11) into cells A10:A14. Then if you put the following formula into B5:

=VLOOKUP(A5,A10:A14,1,TRUE)

you'll have the largest sheave size that is less than or equal to the calculated value. If you also want to find the next size up (i.e. the smallest sheave size that is greater than the calculated value) then put this formula into C5:

=INDEX(A10:A14,MATCH(B5,A10:A14,0)+1,1)

If you want to identify the closest size, then put this formula in D5:

=IF((A5-B5)/(C5-B5)<0.5,B5,C5)

If you don't like looking at the intermediate cells, you can always hide those columns. The formulas should work as long as the calculated sheave value is not less than the smallest value in the size table and also less than the largest value in the size table.

Good luck!