Vlookup, Dmin or What Function 1

[SteveGregory]

I have a database of steel shapes that includes a descriptive label, the cross-sectional area, the section modulus, the moment of inertia and a few other items. The data for each shape is on a separate row. I have sorted the database by area (or weight) so that the least weight items are at the top.

I have calculated values for the required minimum section modulus and minimum moment of inertia. I would like to find the first shape in the database that will satisfy the minimum criteria for these two properties. Since the database is sorted by the area (weight), the section modulus and moment of inertia are not sorted in ascending order.

Are there a series of functions that I can use to find the shape? The functions could be in separate equations or combined in one formula.

 

[GregLocock]

OK, the problem is that you want to find the section with the least weight that has modulus >= target modulus AND moi >= target moi.

How ugly do you want?

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[MintJulep]

How about just some filters?

Filter on modulus "greater than or equal" to required value.

Then filter on moment "greater than or equal" to required value.

That will eliminate everything that doesn't meet the requirements, and the first one on the remaining list will be the lightest.

 

[SteveGregory]

Greg, I don't mind splitting the formula up into 3 or 4 cells or more to make the logic easier to follow and debug.

Mint, I have not used filters before. Does a filter create another database that is a subset of the original? Then the second filter would create a third database that is a subset of the second one. Is this close?

Each record has 24 items and there are 300 records in the shape database and it is sorted by the shape's area. To simplify:
The calculated minimum S is in A1
The calculated minimum I is in A2
The shape database is located at C1:F300
The shape Label is in column C
The shape Area A is in column D
The shape S is in column E
The shape I is in column F

I used the Index, Match, Min and Offset functions to solve for the lightest section based on either criteria but not both at the same time. Any more ideas?

 

[MintJulep]

If you use the AutoFilter option it's simply done in place. None of your data is changed, data not meeting the filtering conditions is just hidden from view. Reset the filters to nothing and everything shows up again.

If you are not trying to use the data elsewhere in a spreadsheet for calculations this is the easiest way to "find" what you are looking for.

If you want to use the values in other formulas it's going to get more complicated.

 

[SteveGregory]

Thanks Mint,...more complicated '#~]^&#;

 

[MintJulep]

I think that you could get where you want to be using MS Query. But I'm not familiar enough with it to offer any useful guidance.

Data | Import External Data | New Database Query

 

[zelgar]

Here's some code that can work:

Name the cells mod & moi for the target section modulus and moment of inertia

=IF(AND(C2>=mod,D2>=moi),B2,1E+99)
where C is colum for section modulus
D is the column with moments of inertia
B is the column with the weight

and
=VLOOKUP(MIN(F2:F8),F2:G8,2,FALSE)
where F is the column with the previous formula
G is the column where you copy the descriptive label of the materials

Alternately, a simple macro looking something like this will work:
Sub a()
Top = 2
bottom = 8
SM = Range("c1")
MOI = Range("D1")
w = 1E+99
material$ = "nothing"
For x = Top To bottom
If Range("C" & x) >= SM And Range("D" & x) >= MOI And w > Range("b" & x) Then
w = Range("B" & x)
material$ = Range("A" & x)
End If
Next x
Range("A13") = material$
End Sub

 

[SteveGregory]

Zelgar, you sparked an idea!

I could insert a new column beside the Label column. In those cells, I could use an equation that will do a pass=1 or fail=0 for each of the 300 shapes using a modification of your equation: =IF(AND(C2>=mod,D2>=moi),1,0).

Then I could use MATCH to find the first 1 and INDEX to get the Label and other properties. 300 equations might be the brute force method, but it is simple. I am still open to other solutions if you have any.