Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will VLOOKUP and MATCH work to find combinations of pulley diamete
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will VLOOKUP and MATCH work to find combinations of pulley diamete
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. -- Biff Microsoft Excel MVP "TallOne" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will VLOOKUP and MATCH work to find combinations of pulley dia
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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will VLOOKUP and MATCH work to find combinations of pulley dia
ACK!... so much for it leaving the tabs in when i copied it over...... i'll
try to add them with the space bar. "TallOne" wrote: 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: RPM1 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 RPM2 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup to find match only part of a text value | Excel Discussion (Misc queries) | |||
vlookup/match to find column name | Excel Worksheet Functions | |||
Return alternate value if VLookup can't find match | Excel Worksheet Functions | |||
Find a not exact match using vlookup | Excel Discussion (Misc queries) | |||
using vlookup to find exact match | Excel Discussion (Misc queries) |