Forum Discussion

Jn12345's avatar
Jn12345
Brass Contributor
Nov 02, 2023

IF or FILTER statement to decide if a Cell and its length Falls between ranges

The title is probably not the best way of describing what im trying to ask for. Basically I have defect measurements (Start (m) and Length (mm)) and i need to determine if any part of the length from...
Excel
Formulas and Functions
office 365
peiyezhu's avatar
peiyezhu
Bronze Contributor
Nov 05, 2023
This is accounting for the start of the defect which sleeve it would be under whereas
please refer to below

https://b23.tv/4FbSRks

The only issue is it doesnt account for long defects that continue under multiple sleeves.
Can you share your workbook file.and expected result?
I can not get what you.mean about long defects .
Jn12345's avatar
Jn12345
Brass Contributor
Nov 05, 2023

peiyezhu 

Thank you for your help. please see attached. I hope this helps explain. you can see some of the defects in red under some of the sleeves in black on the pipeline that the defects are found on in grey. You can see that MFG-001 is covered under multiple sleeves so if it is considered SLEEVED (being covered by at least 50mm on each end of the defect then the repair type is SLEEVED so if it has that critera of being repaired by sleeved in the repair type column then any sleeve it falls under should see it as a defect that is under the sleeve. Please see attached and i hope this explains. also on the attached excel sheet i have attached another table below for what it should look like with proper formulas. Basically the formula statement would be to say the defect is an associated defect if it meets two criteria. the defect says SLEEVED in the repair type column and if any part of the defects length from start to end falls underneath the sleeve area. 

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Nov 06, 2023

    Jn12345 

    From the image I got it more clearly.
    There are some SLEEVED(I consider them as some metal pieces or parts maned SLV-004
    SLV-005
    SLV-006)
    .
    And you do detect on them.
    The most of defects are very small size which like below 100mm we call poit defects because of the small size.
    But MFG-001 is an exception which size is 10000mm so that it cover several pieces of parts or SLEEVES (cover from begin of SLV-004 through
    SLV-005 until the end of
    SLV-006.
    )
    I can understand this point from the SLEEVE Defects2.png.

     

     

    the below table Range B20:E28 will be your expected result right?


    if yes,
    SLV-004 6.7
    SLV-007 19 20
    13.3=20-6.7

    only 13.3mm not exceed 100000mm,why not in MFG-001

    why

    SLV-006 13 18 MFG-001

    SLV-007 19 20

     

    SLV-007 NOT be.covered by MFG-001.

     

    The image shows SLV-006 has long size (more than 900mm from.the image rather than 5mm(18-13=5mm range C26-B26)

     

    So I feel.confused with your datas or expected result in Sheet1.

    they are not consist with the image(see my image )

     

    So,can.you confirm your expected result without SLV-007 MFG-001 covered?

     

     

     

Resources