Need formula to calculate circumference from diameter

**Col J** · 08-05-2012, 09:53 PM

Hi there,
I'm trying to create a spreadsheet that will add a certain thickness onto a pipe outside diameter (OD) to return a
circumfrence.
It will be used as a ready reckoner for our guys who spend all day in the workshop cutting banding for pipe insulation.

I have already used the =PI() function to get the Bare circumfrence (BC) from the OD but now i need to to add 50mm thickness insulation(TI) to the OD to get the new Circumfrence.

Example: 150(NB) x PI = 168(OD), 168 + 50 + 50(because twice the insulation thickness needs to be added) = 268, 268(new OD) x PI = 842 New Circumfrence (NC)
It would also be handy if i could add 150mm to the end result.

50mm is used as an example only, we have dozens of different thicknesses and measurements in the example have been rounded off for clarity.
If someone could show me how to create this for the 50mm thickness, i can edit it for other requirments.
Thanks.

**dilipandey** · 08-05-2012, 11:13 PM

Hi Col J

Welcome to the forum.

How you got 150(NB) x PI = 168(OD) as I am getting it as 150 x 3.141592654 = 471.238898 ?

It would be helpful if you can give an example from your workbook where you can put the number manually and I can give a try to obtain them using formulas. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Col J** · 08-06-2012, 12:59 AM

Sorry DILIPandey, my mistake.
I was typing quickly and not reading what i was typing....
The NB is irrelevent, its only for me as different people read the pipe sizes different ways, it will be removed.
It is only the OD that has relevence here. As you point out 168 x PI = 471.23, which is the circumference of the
pipe without insulation, when we add 50mm insulation, the pipe grows in diameter by 100mm therefore - 268 x PI = 842
Plus i need to add 150mm to the end result for wastage. So, 168OD + 100(2x50mm) = 268 x PI + 150 = 992.PIE.xlsx

**dilipandey** · 08-06-2012, 01:21 AM

HI Col J,

So, 168OD + 100(2x50mm) = 268 x PI + 150 = 992

For above, I guess you are referring to row 5 in data.

Try using below formula:-

Formula:

Please Login or Register  to view this content.

I guess 50 in above formula is manual entry.. if so you can type it somewhere in sheet may be in E1 and give that reference in formula. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**CheshireCat** · 08-06-2012, 01:23 AM

How about this:

Col J 2012-08-05.xlsx

**Marcol** · 08-06-2012, 05:49 AM

As a word of caution, your OD sizes are inconsistant with your nominal bore sizes.
To convert inch sizes multiply by 25.4
in B2 then drag Down

Formula:

Please Login or Register  to view this content.

Now check this ...
in F2 then drag Down

Formula:

Please Login or Register  to view this content.

You now see that you have sizes with zero wall thickness and indeed some with negative thickness!

You are also using symbols to produce your fractional sizes, this returns a string value that can't be read as a number
Format Column A as a fraction then in A3 enter 4 1/2 the size will show as a fraction and the formula bar will convert to 4.5

Watch when using CM in your headers, you'll end up cutting 10 times more material than you need, keep the units constant, (best with mm).
See this workbook