I need help with Excel

Copper Contributor

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 couldn't. Here is my issue. There must be a very simple way to do this, but I couldn't succeed by myself. 

I am building a calculation sheet for pipe welding. The user must put in some parameters in order for the sheet to calculate. The problem is, with pipes, most techs work with the nominal diameter and the schedule, but for the calculation, I need it to be converted to actual diameter and thickness. I have a chart that lists the nominal diameter and gives the thickness related to the schedule while also giving the actual diameter.  I would like to be able to have the user select the nominal diameter in a dropdown list, then the schedule in another dropdown list, and have a function that takes these 2 pieces of information to select the real diameter and the thickness in the chart to use them in the calculations. an example of the chart can be found here: https://www.rolledalloys.ca/tools/pipe-chart/

Does somebody have an idea of how to do this without using any macros?

 

Thanks in advance

6 Replies

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

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 

@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.

@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.

This works too ... nice one Sergei

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

 

Thanks a lot!