# Specialty Search with Calculation and Output

#### smyers

##### New Member
I have a sheet with three columns:
Column 1: Gear Ratio
Column 2: Horsepower Rating
Column 3: Model

There are six inputs:
Input 1: Specific Horsepower
Input 2: Minimum Service Factor
Input 3: Maximum Service Factor
Input 4: Minimum RPM
Input 5: Maximum RPM
Input 6: Actual Motor Speed

Based on the inputs, I would like to output a list of all Models, with corresponding Service Factor, Gear Ratio, and RPM which fall into the desired ranges for both Service Factor and RPM.

Service factor is a function of Horsepower and Horsepower Rating.
Service Factor = Horsepower Rating/Horsepower

RPM is a function of Actual Motor Speed and Gear Ratio.
RPM = Actual Motor Speed/Gear Ratio

I'm not sure if a VBA program is required to perform this. If so, suggested language would be great! I'm not a programmer, unfortunately.

Your Expert help is appreciated!!

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### JB_Scotland

##### Board Regular
You could do the query in situ. Use Data / Auto Filter on the motor specification table to show only those rows that match TRUE. An example below though I don't know if the values are representative.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 154px"><COL style="WIDTH: 135px"><COL style="WIDTH: 88px"><COL style="WIDTH: 117px"><COL style="WIDTH: 99px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 49px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Inputs</TD><TD style="FONT-WEIGHT: bold"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Specific Horsepower</TD><TD style="TEXT-ALIGN: right">30</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Minimum Service Factor</TD><TD style="TEXT-ALIGN: right">0.8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Maximum Service Factor</TD><TD style="TEXT-ALIGN: right">1.7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Minimum RPM</TD><TD style="TEXT-ALIGN: right">850</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Maximum RPM</TD><TD style="TEXT-ALIGN: right">1700</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Actual Motor Speed</TD><TD style="TEXT-ALIGN: right">1000</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Gear Ratio</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Horsepower Rating</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Model</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Service Factor</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">RPM</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">SF</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">RPM</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Match</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">0.766666667</TD><TD style="TEXT-ALIGN: center">200</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">0.5</TD><TD style="TEXT-ALIGN: center">333.3333333</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">D</TD><TD style="TEXT-ALIGN: center">0.266666667</TD><TD style="TEXT-ALIGN: center">142.8571429</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">45</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center">1.5</TD><TD style="TEXT-ALIGN: center">250</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: center">F</TD><TD style="TEXT-ALIGN: center">1.1</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">G</TD><TD style="TEXT-ALIGN: center">0.933333333</TD><TD style="TEXT-ALIGN: center">1000</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">TRUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">0.633333333</TD><TD style="TEXT-ALIGN: center">125</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D10</TD><TD>=B10/\$B\$2</TD></TR><TR><TD>E10</TD><TD>=\$B\$7/A10</TD></TR><TR><TD>F10</TD><TD>=IF(AND(D10>\$B\$3,D10<\$B\$4),1,0)</TD></TR><TR><TD>G10</TD><TD>=IF(AND(E10>\$B\$5,E10<\$B\$6),1,0)</TD></TR><TR><TD>H10</TD><TD>=AND(F10,G10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

#### smyers

##### New Member
Thanks for looking at this, that's a great start. Is there any way to have an output which lists in a separate table the Model, Gear Ratio, RMP, and Service Factor of only the TRUE matches?

I have over 450 models with varying ratios and horsepower ratings and scrolling through the list to find true matches is not convenient for quick results.

Otherwise, your solution will work fine. Thanks again.

#### JB_Scotland

##### Board Regular
Select any cell in the motor specification table. Go to menu Data / Filter / Auto Filter. In the Match column select TRUE from the drop down list and the specification table will only show those models that match the query.

You can rearrange and hide columns that are used for calculating as you wish.

You could create VBA code to do the same thing with a user form for entering the query data. It would be a slicker interface but would require more work setting up.

Replies
3
Views
918
Replies
2
Views
964
Replies
1
Views
947
Replies
0
Views
1K
Replies
1
Views
2K

1,191,087
Messages
5,984,543
Members
439,896
Latest member
SquareCare

### 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?

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