{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Cover Sheet","Revision Record","WES Preface","Calculation Sheet"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":6,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover Sheet","index":0,"n_rows":44,"n_cols":8,"rows_kept":44,"cut":false,"hidden":false,"merged":[[16,6,22,9],[4,6,11,9],[5,0,5,5],[0,0,0,5],[1,0,1,5],[2,0,2,5],[3,0,3,5],[4,0,4,5],[17,0,17,5],[6,0,6,5],[7,0,7,5],[8,0,8,5],[9,0,9,5],[10,0,10,5],[11,0,11,5],[12,0,12,5],[13,0,13,5],[14,0,14,5],[15,0,15,5],[16,0,16,5],[29,0,29,5],[18,0,18,5],[19,0,19,5],[20,0,20,5],[21,0,21,5],[22,0,22,5],[23,0,23,5],[24,0,24,5],[25,0,25,5],[26,0,26,5],[27,0,27,5],[28,0,28,5],[36,0,36,5],[37,0,37,5],[30,0,30,5],[31,0,31,5],[32,0,32,5],[33,0,33,5],[34,0,34,5],[35,0,35,5]]}
[null,null,null,null,null,null,"WES Disclaimer"]
[]
[null,null,null,null,null,null,"Copyright © Wave Energy Scotland Limited 2019 "]
[]
[null,null,null,null,null,null,"All rights reserved. No part of this work may be modified, reproduced, stored in a retrieval system of any nature, or transmitted, in any form or by any means, graphic, electronic or mechanical, including photocopying and recording, or used for any purpose other than its designated purpose without the prior written permission of Wave Energy Scotland Limited, the copyright owner. If any unauthorised acts are carried out in relation to this copyright work, a civil claim for damages may be made and/or a criminal prosecution may result. "]
[]
[]
[]
[]
[]
[]
[]
["OYSTER 800 PROJECT"]
[]
[null,null,null,null,null,null,"Disclaimer "]
[]
["Bolt Torque Calculator",null,null,null,null,null,"This document (including any enclosures and attachments) was commissioned by and prepared for the use and benefit of Aquamarine Power Limited (“APL”), and solely for the purpose for which it was provided. Subsequently, all intellectual property rights in the document have been transferred to Wave Energy Scotland Limited (“WES”), therefore no representation, warranty or undertaking (express or implied) is made, and no responsibility is accepted as to the adequacy, accuracy or completeness of this document or any of its contents. WES does not assume any liability with respect to use of or damages resulting from the use of any information disclosed in this document. The statements and opinions contained in this document are those of the author and do not necessarily reflect those of WES. Additional reports, documents and data files referenced here may not be publicly available. "]
[]
[]
["OY800-CAL-0099\n"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Confidentiality Status:           Commercial in Confidence"]
[]
[null,null,null,null,null,null,"To open Calculation Sheet:"]
["Document Control:"]
[null,null,null,null,null,null,null,"1) Enable Macros"]
["SIGNATURES"]
[null,null,null,null,null,null,null,"2) Click button below:"]
[]
[]
["X1","2019-07-01","WES Release"]
["A1","2014-05-19","For Internal Use"]
["Rev","Date","Purpose of issue","Prepared by","Checked By","Approved By"]

{"sheet":"Revision Record","index":1,"n_rows":15,"n_cols":4,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Revision Record Sheet"]
[]
["Rev","Date Issued","Revision Description","Update / Amendment Details"]
["A1","2014-05-19","Formalised engineering document for issue throughout company"]
[]
["Rev","Date Issued","Revision Description","Update / Amendment Details"]
["X1","2019-07-01","WES Release","Copyright/Disclaimer added. Formatting tidied."]
[]
["Rev","Date Issued","Revision Description","Update / Amendment Details"]
[]
[]
["Rev","Date Issued","Revision Description","Update / Amendment Details"]
[]
[]
["Rev","Date Issued","Revision Description","Update / Amendment Details"]

{"sheet":"WES Preface","index":2,"n_rows":31,"n_cols":2,"rows_kept":31,"cut":false,"hidden":false,"merged":[[14,1,14,3],[0,1,0,3],[2,1,2,3],[3,1,6,10],[8,1,9,10],[11,1,12,10],[27,1,28,10],[30,1,30,10],[15,1,16,10],[18,1,19,10],[21,1,22,10],[24,1,25,10]]}
[null,"WES Preface"]
[]
[null,"General Background"]
[null,"Aquamarine Power Ltd (APL) was a wave energy company responsible for developing a wave energy converter called Oyster. The Oyster system consisted of a Wave Energy Converter (WEC) located in shallow water close to the shore, with a bottom-hinged flap which oscillated due to wave action. Double acting pistons on each side of the WEC pumped water through a high-pressure pipeline back to shore, where high pressure water drove a Pelton wheel turbine connected to an electrical generator. The flow from the Pelton wheel discharged to a header tank and returned to the WEC via a low-pressure return pipeline. "]
[]
[]
[]
[]
[null,"APL deployed a full-scale 315 kW Oyster 1 system at the European Marine Energy Centre (EMEC) in August 2009, followed by a second-generation machine rated at 800kW, Oyster 800, in August 2011. "]
[]
[]
[null,"APL ceased trading in November 2015, and the intellectual property was acquired by Wave Energy Scotland (WES), who propose to share relevant documents and information acquired with developers in the WES programme and the wider sector. "]
[]
[]
[null,"Background to this Document"]
[null,"One area of concern at APL was the long-term integrity of mechanical connections on Oyster 800, a device which included a number of bolted and flanged connections of various sizes and functions. Operational experience shows that mechanical connections are prone to both corrosion and loosening, caused primarily by vibration.\n\n"]
[]
[]
[null,"This document was produced by APL in 2014 to provide a single, standardised point of reference for bolted connection design calculations for the Oyster 800 device. The spreadsheet included data on a large variety of fastener types, materials, locking methods, lubricants, and tightening methodologies. "]
[]
[]
[null,"Outputs from the spreadsheet include required fastener tension, % yield, force range expected from tightening method, preload, bearing stress, and an indication if the design satisfies design criteria."]
[]
[]
[null,"This document was informed by experience gained through training, design activities, and practical implementation of bolted designs during the offshore maintenance campaigns on Oyster 800 in the summers of 2013 and 2014."]
[]
[]
[null,"The data for fasteners, frictional coefficients and tightening factors was obtained from manufacturer data, standards, and experiment, and was valid as of summer 2014. It is up to the user to be satisfied on the current accuracy of the data and standards referenced."]
[]
[]
[null,"The spreadsheet is intended to be used as a design aid, and no liability is assumed by WES for the use of this calculation sheet. "]

{"sheet":"Calculation Sheet","index":3,"n_rows":110,"n_cols":33,"rows_kept":110,"cut":false,"hidden":true,"merged":[[55,8,55,12],[56,8,56,12],[7,21,7,22],[7,24,8,24],[52,7,53,7],[52,8,53,12],[54,8,54,12],[31,27,31,28],[54,21,54,24],[55,16,56,16],[55,17,56,20],[55,23,55,24],[63,16,67,16],[63,25,67,25],[62,17,62,20],[57,23,58,23],[57,22,58,22],[57,21,58,21],[57,17,58,20],[62,21,62,25],[63,24,67,24],[63,23,67,23],[63,21,67,21],[63,20,67,20],[63,19,67,19],[63,18,67,18],[63,17,67,17],[63,22,67,22],[59,17,59,20],[60,17,60,20],[55,21,55,22],[0,16,1,23],[13,30,13,31],[7,27,8,28],[7,30,8,31],[36,30,36,32],[18,30,18,31],[7,16,7,20],[27,30,27,31],[57,24,58,24],[42,30,42,31],[7,23,8,23],[57,16,58,16],[7,25,8,25]]}
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Tables used to populate drop-down lists"]
[null,null,null,null,null,null,"User data input"]
[]
[null,null,null,null,null,null,"Intermediate calculation (no user input required)"]
[]
[null,null,null,null,null,null,"Data output (no user input required)"]
[]
[null,null,null,null,null,"OY800-CAL-0099",null,null,null,null,null,null,null,null,null,null,"Bolt sizes (BS-3692) (ANSI B1.1)",null,null,null,null,"Nordlock Washer Sizes",null,"External thread shear area (per mm engagement) (mm^2)","Internal thread shear area (per mm engagement) (mm^2)","Prevailing Torque due to Nylok",null,"Friction Coeficients",null,null,"Calculation Material Properties"]
[null,null,null,null,null,"Bolt Torque Calculator",null,null,null,null,null,null,null,null,null,null,"THREAD","PITCH (mm)","Tensile Stress Area (mm^2)","WIDTH ACROSS FLATS (mm)","NOMINAL SIZE (mm)","Nordlock OD (mm)","Nordlock SP OD (mm)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M3",0.5,{"$f":"((U10-0.938194*R10)^2)*(PI()/4)","v":5.0308443700916898},5.5,3,7,"-",3.91,5.54,0,null,"Lubricant","coefficient ",null,"Joint Yield Stress",{"$f":"VLOOKUP('Calculation Sheet'!F73,Q69:U80,5,FALSE)","v":{"$error":"#N/A"}},"Mpa"]
[null,null,"The following equation is used to calculate the torque on this spreadsheet:",null,null,null,null,null,null,null,null,null,null,null,null,null,"M4",0.7,8.7787216138708395,7,4,7.6,9,5.47,7.77,0,null,"Steel Nordlock",0.16,null,"Modulus of elasticity",{"$f":"VLOOKUP('Calculation Sheet'!F26,'Calculation Sheet'!Q69:Y80,9,FALSE)","v":{"$error":"#N/A"}},"pa"]
[null,null,"T=F[(P/2π) + (0.577d₂μthread) + μhead(Dₑ/2)]",null,null,null,null,null,null,null,null,null,null,null,null,null,"M5",0.8,14.182548117450365,8,5,9,10.8,7.08,9.99,0,null,"316 Nordlock",0.14000000000000001,null,"Nominal thread size",{"$f":"VLOOKUP('Calculation Sheet'!F22,'Calculation Sheet'!Q10:U53,5,FALSE)","v":{"$error":"#N/A"}},"mm"]
[null,null,"Ref. Handbook for the Training Course on Bolting Technology and the Analysis of Bolted Joints",null,null,null,null,null,null,null,null,null,null,null,null,null,"M6",1,20.123377480366759,10,6,10.8,13.5,8.65,12.2,0,null,"254 SMO Nordlock",0.14000000000000001]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M8",1.25,36.608543273765157,13,8,13.5,16.600000000000001,12.2,16.8,0,null,"None",0.2,null,"Variables from Torque Equation"]
[null,null,"Created","Date","Checked","Date",null,null,null,null,null,null,null,null,null,null,"M10",1.5,57.989596901845204,17,10,16.600000000000001,21,15.6,21.5,{"$f":"IF(U15<14,5,0.2146*U15^2 - 5.9389*U15 + 48)","v":5},null,"Molykote 1000",0.13,null,"F=",{"$f":"'Calculation Sheet'!F35*'Calculation Sheet'!K26*'Calculation Sheet'!K23","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M12",1.75,84.26653836460693,19,12,19.5,25.4,19,26.1,{"$f":"IF(U16<14,5,0.2146*U16^2 - 5.9389*U16 + 48)","v":5},null,"Millers Black Moly Paste",0.09,null,"d₂=",{"$f":"'Calculation Sheet'!AF12-(0.6495*'Calculation Sheet'!K22)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M14",2,115.4393676620503,22,14,23,30.7,22.4,31,{"$f":"IF(U17<14,5,0.2146*U17^2 - 5.9389*U17 + 48)","v":6.9170000000000087},null,"Machine Oil",0.17,null,"Dₑ=",{"$f":"('Calculation Sheet'!K24+'Calculation Sheet'!F28)/2","v":{"$error":"#N/A"}}]
[null,null,"Data Inputed","Date","Checked","Date",null,null,null,null,null,null,null,null,null,null,"M16",2,156.66841070981314,24,16,25.4,30.7,26.1,35.6,7.9151999999999987,null,"Loctite 243",0.13]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M18",2.5,192.47268976098206,27,18,29,34.5,"-","-",10.630199999999988,null,"Loctite 2400",0.22,null,"Max. Combined  Stress Intermediate Calc."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M20",2.5,244.79439173408306,30,20,30.7,39,33.299999999999997,45.4,15.061999999999998,null,"Loctite 278",0.31,null,"bolt/washer friction force",{"$f":"'Calculation Sheet'!F44*'Calculation Sheet'!K33","v":{"$error":"#N/A"}},"N"]
[null,null,"TARGET TORQUE ",null,null,null,null,null,null,null,null,null,null,null,null,null,"M22",2.5,303.39927901436369,32,22,34.5,42,37,50,21.210600000000014,null,"PTFE unlubricated",0.11,null,"Max. Resultant friction torque",{"$f":"AF20*'Calculation Sheet'!AF17/2/1000","v":{"$error":"#N/A"}},"Nm"]
[null,null,"Bolt/Stud Size",null,null,null,null,"Pitch",null,null,{"$f":"VLOOKUP(F22,'Calculation Sheet'!Q10:U53,2,FALSE)","v":{"$error":"#N/A"}},"mm",null,null,null,null,"M24",3,352.50392409707956,36,24,39,48.5,40.5,55,29.075999999999993,null,"User Defined 1","-",null,"Max possible torque",{"$f":"'Calculation Sheet'!F39*('Calculation Sheet'!K42/(('Calculation Sheet'!K42-1)/2+1))","v":{"$error":"#N/A"}},"Nm"]
[null,null,null,null,null,null,null,"Tensile Stress Area",null,null,{"$f":"VLOOKUP(F22,'Calculation Sheet'!Q10:U53,3,FALSE)","v":{"$error":"#N/A"}},"mm²",null,null,null,null,"M27",3,459.40643789569998,41,27,42,48.5,46.2,62,44.093099999999993,null,"User Defined 2","-",null,"effective radius",{"$f":"SQRT('Calculation Sheet'!K23/PI())","v":{"$error":"#N/A"}},"mm"]
[null,null,null,null,null,null,null,"Width Across Flats",null,null,{"$f":"VLOOKUP(F22,'Calculation Sheet'!Q10:U53,4,FALSE)","v":{"$error":"#N/A"}},"mm",null,null,null,null,"M30",3.5,{"$f":"((U24-0.938194*R24)^2)*(PI()/4)","v":560.58721292975974},46,30,47,58.5,51.6,69.599999999999994,62.973000000000013,null,null,null,null,"Polar Second moment of area",{"$f":"PI()*POWER(AF23,4)/2","v":{"$error":"#N/A"}},"mm^4"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M33",3.5,693.55349307715687,50,33,48.5,58.5,"-","-",85.715700000000027,null,"Tightening Method","Tightening Factor",null,"Torsional Stress",{"$f":"(AF22-'Calculation Sheet'!AF21)*1000*AF23/AF24","v":{"$error":"#N/A"}},"N/mm²"]
[null,null,"Bolt/Stud Material",null,null,null,null,"Proportional Limit Stress",null,null,{"$f":"VLOOKUP(F26,'Calculation Sheet'!Q69:U80,4,FALSE)","v":{"$error":"#N/A"}},"N/mm²",null,null,null,null,"M36",4,816.72255625902255,55,36,55,63,63.1,84.1,112.32119999999998,null,"Torque wrench",1.8,null,"Max tensile stress",{"$f":"'Calculation Sheet'!F44/'Calculation Sheet'!K23","v":{"$error":"#N/A"}},"N/mm²"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M42",4.5,1120.9099540848674,65,42,63,70,74.3,99.2,177.12060000000002,null,"Torque wrench Hydraulic",1.6]
[null,null,"Clearance Hole Size",null,null,null,null,null,null,null,null,null,null,null,null,null,"M48",5,1473.1494064072954,75,48,75,"-",85.8,114,257.37119999999999,null,"Hydratight bolt tensioner",1.2,null,"Bearing Stress Calculation Intermediate Calc."]
[null,null,null,null,null,"Enter Value (mm)",null,null,null,null,null,null,null,null,null,null,"M52",5,1757.8344379858545,80,52,80,"-","-","-","-",null,"Impact wrench",4,null,"None (Bolt head width A/F)",{"$f":"'Calculation Sheet'!K24","v":{"$error":"#N/A"}},"mm"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M56",5.5,2030.0176805945136,85,56,85,"-",101,134,"-",null,"User Defined 1 ","-",null,"Nordlock Washer",{"$f":"VLOOKUP('Calculation Sheet'!F22,Q10:W53,6,FALSE)","v":{"$error":"#N/A"}},"mm"]
[null,null,"Thread Lubricant Used",null,null,null,null,"Thread Coefficient of Friction",null,null,{"$f":"SUM(VLOOKUP(F31,'Calculation Sheet'!AB14:AC23,2,FALSE))","v":{"$error":"#N/A"}},null,null,null,null,null,"M64",6,2675.9728830505283,95,64,95,"-",117,154,"-",null,null,null,null,"Nordlock SP Washer",{"$f":"VLOOKUP('Calculation Sheet'!F22,Q10:W53,7,FALSE)","v":{"$error":"#N/A"}},"mm"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M72",6,3459.7479237539437,105,72,105,"-",133,173,"-",null,"Nylok nut selection",null,null,"Washer Bearing Diameter",{"$f":"VLOOKUP('Calculation Sheet'!F69,AE29:AF31,2,FALSE)","v":{"$error":"#N/A"}},"mm"]
[null,null,"Head Washer/Lubricant Used",null,null,null,null,"Head Coefficient of Friction",null,null,{"$f":"VLOOKUP(F33,'Calculation Sheet'!AB11:AC23,2,FALSE )","v":{"$error":"#N/A"}},null,null,null,null,null,"M80",6,4344.0539293722322,115,80,115,"-",149,193,"-",null,"Yes",1,null,null,{"$f":"2*ACOS('Calculation Sheet'!F28/'Calculation Sheet'!AF32)","v":{"$error":"#N/A"}},"radians"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M90",6,5590.8081058066336,130,90,130,"-",169,217,"-",null,"No",0,null,"Hole",{"$f":"(PI()/4)*(POWER('Calculation Sheet'!AF32,2)-POWER('Calculation Sheet'!F28,2))","v":{"$error":"#N/A"}},"mm^2"]
[null,null,"Percentage Factor (of yield stress)",null,null,null,null,"*should be 0.5 for standard ANSI flange fastening",null,null,null,null,null,null,null,null,"M100",6,6994.641914920524,145,100,145,"-",189,241,"-",null,null,{"$f":"VLOOKUP(F22,Q10:Z53,10,FALSE)*VLOOKUP(#REF!,AB33:AC34,2,FALSE)","v":{"$error":"#N/A"}},null,"Slot ",{"$f":"(POWER('Calculation Sheet'!AF32,2)/4)*('Calculation Sheet'!AF33-SIN('Calculation Sheet'!AF33))","v":{"$error":"#N/A"}},"mm^2"]
[null,null,null,null,null,"Enter Value",null,null,null,null,null,null,null,null,null,null,"8-32",0.79400000000000004,{"$f":"((U36-0.938194*R36)^2)*(PI()/4)","v":9.189952052911865},6.8579999999999997,4.1656000000000004,7.6,9,"-","-","-"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1/4\" UNC",1.27,{"$f":"((U37-0.938194*R37)^2)*(PI()/4)","v":20.899489446124345},11.1,6.35,11.5,13.5,9.35,13.69,"-",null,null,null,null,"Embedding Loss Calculation"]
[null,null,"Target Force",null,"=",{"$f":"F35*K26*K23","v":{"$error":"#N/A"}},"N","Note: If Nylok nuts are being used, prevailing torque needs to ",null,null,null,null,null,null,null,null,"5/16\" UNC",1.41,{"$f":"((U38-0.938194*R38)^2)*(PI()/4)","v":33.975425704586094},12.7,7.9,13.5,16.600000000000001,11.94,17.32,"-",null,null,null,null,null,"Head/Nut Surface (μm)","Interfaces (μm)"]
[null,null,"Torque Required ",null,"=",{"$f":"(F38*((K22/6.28)+(0.577*('Calculation Sheet'!AF12-(0.6495*K22))*K31)+(K33*((K24+F28)/4)))/1000)","v":{"$error":"#N/A"}},"Nm","be added. See OY800-TN-0188.",null,null,null,null,null,null,null,null,"3/8\" UNC",1.59,50.369479430293431,14.3,9.5,16.600000000000001,21,14.63,21.03,"-",null,null,null,null,"Tension/Compression",{"$f":"VLOOKUP('Calculation Sheet'!F53,'Calculation Sheet'!Q58:Y61,6,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP('Calculation Sheet'!F53,'Calculation Sheet'!Q58:Y61,8,FALSE)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"7/16\" UNC",1.81,69.425379086088739,15.9,11.1,18.5,"-",17.2,24.92,"-",null,null,null,null,"Shear",{"$f":"VLOOKUP('Calculation Sheet'!F53,'Calculation Sheet'!Q58:Y61,7,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP('Calculation Sheet'!F53,'Calculation Sheet'!Q58:Y61,9,FALSE)","v":{"$error":"#N/A"}}]
[null,null,"TORQUING TOLERANCE",null,null,null,null,null,null,null,null,null,null,null,null,null,"1/2\" UNC",1.95,92.809120262401365,19.100000000000001,12.7,19.5,25.4,19.79,28.45,"-",null,null,null,null,null,{"$f":"VLOOKUP('Calculation Sheet'!F55,AE39:AG40,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP('Calculation Sheet'!F55,AE39:AG40,3,FALSE)","v":{"$error":"#N/A"}}]
[null,null,"Tightening Method",null,null,null,null,"Tightening Factor",null,null,{"$f":"VLOOKUP(F42,'Calculation Sheet'!AB26:AC30,2,FALSE )","v":{"$error":"#N/A"}},null,null,null,null,null,"5/8\" UNC",2.31,148.1174722687669,23.8,15.9,25.4,30.7,25.35,36.07,"-"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"3/4\" UNC",2.54,219.48553478242056,28.6,19.100000000000001,30.7,39,30.73,43.69,"-",null,null,null,null,"Thread Stripping Intermediate Calculation"]
[null,null,"Maximum Possible Tension Force",null,null,{"$f":"K42*F46","v":{"$error":"#N/A"}},"N",null,null,null,null,null,null,null,null,null,"7/8\" UNC",2.82,300.31298759315251,33.299999999999997,22.2,34.5,42,36.32,51.56,"-",null,null,null,null,"Thread size",{"$f":"VLOOKUP('Calculation Sheet'!F95,Q10:W53,5,FALSE)","v":{"$error":"#N/A"}},"mm"]
[null,null,"Maximum Possible % of yield stress",null,null,{"$f":"((F44/K23)/K26)","v":{"$error":"#N/A"}},{"$f":"IF(F45<81%,\"OK\",\"FAIL\")","v":{"$error":"#N/A"}},"*Should not exceed 80% yield stress",null,null,null,null,null,null,null,null,"1\" UNC",3.18,394.66367953723699,38.1,25.4,39,48.5,42.16,59.18,"-",null,null,null,null,"External thread",{"$f":"AF49*'Calculation Sheet'!K95","v":{"$error":"#N/A"}},"N/mm"]
[null,null,"Minimum Possible Tension Force",null,null,{"$f":"(2*F38)/(K42+1)","v":{"$error":"#N/A"}},"N",null,null,null,null,null,null,null,null,null,"1 1/8\" UNC",3.63,498.53585351941797,42.9,28.6,47,58.5,47.75,67.31,"-",null,null,null,null,"Internal Thread",{"$f":"AF50*'Calculation Sheet'!K96","v":{"$error":"#N/A"}},"N/mm"]
[null,null,"Minimum Possible % of yield stress",null,null,{"$f":"((F46/K23)/K26)","v":{"$error":"#N/A"}},null,null,null,null,null,null,null,null,null,null,"1 1/4\" UNC",3.63,633.21897556120871,47.6,31.8,48.5,58.5,53.59,74.680000000000007,"-",null,null,null,null,"shear area of ext thread",{"$f":"'Calculation Sheet'!F109*'Calculation Sheet'!K95","v":{"$error":"#N/A"}},"mm²"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1 3/8\" UNC",4.2300000000000004,751.43278896322852,52.4,34.9,55,63,59.44,83.06,"-",null,null,null,null,"shear area of int thread",{"$f":"'Calculation Sheet'!F109*'Calculation Sheet'!K96","v":{"$error":"#N/A"}},"mm²"]
[null,null,"Max Combined Tensile & Torsional Stress",null,null,{"$f":"SQRT((POWER('Calculation Sheet'!AF26,2))+(3*POWER('Calculation Sheet'!AF25,2)))","v":{"$error":"#N/A"}},"N/mm²",null,null,null,null,null,null,null,null,null,"1 1/2\" UNC",4.2300000000000004,914.95363851028435,57.2,38.1,58.5,"-",65.53,90.68,"-",null,null,null,null,"Bolt thread shear strength",{"$f":"'Calculation Sheet'!K99*'Calculation Sheet'!K100","v":{"$error":"#N/A"}},"N/mm²"]
[null,null,"% of Yield Stress",null,null,{"$f":"F49/K26","v":{"$error":"#N/A"}},{"$f":"IF(F50<95%,\"OK\",\"FAIL\")","v":{"$error":"#N/A"}},"*Should not exceed 95% yield stress",null,null,null,null,null,null,null,null,"1 3/4\" UNC",5.08,1239.9777673459582,41.3,44.5,70,"-",77.22,106.68,"-",null,null,null,null,"Int thread shear strength",{"$f":"'Calculation Sheet'!K105*'Calculation Sheet'!K106","v":{"$error":"#N/A"}},"N/mm²"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2\" UNC",5.64,1626.5842462405046,76.2,50.8,80,"-",null,null,"-",null,null,null,null,"Bolt thread stripping force",{"$f":"AF47*AF49","v":{"$error":"#N/A"}},"N"]
[null,null,"BOLT PRELOAD LOSS",null,null,null,null,null,null,null,null,null,null,null,null,null,"User Defined 1","-",{"$error":"#VALUE!"},"-","-","-","-","-","-","-",null,null,null,null,"Int thread stripping force",{"$f":"AF48*AF50","v":{"$error":"#N/A"}},"N"]
[null,null,"Bolting Surface Material Roughness",null,null,null,null,"Average Roughness Height Rz","Typical Manufacturing Process",null,null,null,null,null,null,null,"User Defined 2","-",{"$error":"#VALUE!"},"-","-","-","-","-","-","-",null,null,null,null,"Force needed to break bolt",{"$f":"'Calculation Sheet'!K97*'Calculation Sheet'!K101","v":{"$error":"#N/A"}},"N"]
[null,null,null,null,null,"See table for reference",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Weakest Thread",{"$f":"IF(AF46<AF45,\"INTERNAL THREAD\",\"BOLT THREAD\")","v":{"$error":"#N/A"}}]
[null,null,"Main Type of Applied Load Acting",null,null,null,null,"< 10 μm","Die casting, cold rolling, drawing, polishing, lapping, honing, grinding, smooth milled or turned surface",null,null,null,null,null,null,null,null,null,null,null,null,"Guide values for the amount of embedding (μm) [VDl 2230]",null,null,null,null,null,null,null,null,"Minimum length",{"$f":"IF(AF54=\"INTERNAL THREAD\",AF53/(AF50*'Calculation Sheet'!K96),AF53/(AF49*'Calculation Sheet'!K95))","v":{"$error":"#N/A"}},"mm"]
[null,null,null,null,null,null,null,"10 μm to < 40 μm","Investment casting, forging, milling, planing, shaping, sawing, turning",null,null,null,null,null,null,null,"Average Roughness Height Rz","Typical Manufacturing Process",null,null,null,"Head",null,"Interface"]
[null,null,"No. of Head/Nut Bearing Surfaces",null,null,null,null,"40 μm to <160 μm","Hot rolling, sand casting, flame cutting",null,null,null,null,null,null,null,null,null,null,null,null,"Tension","Shear","Tension","Shear"]
[null,null,null,null,null,"Enter Value",null,null,null,null,null,null,null,null,null,null,"< 10 μm","Die casting, cold rolling, drawing, polishing, lapping, honing, grinding, smooth milled or turned surface",null,null,null,2.5,3,1.5,2]
[null,null,"No. of Interfaces Within Joint (inc washers)",null,null,null,null,"Embedding Loss",null,null,null,{"$f":"3+(F57*'Calculation Sheet'!AF41)+(F59*'Calculation Sheet'!AG41)","v":{"$error":"#N/A"}},"μm"]
[null,null,null,null,null,"Enter Value",null,null,null,null,null,null,null,null,null,null,"10 μm to < 40 μm","Investment casting, forging, milling, planing, shaping, sawing, turning",null,null,null,3,4.5,2,2.5]
[null,null,"Length of Bolt in Tension",null,null,null,"mm","Effective Clamp Length",null,null,null,{"$f":"F61+(0.8*'Calculation Sheet'!AF12)","v":{"$error":"#N/A"}},"mm",null,null,null,"40 μm to <160 μm","Hot rolling, sand casting, flame cutting",null,null,null,4,6.5,3,3.5]
[null,null,null,null,null,"Enter Value",null,"Min. Bolt Stretch (pre embedding loss)",null,null,null,{"$f":"F46*L61*1000/('Calculation Sheet'!AF11*K23)","v":{"$error":"#N/A"}},"μm"]
[null,null,null,null,null,null,null,"Net Bolt Stretch (post embedding loss)",null,null,null,{"$f":"L62-L59","v":{"$error":"#N/A"}},"μm",null,null,null,null,"Yield stress dependent on bolt size",null,null,null,"Bolt Material Properties"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Material","Threshold tensile area (mm^2)","Stress @ 0.2% proportional limit Below Threshold","Stress @ 0.2% proportional limit Above Threshold","stress @ 0.2% proportional limit (N/mm^2)","Min Tensile Strength","Max tensile strength based on max hardness","Ratio of Shear to tensile stress","modulus of elasticity (Mpa)","Reference"]
[null,null,"Minimum Resultant Preload",null,null,{"$f":"F46-K65","v":{"$error":"#N/A"}},"N","Bolt Preload Loss",null,null,{"$f":"(L59/1000)*(K23*'Calculation Sheet'!AF11)/L61","v":{"$error":"#N/A"}},"N"]
[null,null,"Minimum Resultant % of yield stress",null,null,{"$f":"(F65/K23)/K26","v":{"$error":"#N/A"}},null,"*Nordlock washers are ineffective under 30% yield stress of bolt"]
[]
[null,null,"BEARING STRESS"]
[null,null,"Type of Washer",null,null,null,null,null,null,null,null,null,null,null,null,null,"Grade 8.8 Steel",157,660,640,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R69,'Calculation Sheet'!T69,'Calculation Sheet'!S69)","v":{"$error":"#N/A"}},800,1080,0.6,208000,"ISO 898-1: 1999 "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"275 Steel",157,275,265,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R70,'Calculation Sheet'!T70,'Calculation Sheet'!S70)","v":{"$error":"#N/A"}},350,530,0.6,210000," EN 10025-2:2004 "]
[null,null,"Hole or Slot?",null,null,null,null,"Bearing Area",null,null,{"$f":"VLOOKUP(F71,'Calculation Sheet'!AE34:AF35,2,FALSE)","v":{"$error":"#N/A"}},"mm²",null,null,null,null,"355 Steel",157,355,345,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R71,'Calculation Sheet'!T71,'Calculation Sheet'!S71)","v":{"$error":"#N/A"}},470,630,0.6,210000,"EN 10025-2:2004 "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Stainless Steel 316",0,205,205,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R72,'Calculation Sheet'!T72,'Calculation Sheet'!S72)","v":{"$error":"#N/A"}},515,705,0.6,193000,"ISO 3506-1: 1997"]
[null,null,"Joint Material ",null,null,null,null,null,null,null,null,null,null,null,null,null,"Stainless Steel A4-70",0,450,450,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R73,'Calculation Sheet'!T73,'Calculation Sheet'!S73)","v":{"$error":"#N/A"}},700,"-",0.6,193000,"ISO 3506-1: 1997"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Duplex S31803",0,450,450,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R74,'Calculation Sheet'!T74,'Calculation Sheet'!S74)","v":{"$error":"#N/A"}},620,990,0.6,200000,"ASTM A479"]
[null,null,"Bearing Stress",null,null,{"$f":"(F44-K65)/K71","v":{"$error":"#N/A"}},"N/mm²",null,null,null,null,null,null,null,null,null,"Grade B7 Steel",0,720,720,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R75,'Calculation Sheet'!T75,'Calculation Sheet'!S75)","v":{"$error":"#N/A"}},861,1110,0.6,208000,"ASTM A193"]
[null,null,"% of Yield Stress",null,null,{"$f":"F75/'Calculation Sheet'!AF10","v":{"$error":"#N/A"}},{"$f":"IF(F76<100%,\"OK\",\"FAIL\")","v":{"$error":"#N/A"}},null,null,null,null,null,null,null,null,null,"SAE Grade 5",391,558,634,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R76,'Calculation Sheet'!T76,'Calculation Sheet'!S76)","v":{"$error":"#N/A"}},827,1080,0.6,208000,"SAE J429"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Titanium Grade 5",0,828,828,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R77,'Calculation Sheet'!T77,'Calculation Sheet'!S77)","v":{"$error":"#N/A"}},950,1140,0.5,113800,"UNS R56400"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Titanium Grade 2",0,275,275,{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R78,'Calculation Sheet'!T78,'Calculation Sheet'!S78)","v":{"$error":"#N/A"}},344,610,0.5,103000,"UNS R50400"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"User Defined 1","-","-","-",{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R79,'Calculation Sheet'!T79,'Calculation Sheet'!S79)","v":{"$error":"#N/A"}},"-","-","-","-","-"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"User Defined 2","-","-","-",{"$f":"IF('Calculation Sheet'!$K$23>'Calculation Sheet'!R80,'Calculation Sheet'!T80,'Calculation Sheet'!S80)","v":{"$error":"#N/A"}},"-","-","-","-","-"]
[null,null,null,null,null,"OY800-CAL-0099"]
[null,null,null,null,null,"Thread Stripping Calculator"]
[]
[null,null,"This calculator is designed to determine minimum thread engagement length so that internal & external threads will not strip before "]
[null,null,"bolt (at maximum tensile strength) has broken."]
[null,null,"Ref. Page 50, Handbook for the Training Course on Bolting Technology and the Analysis of Bolted Joints"]
[]
[null,null,"Created","Date","Checked","Date"]
[]
[]
[null,null,"Data Inputed","Date","Checked","Date"]
[]
[]
[null,null,"THREAD STRIPPING"]
[null,null,"Bolt/Stud Size",null,null,{"$f":"F22","v":0},null,"Shear Area of External Thread",null,null,{"$f":"VLOOKUP(F95,'Calculation Sheet'!Q10:X53,8,FALSE)","v":{"$error":"#N/A"}},"mm²/mm"]
[null,null,null,null,null,null,null,"Shear Area of Internal Thread",null,null,{"$f":"VLOOKUP(F95,'Calculation Sheet'!Q10:Y53,9,FALSE)","v":{"$error":"#N/A"}},"mm²/mm"]
[null,null,null,null,null,null,null,"Tensile Stress Area",null,null,{"$f":"VLOOKUP(F95,'Calculation Sheet'!Q10:S53,3,FALSE)","v":{"$error":"#N/A"}},"mm²"]
[]
[null,null,"Bolt/Stud Material",null,null,{"$f":"F26","v":0},null,"Ratio of shear to tensile strength",null,null,{"$f":"VLOOKUP(F99,'Calculation Sheet'!Q69:X80,8,FALSE)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,"Minimum Tensile Strength",null,null,{"$f":"VLOOKUP(F99,'Calculation Sheet'!Q69:V80,6,FALSE)","v":{"$error":"#N/A"}},"N/mm²"]
[null,null,null,null,null,null,null,"Maximum Tensile Strength",null,null,{"$f":"VLOOKUP(F99,'Calculation Sheet'!Q69:W80,7,FALSE)","v":{"$error":"#N/A"}},"N/mm²"]
[]
[null,null,null,null,null,null,null,"Max. force needed to break bolt",null,null,{"$f":"'Calculation Sheet'!AF53","v":{"$error":"#N/A"}},"N"]
[]
[null,null,"Internal Thread Component Material",null,null,null,null,"Ration of shear to tensile strength",null,null,{"$f":"VLOOKUP(F105,Q69:X80,8,FALSE)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,"Minimum Tensile Strength",null,null,{"$f":"VLOOKUP(F105,Q69:V80,6,FALSE)","v":{"$error":"#N/A"}},"N/mm²"]
[]
[]
[null,null,"Min. Length of Thread Engagement",null,null,{"$f":"IF('Calculation Sheet'!AF54=\"INTERNAL THREAD\",K103/('Calculation Sheet'!AF50*K96),K103/('Calculation Sheet'!AF49*K95))","v":{"$error":"#N/A"}},"mm","Bolt thread stripping force",null,null,{"$f":"'Calculation Sheet'!AF47*'Calculation Sheet'!AF49","v":{"$error":"#N/A"}},"N"]
[null,null,null,null,null,null,null,"Internal thread stripping force",null,null,{"$f":"'Calculation Sheet'!AF48*'Calculation Sheet'!AF50","v":{"$error":"#N/A"}},"N"]