Calculating gear ratios in Excel 1

[Steve001]

This a problem dealing with choosing gears for a lathe to give a certain Thread per inch (and of course it's a non standard, no longer produced machine that has no manuals). ABCD are the gear positions and the values are the gears (tooth count) available. I want to give a value for X (threads per inch) and have Excel give me possible values to place in positions ABCD (no doubt there will be multiple options.
I've worked out that

X =(28 x A x C)/(BxD)

But :
110≥(A+B)≤205
110≥(C+D)≤205
Possible Values for A are - 30,35,40,45,50,55,60,63,65,70,80
Possible Values for B,C,D are the same – 30,35,40,45,50,55,60,63,65,70,80,90,98,100,105,120,125

I’ve tried various functions like solver and lookup etc but have been unable to crack this one.

Thanks

 

[SkipVought]

hi,

Make 2 named ranges for the to lists your specified.

Make 4 Data > Validation -- LIST using in-cell drop downs for A, B, C, & D

Reference your formula(s) to the 4 DV Cells

In use, make your 4 selections and VOLA, y'all!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Steve001]

Thanks Skip I may not be following you but I need EXCEL to give the the possible combinations of values for A, B, C, D which result in X.

 

[SkipVought]

This is ALL in Excel.

Lets say that your Data Validation cell for your A value is Named SelectedA and so on for your other 3 values.

Then your formula would be, in any unused cell on your sheet
[tt]
=(28 * SelectedA * SelectedC)/(SelectedB * SelectedD)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

If you want a macro to calculate all of the options...

Sub trial2()
Dim A As Variant
Dim B As Variant
Dim C As Variant
Dim D As Variant
Dim T As Integer
Dim test As Integer
Dim iA As Integer
Dim iB As Integer
Dim iC As Integer
Dim iD As Integer
Dim x As Variant

T = 10
test = 1
iRowA = 1
For iA = 1 To 11
    A = Choose(iA, 30, 35, 40, 45, 50, 55, 60, 63, 65, 70, 80)
    For iB = 1 To 17
        B = Choose(iB, 30, 35, 40, 45, 50, 55, 60, 63, 65, 70, 80, 90, 98, 100, 105, 120, 125)
        If A + B >= 110 And A + B <= 205 Then
        For iC = 1 To 17
            C = Choose(iC, 30, 35, 40, 45, 50, 55, 60, 63, 65, 70, 80, 90, 98, 100, 105, 120, 125)
            For iD = 1 To 17
                D = Choose(iD, 30, 35, 40, 45, 50, 55, 60, 63, 65, 70, 80, 90, 98, 100, 105, 120, 125)
                If C + D >= 110 And C + D <= 205 Then
                    x = 26 * A * C / (B * D)
                    Sheet1.Cells(T, 1) = test
                    Sheet1.Cells(T, 2) = A
                    Sheet1.Cells(T, 3) = B
                    Sheet1.Cells(T, 4) = C
                    Sheet1.Cells(T, 5) = D
                    Sheet1.Cells(T, 6) = x
                    T = T + 1
                    test = test + 1
                End If
             Next iD
        Next iC
        End If
    Next iB
Next iA                   
End Sub

 

[Steve001]

That's great! Now I know this must sound thick but how do I apply that to my spreadsheet?

 

[Steve001]

Sorry I meant to say that the macros worked fantastically. However, the orignial intent was to have a simple screen where the machinists can input the thread pitch required and see the gear options. Anyway to do that with the macro? It is excellent as is though and the data sheets when sorted on X will serve very well.

Thanks again
Steve

 

[zelgar]

Here's what you can do with the macro:
1. change the T=10 to T=2 (Note, if you don't do this you'll have to delete Rows 2 - 9)
2. In Row 1 put following in the cells:
A1 Test
B1 A
C1 B
D1 C
E1 D
F1 X
3. Run the macro
4. FILTER the results

If you don't want to have everything calculated, you can use Data Validation to restrict the data options for B & D. to do this you must do the following:
1. In this example, cell B1 = A; cell B2 = B; cell B3 = C; cell B4 = D; and cell B5 = X
2. In cell B1, use Data Validation to select a list with values 30, 35, 40, 45, 50, 55, 60, 63, 65, 70, 80 OR put these numbers in a column (e.g., column E)
3. In column F, put the data that are options for B, C and D since they are sharing these data (30, 35, 40, 45, 50, 55, 60, 63, 65, 70, 80, 90, 98, 100, 105, 120, 125)
4. Insert the following formula in cell G1: = IF(AND($B$1 + F1>= 120, $B$1 + F1<= 205),F1, "DO NOT SELECT")
5. Copy cell G1 & Paste in cells G1 to G17
6. For cell B2 (the data for B), use Data Validation to select the cells G1:G17
7. For cell B3 (the data for C), use Data Validation to select the cells F1:F17
8. Insert the following formula in cell H1: = IF(AND($B$3 + F1>= 120, $B$3 + F1<= 205),F1, "DO NOT SELECT")
9. Copy cell H1 & pase in cells H1 to H17
10. For cell B4 (the data for D), use Data Validaton to select the cells H1:H17
11. For cell B5 (the data for calculated X), insert the following formulae:
=IF(AND(B2<>"DO NOT SELECT",B4<>"DO NOT SELECT"),26 * B1 * B3/(B2 * B4),"")

 

[Steve001]

Thanks I now have a complete table of the 2,6082 gear combinations with the restrictions on gear sizes for position A.
i.e.
A B C D
1 30 35 120 30
2 30 90 90 40

Now, there is only one gear of each tooth size so I have to eliminate duplicates – or at least identify where they are. I can do that with:
=IF(COUNTIF(A11,MODE(A11))>1, "Has duplicates", "No duplicates")
But unfortunately that eliminates the case where B and C can be equal ( because in the gear set up it provides for a spacer or "joker" in the B or C positions (ie row 2 in the example)
So where B=C, A ≠B ≠ D
Any help greatly appreciated, and can it be added to Zelgar’s macro?

 

[zelgar]

Change the following line:
If C + D >= 110 And C + D <= 205 Then
to
If C + D >= 110 And C + D <= 205 And (C <> B Or (C = B And A <> B And A <> D And B <> D)) Then

 

[zelgar]

Note: For my change above the C = B And A <> B And A <> D And B <> D means A ≠ B, A ≠ D and B ≠ D, which is what I think you're saying with "So where B=C, A ≠B ≠ D" If you only care about A ≠ B and B ≠ D the code should be:
If C + D >= 110 And C + D <= 205 And (C <> B Or (C = B And A <> B And B <> D)) Then