Will VLOOKUP and MATCH work to find combinations of pulley diamete

[TallOne]

I'm trying to use Excel to calculate the output shaft rpm of compound
pulley systems. Pulley sizes are available in half inch increments. I've got
Excel set up to calculate the ratio of the known input rpm and the target
output rpm. I've got a table on another worksheet with the ratio of the
driver(vertical column of half inch increments from 1 to 30) and
driven(horizontal row of half inch increments from 1 to 30) pulley diameters.
What I need Excel to do is take the input rpm, divide it by the target rpm,
then search the ratio table for all the combinations of pulley diameters that
will provide that target rpm. One complication is that I need it to do this
for combinations of pulleys in mulitples of 2 (2,4,6...). For example when I
have an input rpm of 1725 and need an output rpm of 150 I need to use a ratio
of pulley diameters of 11.5:1. In a 4 pulley system the combination of a
driver(1) pulley dia. of 3 inches and a driven(1) pulley dia. of 13 inches
produces a ratio of 4.33:1. Then the second driver(2) pulley is on the same
shaft as driven(1). If the second driver(2) pulley is 3 inches in dia. and
the second driven(2) pulley is 8 inches that produes a ratio of 2.67:1.
multiplying 2.67 and 4.33 meets the required total ratio of 11.5:1. I've got
Excel set up to produce a factor tree of combined ratios that meet the total
ratio needed to produce the target rpm. Where I'm stuck is how to get Excel
to search the tables that are calculating rpm from known input rpm and
calculated total ratio and then spit out the different combinations of
driver(1)~driven(1) and driver(2)~driven(2) pulley diameters that produce the
target rpm. I'm trying to use VLOOKUP and MATCH, but I only know enough about
excel to be dangerous and it's not working. Any help, advice, and ideas would
be most welcome.

Thanks in advance, Rob...

P.S.
I'll also need to do some trig functions on the diameters to make sure
that the belt is in contact with at least 120 degrees of both pulley's
surfaces. I figure I can do that with a similar setup to calculating output
rpm(using center to center distance and diameter).Again, thanks and sorry
about such a long post.

 

[T. Valko]

This is something I would need to see to understand what's going on. The
basic lookup is no problem but you have all those calculated conditions that
need to be dealt with. If you don't get any other replies that solve the
problem and you are willing to send a copy of the file to me I'll see what I
can do. Let me know in this thread if you want to do that.

 

[TallOne]

Thanks for the fast reply Biff. The odor you smell is my brain melting trying
to figure out the logic, math and commands on this infernal machine. I'll try
to copy a bit of what I've got on here and we'll go from there.

These are my input data items:

CATEGORY DATA IN
Input RPM = 1725.00 <-- Known rpm
Output RPM = 150.00 <-- this is the target rpm
Ratio = 11.50 <-- calculated... 1725/150=11.5
min pulley size = <-- ignore these 2 for now
MAX PULLEY SIZE =

From which I calculate available ratios (this is real primative at the
moment):
WHEN RATIO CALCULATED RATIO 1 RATIO 2
1 2:1 FACTOR TREE 4 PULLEY /2 5.75:1
2 3:1 FACTOR TREE 4 PULLEY /3 3.83:1
3 4:1 FACTOR TREE 4 PULLEY /4 2.88:1
4 5:1 FACTOR TREE 4 PULLEY /5 2.30:1


And this is my ratio calculator worksheet:
Pulley diameter ratio:

DRIVER dia. DRIVEN dia.
1.0 1.5 2.0 2.5 3.0..........
1.0 1.00 1.50 2.00 2.50 3.00
1.5 0.67 1.00 1.33 1.67 2.00
2.0 0.50 0.75 1.00 1.25 1.50
2.5 0.40 0.60 0.80 1.00 1.20
3.0 0.33 0.50 0.67 0.83 1.00
..
..
..Where DRIVEN/DRIVER = ratio

And these are my rpm out worksheets:

RPM 1

DRIVER(1) DRIVEN(1)
1.0 1.5 2.0 2.5 3.0......
1.0 1725.0 1150.0 862.5 690.0 575.0
1.5 2587.5 1725.0 1293.8 1035.0 862.5
2.0 3450.0 2300.0 1725.0 1380.0 1150.0
2.5 4312.5 2875.0 2156.3 1725.0 1437.5
3.0 5175.0 3450.0 2587.5 2070.0 1725.0


RPM 2

DRIVER(2) DRIVEN(2)
1.0 1.5 2.0 2.5 3.0......
1.0 1725.0 1150.0 862.5 690.0 575.0
1.5 2587.5 1725.0 1293.8 1035.0 862.5
2.0 3450.0 2300.0 1725.0 1380.0 1150.0
2.5 4312.5 2875.0 2156.3 1725.0 1437.5
3.0 5175.0 3450.0 2587.5 2070.0 1725.0


1.0, 1.5, etc. represent pulley diameter and 1725.0, 2587.5 etc. represent
output rpm(input RPM/(DRIVEN/DRIVER)). The table continues out to 30"
diameter in both the "driver" column and the "driven" row

At the moment I'm thinking of using 2 identical rpm worksheets (for a 4
pulley system) and making Excel spit out whatever combination of the 2
worksheets produce the target output rpm. The other option(I think) is to use
a command to loop through the same worksheet twice to get the combined output
rpm.

I realize there might not be an "exact" match for the output rpm, but if I
could choose between combinations that produce within 98%-100% of the target
rpm that would be close enough.

I didn't include the min and max pulley size data in the first question
because I think that'll be easy to solve compared to the rest.

Thanks again for the reply and let me know if this is enough data,

Rob

 

[TallOne]

ACK!... so much for it leaving the tabs in when i copied it over...... i'll
try to add them with the space bar.