Excel Formula - Pick the next highest pipe wall thickness 3

[MattGreer]

I have a table of pipe data. The data includes the Pipe Size, Outer Diameter, Inner Diameter, and Wall Thickness.

For pipeline related calculations, a minimum wall thickness is calculated for the given service. I need help creating a formula that will pick the next highest wall thickness for a given pipe size.

So for example, let's say my calculation says for a 4" pipe, I need a minimum wall thickness of 0.242".

From the table below, how would I ensure the formula chooses a wall thickness of 0.250?

[tt]Size OD ID WT
1 1.315 0.957 0.179
1 1.315 0.815 0.250
1 1.315 0.599 0.358
2 2.375 1.939 0.218
2 2.375 1.875 0.249
2 2.375 1.813 0.281
4 4.5 4.124 0.188
4 4.5 4.094 0.203
4 4.5 4.062 0.219
4 4.5 4.026 0.237
4 4.5 4.000 0.250
4 4.5 3.938 0.281
6 6.625 6.281 0.172
6 6.625 6.249 0.188
6 6.625 6.219 0.203
6 6.625 6.187 0.219
6 6.625 6.125 0.252
[/tt]

Thanks for your help!

Thanks!!


Matt

 

[strongm]

Since you have asked this in the vba forum, do you want 'a formula' (as in your op), or a user defined function?

 

[MattGreer]

Oh geez, my mistake! I'm usually asking VBA questions so I wasn't paying close enough attention when I posted.

To be honest, I'd love to know how to use VBA to do this as well as a formula. I think the formula would be the "better" option, however, as the result will be going into a table and with Excel's capabilities the formula will just get copied down automatically with each new row.

Should I repost this in another forum? Should it go in the Microsoft Office forum, or somewhere else?

Thanks!!


Matt

 

[Andrzejek]

how to use VBA to do this

If a VBA, how do you want it to work?
Do you want to provide Pipe Size and a minimum Wall Thickness in cells (for example) E1 and E2 respectively and then your VBA will highlight the appropriate row of data?
Or do you want to have 2 Input boxes asking you to provide Pipe Size and a minimum Wall Thickness and, as an outcome, you want a message box with the “wall thickness of 0.250”?
Combination of both from above?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Actually, as I think about it, I think a custom function in VBA would be quite nifty. Something that would take two arguments, pipe size and min wall thickness, and the result of the function would be the answer. I could stuff the pipe dimension info into an array in VBA but I think leaving it in the spreadsheet would be better because the user (me) often references the info.

So I have that data in a table named "tblPipeData" and the columns are named Size, OD, ID, and WT.

I'm really looking forward to this answer because while I'd like to think I know a good bit about Excel, I can't wrap my mind around how you can tell the software to look at a "sub table" (so to speak) identified by 'Size', and *then* find a value greater than or equal to the min wall. This will be quite educational for me.



Thanks!!


Matt

 

[SkipVought]

Matt,

What happens at the boundaries?

Do you allow thicknesses less than the minimum WT for any Size? I suspect yes.

Do you allow thicknesses greater than the maximum WT for any Size? I suspect no.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MattGreer]

Heya skip!

I interpret your question to ask if I would ever select a pipe with a wall thickness less than the calculated number. Wall thicknesses less than the calculated minimum wall thickness (min wt) are NOT allowed. If we used a pipe with a thinner wall it could burst.

If the min wt calc results in a number for which there is no selection available, I would generate an error. The normal Excel error would be fine, but if there's a way to customize it via the custom function in VBA, then I'd enter a message "Min wall requirements exceeded" or something more clear.

If I misunderstood you I apologize; please let me know.

Thanks!!


Matt

 

[Andrzejek]

I am sure Skip will have a more elegant solution, but here is mine:

Enter required Size and Min WT in cells G1 and G2, run this code:

Option Explicit

Sub Matt()
Dim r As Integer

Cells.Interior.Pattern = xlNone

r = 2
Do While Cells(r, 1).Value <> ""
    If Cells(r, 1).Value = Cells(1, 7).Value And _
        Cells(r, 4).Value >= Cells(2, 7).Value Then
    
    Range("A" & r & ":D" & r).Interior.Color = vbYellow
    
    Call MsgBox("Your wall thickness should be " & _
        Cells(r, 4).Value, vbInformation, "Found it.")
        
        Exit Sub
    End If
    r = r + 1
Loop

Call MsgBox("Cannot find what you are looking for.", vbCritical, "No Cigar")

End Sub

Assuming your data is ordered by Size and WT

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Data is ordered by size and wt.

So I wanted to find an equation-type function to do this but I doubt that exists. I made some small modifications to the code to set it up as function, and so far it works perfectly. Thank you! This is going to save me from having to do a lot of annoying lookup stuff.

I wish I could give you bonus points for "No cigar" lol.

Option Explicit

'Final version
Public Function Matt(size As Single, wt As Single) As Single
    Dim r As Integer

    r = 2
    Do While Cells(r, 1).Value <> ""
        If Cells(r, 1).Value = size And _
            Cells(r, 4).Value >= wt Then
            
            Matt = Cells(r, 4).Value
            
            Exit Function
        End If
        r = r + 1
    Loop
    
    Call MsgBox("Cannot find what you are looking for.", vbCritical, "No Cigar")

End Function

Thanks!!


Matt

 

[MattGreer]

Since this calc could be anywhere, I went ahead and modified the code to use structured tables. Also, because I frequently change the orders of the columns in a table, there's a little function (I don't know who to give credit to tho) that will get the row number for you each time so you don't have to keep up with the code.

Public Function SelectPipe(size As Single, wt As Single) As Single
    Dim r As Integer
    Dim wtCol, pipeCol As Integer
    Dim tbl As ListObject
    
    Set tbl = Sheets("Data-Pipe").ListObjects("tblPipe")
    
    wtCol = GetIndex("WT", "tblPipe")
    pipeCol = GetIndex("Nominal", "tblPipe")
    r = 2
    
    Do While tbl.DataBodyRange(r, wtCol).Value <> ""
        If tbl.DataBodyRange(r, pipeCol).Value = size And _
            tbl.DataBodyRange(r, wtCol).Value >= wt Then
            
            SelectPipe = tbl.DataBodyRange(r, wtCol).Value
            
            Exit Function
        End If
        r = r + 1
    Loop
    
    Call MsgBox("Cannot find what you are looking for.", vbCritical, "No Cigar")

End Function

Public Function GetIndex(colName As String, Table As String)

    Table = Table & "[#Headers]"
    GetIndex = WorksheetFunction.Match(colName, Range(Table), 0)

End Function

Thanks!!


Matt

 

[SkipVought]

I can't say that this is more elegant than Andy's VBA solution, but its a challenging spreadsheet solution.

First off, I added a helper column that concatenates Size and WT. This is my MATCH() lookup range.

The approach trades on the use of the MATCH(lookup_value,lookuo_array,match_type) function's match type.

Match Type 0: finds the first value that is exactly equal to lookup_value.
Match type 1: finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order

The challenge is that the match_type 1 doesn't act like we want. First off, lookup_values less than the minimum lookup_array value return an error and lookup_values greater than the maximum lookup_array value return a value that we don't want.

Consequently, I have this expression to compensate...
[tt]
A1
=INDEX(Table1[Size], IFERROR(IFERROR(MATCH($I$3,Table1[Key],0),MATCH($I$3,Table1[Key],1)+1),MATCH($G$3,Table1[Size],0))
,1)

B1,C1,D1
=IF($G$3=$A$1,INDEX(Table1[WT], IFERROR(IFERROR(MATCH($I$3,Table1[Key],0),MATCH($I$3,Table1[Key],1)+1),MATCH($G$3,Table1[Size],0))
,1),NA())
[/tt]

BTW, the lookup table is a Structured Table

Sample workbook attached.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MattGreer]

Very nice Skip. I think it's good to have multiple options because sometimes spreadsheets with macros are frowned upon when attempting to share.

Thanks!!


Matt

 

[SkipVought]

BTW, I'd also lock all cell except the 2 Entry cells and then protect the sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

With this in G3.

=XLOOKUP(G2,FILTER(Table2[WT],Table2[Size]=G1),FILTER(Table2[WT],Table2[Size]=G1),,1,1)

The FILTER() returns an array of only the WT of the Size given in G1.

XLOOKUP() with the match type option = 1 works the way we need.

Could be simplified using Lambda functions if working in a version with them available.

 

[SkipVought]

Mint, great solution. In my research, I saw XMATCH() referenced, but didn't bother to dig into that. You have spurred me on to do some digging on X...() functions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Matt,

I see you have fun with VBA

Public Function SelectPipe(size As [red]Single[/red], wt As Single) As Single
    Dim r As Integer
    Dim [red]wtCol[/red], pipeCol As Integer
    Dim tbl As ListObject
...

Looks to me that size should be an Integer, based on your data example...
Also, wtCol is declared here as a Variant, and only pipeCol is an Integer, just FYI

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

The X functions only exist in Excel for Office 365

 

[MattGreer]

Looks to me that size should be an Integer, based on your data example

To make this simpler for the discussion here, I left out the fractional pipe sizes (1 1/2, 2 1/2, etc.)

I thought when you put two variables on the same line in a Dim statement, they'd both get declared as the same kind of variable? Man, I appreciate that. I don't do that often, thankfully!

Thanks!!


Matt

 

[SkipVought]

Matt, FYI...

Dim wtCol, pipeCol As Integer

wtCol is declared to be a Variant
pipeCol is declared to be Integer

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[strongm]

>two variables on the same line in a Dim statement, they'd both get declared as the same kind of variable

Coo - for 'classic' Microsoft Basics not since QuickBasic .. (althpough VB.NET reintroduces and expands on it)