Forum Discussion

Fabiencote's avatar
Fabiencote
Copper Contributor
Sep 17, 2019

I need help with Excel

Sorry, I have posted this earlier but it has been marked as spam even though I have a legitimate question.   I'm new at this. I've tried to search for an answer through the existing posts, but I co...
excel
Formulas and Functions
Drogar's avatar
Drogar
Copper Contributor
Sep 17, 2019

If I'm not mistaken outside diameter of the pipe are always the same. Inside diameter is decreasing with thickness increase.

Here's a tutoriral for drop down list https://www.youtube.com/watch?v=KGnvCKiOLM0.

Do not understand what are you trying to calculate ?

I'm also in pipe welding branche (IWE).

  • Fabiencote's avatar
    Fabiencote
    Copper Contributor
    Sep 19, 2019

    If I enter on excel the data of the chart appearing in the link, how can I ask Excel to output the actual diameter and the actual thickness from 2 input parameters that are the nominal diameter and the schedule. You are right, the outside diameter between 2 schedules is the same, it's the inside diameter that decreases, but it does not matter in my case. What I'm trying to calculate does not matter either. And I do not have problems doing dropdown list.

    Here is an example:

    Let's say the user wants to use the sheet for a 1/8 schedule 40 pipe. I want him to select 1/8 in a dropdown list and schedule 40 in another list. From there, I want excel to use those two data and be able to go in the chart (for which the data is somewhere hidden in the sheet) and output 0.405 as actual diameter and 0.068 for the thickness. I have tried to go with IF functions (IF(DROPDOWNLIST1="1/8";0.405;IF(DROPDOWNLIST1="3/16";0.540; etc), but there are too many levels of imbrication and excel does not allow it. 

    Is there a function that takes the input parameter and can convert it to a row number( ex: A), takes a second parameter and converts it to a column number (ex: 1) and outputs the value of the case A1?

     Drogar 

    • Stephen_Vidulich's avatar
      Stephen_Vidulich
      Copper Contributor
      Sep 20, 2019

      Fabiencote try this.

       

      Basically you have to use a vlookup and an hlookup with and index row at the bottom of you list.

       

      I got it working for you ... assuming I have understood what you want.

      • Fabiencote's avatar
        Fabiencote
        Copper Contributor
        Oct 03, 2019

        Stephen_Vidulich Sorry for the late reaction. That is exactly what I was trying to do!

         

        Thanks a lot!

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 19, 2019

      Fabiencote 

      Yes, that's INDEX/MATCH. It looks like

      =INDEX(<dict range>,
   MATCH(<pipe>,<column in dict with pipe sizes>,0),
   MATCH(<schedule>,<headers row in dict with schedules>,0)
)

      but it's easier to explain on some sample if you could provide one.

      • Stephen_Vidulich's avatar
        Stephen_Vidulich
        Copper Contributor
        Sep 20, 2019
        This works too ... nice one Sergei

Resources