Searching through individual cells to see if value falls within multiplicative range

[gr33kfr33k5]

Hello everyone,

I've been scratching my head on this one and as a mere dabbler in excel formulas I think it is far beyond my skill. I'll lay out exactly what I want to do.

Firstly,

Check whether or not an inputed value falls within a given range determined by checking individual cells in a table. Optimally the formula would run through the entire "A" column and check if the "value" ( we'll call i pipe size from here on out) is within the range of .9 - 1.5 times any given cell value in column "A."

If false the formula responds "No valid calibration standard"
If this returns true . . .

Secondly,

The formula then checks whether or not a second value (we'll call this pipe thickness) falls within another range in column "B." Its checking if any individual cell in column B falls within +-50% of "pipe thickness."

If false the formula responds "no valid calibration standard"
If true. . .

Lastly,

The formula displays an entire row of information based on which cells actually meet both of the criteria specified

Below is vaguely how my spreadsheet is set up:


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Diameter[/TD]
[TD="align: center"]Wall Thickness[/TD]
[TD="align: center"]Reflector Type[/TD]
[TD="align: center"]SN[/TD]
[TD="align: center"]Material[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD].688[/TD]
[TD]Notch[/TD]
[TD]1[/TD]
[TD]cs[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD].406[/TD]
[TD]notch[/TD]
[TD]2[/TD]
[TD]cs[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD].599[/TD]
[TD]notch[/TD]
[TD]3[/TD]
[TD]cs[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1.858[/TD]
[TD]Side Drilled Hole[/TD]
[TD]4[/TD]
[TD]cs[/TD]
[/TR]
[TR]
[TD]"pipe size"[/TD]
[TD]"Pipe Thickness"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

*pipe size and thickness can be located anywhere on the spreadsheet, preferably on another sheet and are both input values.


Thanks for any help!

 

[mikerickson]

To restate, you have a column of pipe sizes another column of pipe thicknesses and other columns of data.

Given a particular size and thickness, you want to see those rows where the size is > .6667 times the given size, < 1.11 times the given size, > .667 times the given thickness and < 2 times the given thickness.

To do that I would use Advanced Filter.

If your data is in A1:E1 (with headers), and your given size is in G1, given thickness in H1. Leave M1:P1 blank.
In M2 put the formula =($A2>($G$1/1.5))
In N2 put the formula =($A2<($G$1/.9))
IN O2 put =($B2>($H$1/1.5))
in P2 put = ($B2<($H$1/.5))

Then use Advanced filter with your A:D data as the data range, and M1:P2 as the criteria range

 

[gr33kfr33k5]

Thanks for the help!

I think if I could figure out the first part the rest would fall into place. The data set for all "pipe size" references is column A, for "pipe thickness" it is only column B. All other columns need not be referenced.

Is there a way for me to change that formula to instead of only checking "A2" to instead check every cell in the A column?

 

[mikerickson]

Formulas used in criteria ranges adjust their references as the row being looked at changes. Used in a criteria range, it will look at the to see if the A and B column cells meet the criteria.