Auto calculation from ductwork in ft to lbs

[Saldavid]

Good afternoon All,

I was hoping someone can help me at expediting my quoting process with the help of excel fomulas.

Excel version working on: Microsoft office home & business 2016

Brief explanation: I need to convert metal duct work of various project into lbs and metal gage so I can then provide a dollar amount to it.

Anyone can shine a light as to which formulas would work?

See attachment to get a better idea.

Thanks a lot.

[p24leclerc]

See attached workbook.
Your table's values are not quite the same as if weight is calculated based on dimensions and density.
I put the 2 formulas in the sheet.
You take the one you want.
Regards

[FlameRetired]

@ Pierrre,

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

Please consider also posting the formula(s).

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Saldavid]

Thank you, Pierre.

I tried to use your formulas, but it is not choosing the right values therefore it is giving me wrong answers.

Both formulas dont provide the right numbers:
=INDEX('Duct calculator'!B7:F115,MATCH(A3+B3,'Duct calculator'!A7:A115,0),COUNTIF('Duct calculator'!B4:E5,"<"&A3)+1)*C3/12
=INDEX('Duct calculator'!B2:F2,1,COUNTIF('Duct calculator'!B4:E5,"<"&'Metal Duct calc'!A3)+1)

These are the values that should populate

WIDTH LENGTH TOTAL DUCT LENGTH LBS GAGE DUCT INSULATION (SQF)
40 20 15 225.00 22 160.03
20 20 5 43.00 24 37.2
65 30 10 276.00 20 166.67


Please keep in mind that a duct could be 40x20 or 20x40 but the formula changes values when the order is altered. Not necessarily the longest side will be in the 1st column.



Also to find duct insulation (SQF) I need to first find the value corresponding in the duct calculator tab and then divide total lbs by it.

Thank again for all the help!

[p24leclerc]

OK, you should have told us that all dimensions were in FEET because I thought it was in inches.
So the new formula for the weight is:

Please Login or Register  to view this content.

And the new one for the duct insulation would be:

Please Login or Register  to view this content.

[Saldavid]

Thanks for the help. Works great!