×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Vlookup, Dmin or What Function

Vlookup, Dmin or What Function

Vlookup, Dmin or What Function

(OP)
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.

 

RE: Vlookup, Dmin or What Function

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.

RE: Vlookup, Dmin or What Function

(OP)
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?

RE: Vlookup, Dmin or What Function

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.

RE: Vlookup, Dmin or What Function

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

RE: Vlookup, Dmin or What Function

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

 

RE: Vlookup, Dmin or What Function

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
 

RE: Vlookup, Dmin or What Function

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources