Calculate amount based on circumference / divide total in 2 locations

**JACKIECOTHRON** · 04-24-2012, 10:27 AM

I am a novice Excel user and i have ran into a snag trying to figure this out on my own. Any solutions or ideas would be appreciated. My goal is to create a spreadsheet that will calculate the amount of insulation I will need in square footage based on O.D.'s. I have attached the spreadsheet that shows the different ranges of insulation thickness. Another thing is the amount will have to be divided into (2) thickness, 10MM and 5MM. Example: 4.5" O.D. x 15MM will be achieved by taking the 4.5 O.D. and adding .393 thickness twice, which is the thickness of 10MM in inches and then taking that O.D., 5.286 and adding .197 thicness twice, which is the thickness of 5MM in inches. From this total the calculation for the amount of 10MM and 5MM will need to be done. I will show the quantities in (2) locations for different part numbers in squaure footage. As you can see from the spreadsheet the O.D.'s and circumferences of each range needed are already entered. I hope I have explained this where you can understand. An example is located on the AEROGEL TOTAL tabs at the bottom of the sheet of what I am trying to achieve through a spreadsheet.

thanks,
Jackie

**StevenM** · 04-24-2012, 12:22 PM

Jackie,

You might get an answer faster if you describe what you want done in terms of cells.
In sheet 1 cell C4 you want a number there based on what information? And where are we to find this information (which cells?)?

**xladept** · 04-24-2012, 02:33 PM

Jackie,

A millimeter is approximately .0393 inches, so you could just add 2 x .03937 X the thickness in millimeters to the O.D.
but aren't you looking for the circumference? You'd need to multiply that by Pi.

C=O.D. + 2 x .03937 X the thickness in millimeters x Pi

The effective Circumference of your 4.5" + 15mm wrapped pipe would be: (4.5"+1.18") x 3.1416 = 17.8"

XLAdept

**JACKIECOTHRON** · 04-25-2012, 07:36 AM

Hi Steven,

I knew I didn't make myself clear. It is hard to describe what I am wanting. Let me try again.

I have (2) seperate thickness of insulation, 5MM thick with part number 8013904 and 10MM with part number 8013903 shown on sheet 'A'.

Whenever a job calls for 4" pipe with 25MM insulation we instruct the shop to put on the fist layer of insulation as 4"x10MM. When looking at this spreadsheet on sheet AEROGEL AMT sheet, you will see at cell address B12 it shows 4.5" as the O.D. of 4" pipe. Then looking at D12 you will see that 5.288 listed there is the sum of 4.5"+.393"+.393 which is the O.D. of 4"x10MM. Knowing 5.288 is the sum of 4"x10mm I can calculate the sum of 5.288"x10MM for the second layer, this is shown in F12 and this would be 4"x20mm. Now this 6.076 in F12 has to be added to .197" twice for the last layer of insulation 5MM. Now after the spreadsheet calculates the amount of insulation needed for 4"x25MM, the sum will have to be distributed correctly into the seperate part numbers listed in A, with the first (2) wraps being the 10MM part number and the last wrap being the 5MM part number.

The AEROGEL AMT sheet shows the O.D.'s and circumferences of all of the scenerios I will need based on pipe O.D.'s and insulation thicknesses and the application of the 10MM always being the first layer in a multilayered application.

The AEROGEL FT sheet is only shown to enter in the linear footage needed for each application and should be multiplied to the amount calculated in the AEROGEL AMT sheet.

The AEROGEL TOTAL has no reference.

Sheet A shows the total sum of each thickness of insulation and should be linked to the other worksheets.

I hope this is enough information to describe what I am trying to achieve from this spreadsheet. Thank you for you reply.

Jackie

**JACKIECOTHRON** · 04-25-2012, 07:39 AM

Hi XLAdept,

I knew I didn't make myself clear. It is hard to describe what I am wanting. Let me try again.

All the calculations you are referring to have already been done in the spreadsheet. Please read the following so you understand what I am trying to do.

I have (2) seperate thickness of insulation, 5MM thick with part number 8013904 and 10MM with part number 8013903 shown on sheet 'A'.

Whenever a job calls for 4" pipe with 25MM insulation we instruct the shop to put on the fist layer of insulation as 4"x10MM. When looking at this spreadsheet on sheet AEROGEL AMT sheet, you will see at cell address B12 it shows 4.5" as the O.D. of 4" pipe. Then looking at D12 you will see that 5.288 listed there is the sum of 4.5"+.393"+.393 which is the O.D. of 4"x10MM. Knowing 5.288 is the sum of 4"x10mm I can calculate the sum of 5.288"x10MM for the second layer, this is shown in F12 and this would be 4"x20mm. Now this 6.076 in F12 has to be added to .197" twice for the last layer of insulation 5MM. Now after the spreadsheet calculates the amount of insulation needed for 4"x25MM, the sum will have to be distributed correctly into the seperate part numbers listed in A, with the first (2) wraps being the 10MM part number and the last wrap being the 5MM part number.

The AEROGEL AMT sheet shows the O.D.'s and circumferences of all of the scenerios I will need based on pipe O.D.'s and insulation thicknesses and the application of the 10MM always being the first layer in a multilayered application.

The AEROGEL FT sheet is only shown to enter in the linear footage needed for each application and should be multiplied to the amount calculated in the AEROGEL AMT sheet.

The AEROGEL TOTAL has no reference.

Sheet A shows the total sum of each thickness of insulation and should be linked to the other worksheets.

I hope this is enough information to describe what I am trying to achieve from this spreadsheet. Thank you for you reply.

Jackie