Compare Caliper Measurements

Andreavnn

New Member
Joined
Mar 3, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking for help to correct my formula. I am wanting to have a USER input a 'Thread Size' into J7 which references a column [C through G] and then input a 'Caliper Result' which is a decimal value into L7. Using the information is J7 and L7 that formula will select the closest result based on the decimal input in L7 and the column selected J7 and then display the matching results in column B and A, K10 and K11.
 

Attachments

  • Capture.JPG
    Capture.JPG
    199.2 KB · Views: 18

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi @Andreavnn , I hope you are well.

Put the following formula in cell K10, for the result in column B:
Excel Formula:
=INDEX(B1:B32,LARGE(IF(INDEX(C2:G32,,MATCH(J7,C1:G1,0))>=L7,ROW(B2:B32)),1))

Put the following formula in cell K11, for the result in column A:
Excel Formula:
=INDEX(A1:A32,LARGE(IF(INDEX(C2:G32,,MATCH(J7,C1:G1,0))>=L7,ROW(B2:B32)),1))

I suggest that in column A you fill in all the data, otherwise it will return 0 or empty in the cells where you have no information:
Dante Amor
A
1Turns
21-1/2T
31-1/2T
41-1/2T
51-1/4T
61-1/4T
71
81
93/4T
103/4T
113/4T
121/2T
131/2T
141/2T
Hoja7

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​
 
Upvote 0
Solution
Or this:

Put the following formula in cell K10, for the result in column B:
Excel Formula:
=INDEX(A2:B32,MATCHX(L7,FILTER(C2:G32,C1:G1=J7),1),2)

Put the following formula in cell K11, for the result in column A:
Excel Formula:
=INDEX(A2:B32,MATCHX(L7,FILTER(C2:G32,C1:G1=J7),1),1)
 
Upvote 0
This work, but for some reason it seems to be off a few cells when inputting data into L7.

K10
Excel Formula:
=IFERROR(INDEX(B3:B33,LARGE(IF(INDEX(C3:G33,,MATCH(J8,C1:G1,0))>=L8,ROW(B3:B33)),1)),"")

K11
Excel Formula:
=IFERROR(INDEX(A3:A33,LARGE(IF(INDEX(C3:G33,,MATCH(J8,C1:G1,0))>=L8,ROW(B3:B33)),1)),"")

You can see in the image I put in '-0.0519' which should be '-1.4' or '-1-1/4T' I can't seem to get it to function correctly. It will also display blanks sometimes too, you can see in my second image.

I did add the following to the C3-G3:C33-G33 - which will auto-calculate the decimals in those columns to ensure accuracy.
Excel Formula:
=1/$C$2*$B3

I played with it some, but can't seem to get it to function correctly.

EDIT* Column 'A' does have information in each cell I just changed the text color to match the background color for visual design.
 

Attachments

  • Capture.JPG
    Capture.JPG
    222.9 KB · Views: 4
  • Capture1.JPG
    Capture1.JPG
    231 KB · Views: 4
Upvote 0
I placed data in column 'A' and the return is still off. Using '1' or '-1' in the [match_type] doesn't seem to fix it either. Maybe I am doing something wrong?

If I changed the LARGE(array, K) from 1 to 3 everything works, but the results from '1.4' to '1.5' in each column. Both K10 & K11 return blanks.
 

Attachments

  • Capture.JPG
    Capture.JPG
    198.9 KB · Views: 4
  • Capture1.JPG
    Capture1.JPG
    193.5 KB · Views: 4
Last edited:
Upvote 0
In your user profile data it says you have office 365.
You shouldn't have any problems with the formulas:

If not, in the case of this formula you must enter it with an array formula:



varios 12jun2023.xlsm
ABCDEFGHIJKL
1Turns1/6" -1/8"1/4" - 3/8"1/2" - 3/4"1" - 2"2 1/2" - 4"
21-1/2T1-50.05560.08330.10710.13040.1875
31-1/2T1-40.05190.07960.10340.12670.1838
41-1/2T1-30.04820.07590.09970.12300.1801
51-1/4T1-20.04450.07220.09600.11930.1764
61-1/4T1-10.04080.06850.09230.11560.1727Thread SizeCaliper Result
7110.03710.06480.08860.11190.16901/6" -1/8"0.0556
810.90.03340.06110.08490.10820.1653
93/4T0.80.02970.05740.08120.10450.1616Turns
103/4T0.70.02600.05370.07750.10080.15791-5B
113/4T0.60.02230.05000.07380.09710.15421-1/2TA
121/2T0.50.01860.04630.07010.09340.1505
131/2T0.40.01490.04260.06640.08970.1468
141/2T0.30.01120.03890.06270.08600.1431
Hoja7
Cell Formulas
RangeFormula
K10K10=INDEX(B1:B14,LARGE(IF(INDEX(C2:G14,,MATCH(J7,C1:G1,0))>=L7,ROW(B2:B14)),1))
K11K11=INDEX(A1:A14,LARGE(IF(INDEX(C2:G14,,MATCH(J7,C1:G1,0))>=L7,ROW(B2:B14)),1))
Press CTRL+SHIFT+ENTER to enter array formulas.



Note:
Check that the data you type in cell J7 exactly matches any data in cells C1 through G1.
;)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top