How to do a Table with 3 variable

[gjjh25]

I am trying to do a table that has 3 variables.

So in the enclosed example the ring size can have the various depths, but it can also have upto 5 pipe sizes per ring per depth. which all have different prices associated with them (the main table)

I am unsure how the best way to set this out would be, if anyone can advise me please?

[AliGW]

I don't see where you have mocked up a few examples and the lookup table seems to be empty. There's not enough to go on here - sorry. Add some more details.

[gjjh25]

I have posted a new table with values in.

But that is the problem, I dont know how to set the table out, when say a 1200 dia at 1.75 - 2m deep can have different prices at .15,.225,.300,.450 for the pipe size.

Its having that extra variable the pipe size that I dont know how to set the table out. If that makes sense?

[AliGW]

Please give us at least ONE example of a calculation that you'd want to make using the table and what the result of that calculation should be. Then we can let you know whether the table will work and how to use it. Without an example, I won't go any further - sorry. Guessing games lead to a lot of wasted time and energy.

[gjjh25]

Sorry, I dont think I have been very clear, it is not a result calculation that i am looking for.

I am looking for advice on how to create the table given that it has three variable columns. eg not just size down the side and depth across the top, I need help on how to incorporate the pipe size within a table too. if this still is no good as a description, please delete and I will have to have a re think.

[AliGW]

OK - so what's the purpose of the table? Unless we know HOW you are going to use it, there's nothing we acn do to advise on how it should be put together.

[gjjh25]

I will use the initial calculation is to get the dia from column A and the Depth from row 49. But then it needs a pipe size too. This bit is not set up correct as a table as I don't know how to incorporate the different sizes related to the different dia. So somewhere i need to fit within the table say, 1200 dia 1.5 - 1.75 deep, but it would also depend on the pipe dia too which could be either .150, .225, .300, .450.

So for each dia in the down column, it would have one of the depth ranges in row 49, but it would also have a pipe dia for the sizes listed too. I just cant get how to have a cell that would reference this? So I cant show a proper example, because I dont know where to put the pipe di references, within the table.

[AliGW]

OK - I am getting closer to knowing what you want!

Ultimately, if a customer came in and wanted to buy some piping, what would you need to ask him? In other words, what details would you need to be able to work out the cost based on the three (?) aspects you've mentioned? When we know that, we'll be able to tell you how to lay out the table.

Does this make any sense? You are clearly trying to create a lookup table that will ultimately be used to automate a price calculation.

[gjjh25]

I would ask them what size dia they require from column A, then I would ask what depth range it would fall into in Row 49, finally I would ask them what was the pipe size going into this.

Yes it is a look up table but it is that final pipe size lookup I dont know how to put into the table.

[AliGW]

Right - what you need to ask yourself is how of those three things would affect the pricing. Explain in words the calculations that you would have to do for one scenario (you can choose the values). Once we know exactly how the three things relate, we can help you structure the best lookup layout.

[gjjh25]

Thanks for you patience.

I have uploaded another version which i further breakdown which I think works as a look up table I have only done the first 2 depths but it would carry on for all depths.

Just looks a bit unwieldly.

Is this understandable and the best way to create this table?

[MrShorty]

It's still not clear to me how you intend to use this 3D lookup table. I'm fairly certain that depth is a continuous variable (it can be any real, positive number up to at least 8.25 and maybe beyond). The other two variables, I'm not sure how they are used.

It seems to me that a spreadsheet -- as a 2D grid -- works well with 1D and 2D data, but 3D (and higher D) data becomes more problematic. Just about any spreadsheet solution that come to mind involve "reducing" the number of dimensions in some way. If I assume that ring diameter and pipe diameter are discrete values (the values listed in your spreadsheet are the only values that these can be and they cannot be values in between the tabulated values), then I think I would lean towards something that combines ring and pipe diameter into one lookup value, then use a simple 2D lookup table like you currently have. Since ring diameter appears to be "large" integers (900 to 300 spaced at 150/300 unit intervals) and pipe diameter tends to be "small" fractions/decimals (ranging from 0.15 to 1.35 -- except for the odd 675 value between 0.6 and 0.75??), here's how I might do it:

1) Combine ring diameter and pipe diameter by summing. Again, I'm assuming that these are discrete numbers (ring diameter cannot be 901 or 1235 and that pipe diameter cannot be 0.2 or 0.5). I will also assume that a pipe diameter currently listed as 675 could be just as easily represented as 0.675. This gives me values along the left side of the lookup table of 900.15, 900.225, 900.3, 900.45, 900.525, 900.6, 900.675,...,1050.15,....
2) Again, I'm assuming depth range is a continuous variable (can assume any positive real value). Across the top of the table, I enter the "boundaries", 0, 1, 1.25, 1.5, 1.75, 2,...
3) Now, when I am given pipe diameter, ring diameter, and depth, I can add the pipe and ring diameter and use an exact match lookup MATCH() function to find the correct row number. An approximate match lookup MATCH() function based on depth can find the column number. Then an INDEX() function can easily extract the result from the body of the 2D lookup table.

I've had to guess at a few different aspects of your problem, so I don't know if that is useful or not. If nothing else, maybe it will give you a framework to better describe how you intend to use these lookup tables and values in real life so we can address the full problem.

[gjjh25]

Thanks for the advice, I will have a try of it.

Regards the depths, they need to fall between the ranges so between 1.5-1.75, 1.75-2.00 etc

[JeteMc]

Based on post #9 and the bottom file in post #1 the following might be what you want.
Cells C21:C23 would display Ring Dia, Depth and Pipe Dia respectively.
Note that this might be accomplished using data validation drop downs, however I wanted to make sure I understand the request before attempting.
Cell F21 displays the cost using: =INDEX(C9:AW18,MATCH(C21,B9:B18,0),AGGREGATE(15,6,(COLUMN(C4:AW4)-COLUMN(B4))/(C4:AW4=C23)/(C7:AW7<=C22)/(C8:AW8>=C22),1))
Let us know if you have any questions.

[gjjh25]

Thank you, Sorry not been on here for a while.

[JeteMc]

Here is the file with data validation drop downs in C21:C23.
The lists for Depth and Pipe Dia. are in columns XFC:XFD
The source for those two drop downs is similar to: =OFFSET($XFD$2,0,0,SUMPRODUCT(--($XFD$2:$XFD$35<>"")))
Let us know if you have any questions.