I have this situation :
Diameter 35, and Tolerance max=0 and min=-0.014
I need to look for this Diameter in another Table, and find the moust appropiate value to my Tolerance
See attached for better description (I hope)
Thanks
I have this situation :
Diameter 35, and Tolerance max=0 and min=-0.014
I need to look for this Diameter in another Table, and find the moust appropiate value to my Tolerance
See attached for better description (I hope)
Thanks
Hi,
While the format of data seems easy for human, I think it is not so easy for excel.
And especially if for some reason you keep numbers as texts (see formatting of cells in your file - a lot of them are neither General, nor Number, but Text).
Next point for diameters 10-20 you doi not have tolerances, but texts max/min.
I thought it is not for reason, but it "just happened" so restored numbers their numerical nature :-)
My suggestion (for sake of functions readability) is to use helper cells. For instance in G15 (below table):
=INDEX(G4:G8,MATCH($A3,$E4:$E8))
and copy right. This will pick up from the table interesting row.
This would be enough to have (rather complex) arrray formulas in D11, F11 and F12. as presented in Step 1 sheet (it is ready to use as is).
But looking for simplicity I'd do (as shown in Step 2X sheets)
- move cells with min tolerances below their max counterparts (H15 to G16 etc) see Step 2a,
- and then move cells them left (I15:16 to H15:16, and the same with next column) See Step 2b
- add headers in row 14
- in row 17 (G17 and copy to right) add checking whether required tollerances fit inside tollerance for given class*:
Formula:Please Login or Register to view this content.
value of 1000 or more will indicate that we felt out the field of tollerance
- now finding values is super-easy, D11:
Formula:Please Login or Register to view this content.
F11 (and similar for F12):
Formula:Please Login or Register to view this content.
So if it suits you you can delete first 2 shets and work just with this one.
* Note that in Step 1 formulas I used "closest abs value" - formulas in D11, F11 and F12. It could be adjusted, but I think the second approach is better anyway, so left them as they were.
Best Regards,
Kaper
I would like to thank you, it worked out great
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks