Sizing Gas Lines In Excel

[cujonles]

I am trying to make a spreadsheet to when I put in the CFH of a piece of equipment in one cell, it totals up all the CFH and spits out a pipe size in another cell. I set up the gas table just as it appears in the 94' Plumbing Code in the spreadsheet. I used hlookup at first and it doesn't work, for me anyway. The way I did it, it will scan across for the distance and then scan down for the CFH. The only way it find that CFH is to put in a row number. I have been trying to work on this for months now, so I'm finally breaking down and asking, Can anybody help me?

 

[etch]

playing around with it, and not knowing how many different cfc there are, i set up a range of formulae to cover each size, but i have a feeling there is a lot?

use the countif function. this will count up ho wmany part there are with a given criteria, once you have a number cnverting to a pipe size is easy.

 

[MortenA]

Im not sure that i toally get you question but assuming that what you have is a table with some "x" values across and some "y" values down. You then need to match a x and a y value and find the result in the table. The x values a located in the top row and the y values are located in the left most collum of an array (im assuming this)

You need to make a "nested" hlookup and vlookup.

Make the "inner lookup" the vlookup that scans the left collumn and returns the row number that fits in the outer hlookup (a bit tricky). You may need to add an extra "index collum) right of your data that help the vlookup to identify the row. heres an example:

x val=2
y=c

answer=32

formula:
=hlookup(2,a1:f6,vlookup("c",a1:g6,7))
The inner lookup wil respond "4" (not displayed anywhere)

the outer lookup will respond 32 (the result of the formula)

1 2 3 4 5 (comment: right most collumn is index collumn)
a 11 12 13 14 15 2
b 21 22 23 24 25 3
c 31 32 33 34 35 4
d 41 42 43 44 45 5
e 51 52 53 54 55 6

 

[cujonles]

Thank you for your help. We are almost there. The table MortenA has set up is correct. What I am trying to accomplish is to read across as you did to "2", then read down to a specific number (the number might not be an exact number and will have to read the next higher number), then go to the first column and read that number or letter. For instance, "c".

 

[pardal]

Hi Cujonles:

I had made a litle macro to do the same you want to

I made it in metrics units relating
pipe diameter in asa schedules
pipe distance in meters
and flow as calories to handle .
you can replace such values with yours.
Please contact me at
k281969@hotmail.com Pardal