#### dougwashere

##### New Member

- Joined
- Dec 22, 2016

- Messages
- 2

I am hoping someone can help me out here.

I am looking for a formula that will choose the "Closest Match" from the "Available On Hand" list based on the three "Calculated" columns. It does not need to be an exact match, but must fall between the "Calculated MIN and MAX". And have it continue choosing the closest but not using more options than what is on hand.

Basically I want to make sure I am using the best option and all the options, before I have to order additional shims.

Any help would be appreciated.

Book1 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | L | |||

3 | MIN | MID | MAX | Calculated New Clearance (mm) | ||||||||||

4 | Exhaust Spec (mm) | 0.25 | 0.30 | 0.35 | ||||||||||

5 | Measured (inch) | Measured (mm) | Current Shim | Calculated MIN Shim | Calculated MID Shim | Calculated MAX Shim | Closest Match On Hand | Shim Installed | Available Shims On Hand | |||||

6 | ||||||||||||||

7 | ||||||||||||||

8 | Cylinder | 0.005 | 0.127 | 5.450 | 5.327 | 5.277 | 5.227 | 5.450 | 0.127 | 5.530 | ||||

9 | 1 | 0.008 | 0.203 | 5.450 | 5.403 | 5.353 | 5.303 | 5.450 | 0.203 | 5.490 | ||||

10 | 5.490 | |||||||||||||

11 | Cylinder | 0.009 | 0.229 | 5.430 | 5.409 | 5.359 | 5.309 | 5.450 | 0.209 | 5.470 | ||||

12 | 2 | 0.009 | 0.229 | 5.420 | 5.399 | 5.349 | 5.299 | 5.420 | 0.229 | 5.460 | ||||

13 | 5.450 | |||||||||||||

14 | Cylinder | 0.006 | 0.152 | 5.410 | 5.312 | 5.262 | 5.212 | 5.410 | 0.152 | 5.450 | ||||

15 | 3 | 0.011 | 0.279 | 5.390 | 5.419 | 5.369 | 5.319 | 5.390 | 0.279 | 5.450 | ||||

16 | 5.450 | |||||||||||||

17 | Cylinder | 0.011 | 0.279 | 5.390 | 5.419 | 5.369 | 5.319 | 5.390 | 0.279 | 5.450 | ||||

18 | 4 | 0.010 | 0.254 | 5.340 | 5.344 | 5.294 | 5.244 | 5.340 | 0.254 | 5.430 | ||||

19 | 5.420 | |||||||||||||

20 | Cylinder | 0.000 | 0.000 | 0.000 | -0.250 | -0.300 | -0.350 | 0.000 | 0.000 | 5.410 | ||||

21 | 5 | 0.000 | 0.000 | 0.000 | -0.250 | -0.300 | -0.350 | 0.000 | 0.000 | 5.390 | ||||

22 | 5.390 | |||||||||||||

23 | Cylinder | 0.000 | 0.000 | 0.000 | -0.250 | -0.300 | -0.350 | 0.000 | 0.000 | 5.340 | ||||

24 | 6 | 0.000 | 0.000 | 0.000 | -0.250 | -0.300 | -0.350 | 0.000 | 0.000 | |||||

Sheet2 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

E8 | =SUM($C8,-($E$4-$D8)) | |

E9 | =SUM($C9,-($E$4-$D9)) | |

E11 | =SUM($C11,-($E$4-$D11)) | |

E12 | =SUM($C12,-($E$4-$D12)) | |

E14 | =SUM($C14,-($E$4-$D14)) | |

E15 | =SUM($C15,-($E$4-$D15)) | |

E17 | =SUM($C17,-($E$4-$D17)) | |

E18 | =SUM($C18,-($E$4-$D18)) | |

E20 | =SUM($C20,-($E$4-$D20)) | |

E21 | =SUM($C21,-($E$4-$D21)) | |

E23 | =SUM($C23,-($E$4-$D23)) | |

E24 | =SUM($C24,-($E$4-$D24)) | |

F8 | =SUM($C8,-($F$4-$D8)) | |

F9 | =SUM($C9,-($F$4-$D9)) | |

F11 | =SUM($C11,-($F$4-$D11)) | |

F12 | =SUM($C12,-($F$4-$D12)) | |

F14 | =SUM($C14,-($F$4-$D14)) | |

F15 | =SUM($C15,-($F$4-$D15)) | |

F17 | =SUM($C17,-($F$4-$D17)) | |

F18 | =SUM($C18,-($F$4-$D18)) | |

F20 | =SUM($C20,-($F$4-$D20)) | |

F21 | =SUM($C21,-($F$4-$D21)) | |

F23 | =SUM($C23,-($F$4-$D23)) | |

F24 | =SUM($C24,-($F$4-$D24)) | |

G8 | =SUM($C8,-($G$4-$D8)) | |

G9 | =SUM($C9,-($G$4-$D9)) | |

G11 | =SUM($C11,-($G$4-$D11)) | |

G12 | =SUM($C12,-($G$4-$D12)) | |

G14 | =SUM($C14,-($G$4-$D14)) | |

G15 | =SUM($C15,-($G$4-$D15)) | |

G17 | =SUM($C17,-($G$4-$D17)) | |

G18 | =SUM($C18,-($G$4-$D18)) | |

G20 | =SUM($C20,-($G$4-$D20)) | |

G21 | =SUM($C21,-($G$4-$D21)) | |

G23 | =SUM($C23,-($G$4-$D23)) | |

G24 | =SUM($C24,-($G$4-$D24)) | |

C8 | =B8*25.4 | |

C9 | =B9*25.4 | |

C11 | =B11*25.4 | |

C12 | =B12*25.4 | |

C14 | =B14*25.4 | |

C15 | =B15*25.4 | |

C17 | =B17*25.4 | |

C18 | =B18*25.4 | |

C20 | =B20*25.4 | |

C21 | =B21*25.4 | |

C23 | =B23*25.4 | |

C24 | =B24*25.4 | |

J8 | =$C8+($D8-$I8) | |

J9 | =$C9+($D9-$I9) | |

J11 | =$C11+($D11-$I11) | |

J12 | =$C12+($D12-$I12) | |

J14 | =$C14+($D14-$I14) | |

J15 | =$C15+($D15-$I15) | |

J17 | =$C17+($D17-$I17) | |

J18 | =$C18+($D18-$I18) | |

J20 | =$C20+($D20-$I20) | |

J21 | =$C21+($D21-$I21) | |

J23 | =$C23+($D23-$I23) | |

J24 | =$C24+($D24-$I24) |