Mechanical Sizing Spreadsheet

[MechEst]

Hello Experts,

I'm stuck trying to figure out a formula for a pipe sizing spreadsheet that will determine sizing from a couple of different factors. Given a certain system size and pipe length, a size will be the result.

e.g. - if a system (tons) has a certain run length, it will be a given size. I have attached a copy of the file I am trying to put together. Hopefully it's not confusing.

Thanks for any help in advance!

[nick.williams]

I think it would be easier to have 2 columns specifying the min and max for each length, and have the tons on every row. Then you could use:

=INDEX(N6:N22,MATCH(1,(B6=J6:J22)*(D6>=K6:K22)*(D6<=L6:L22),0))

Also copy format to be the same as column N.

See attached sheet for example.

[Pete_UK]

I suggest that you rearrange you table like this:

0 51 81 101 126 151 201 251
1.5 5/8 3/4 3/4 3/4
2 5/8 3/4 7/8 7/8 7/8
2.5 3/4 3/4 7/8 7/8
3 3/4 3/4 7/8
3.5 3/4 7/8 1 1/8 1 1/8 1 1/8 1 1/8 1 1/8
4 3/4 7/8 1 1/8 1 1/8 1 1/8 1 1/8
5 7/8 1 1/8 1 1/8


It probably won't show up very well on the forum, so I'll try to edit it better after I have posted, but on the top row (which starts in Q5) you have the start point of each range of lengths, and in the first column (starting in P6) you have the start of each tonnage range, and then you can use this formula in cell F6:

=INDEX(Q6:X12,MATCH(B6,P6:P12),MATCH(D6,Q5:X5))

(format as a fraction).

You could put N/A in the blank cells in the table where the tonnage is exceeded.

Hope this helps.

Pete

EDIT: It is easier if I attach the file, as below:

[MechEst]

Thanks Pete! That work great as well. Gives me an idea for a different sheet!

[Pete_UK]

Glad to help, and thanks for the rep. As with the other thread, if you intend to copy it down you will need to make the ranges absolute, i.e.:

=INDEX($Q$6:$X$12,MATCH(B6,$P$6:$P$12),MATCH(D6,$Q$5:$X$5))

Hope this helps.

Pete

[MechEst]

Nick -

Thanks for your fast reply, the formula works great! However, when I try to copy it down for a full sheet, the reference cells change. I tried to protect them with "$", but got an error as a result. What am I doing wrong?

Thanks again!

Dave

[nick.williams]

I guess you missed one when you were putting the $ in, I often do that. Try:

=INDEX($N$6:$N$22,MATCH(1,(B6=$J$6:$J$22)*(D6>=$K$6:$K$22)*(D6<=$L$6:$L$22),0))

[MechEst]

Thanks Nick!