Appendix 2 Flange Factors (F, V, f) 1

[Cobra17]

Hey,

long time viewer as a guest but I've run into something I need a little help with.

I'm trying to do appendix 2 integral flange calculations in excel and I want the long formulas found in Table 2-7.1. I've got them all there and checked, double checked, checked by 2 other people... but I can't seem to get F,V and f correct when g0 & g1 are not the same.

I did stumble on a post from years ago that someone did mention they recalled something off about the equations, but i figured I'd start a new thread since that thread was over 5 years ago and no real solution was posted.

 

[Geoff13]

I've had my own Appendix 2 flange spreadsheet for years. Just did a quick modification to it to create my equivalent to the Figure 2-7.2 graph for F (without the log-log axis scaling). The V and f graphs look equally close. Seems to work for me, so I'd guess the Table 2-7.1 equations are OK.

The Table 2-7.1 equations are a nasty bunch. I gave each cell a Name to match it's formula number, so when I referenced it later the equations looked like the Table. For example:

Is your spreadsheet (or a portion of it) something you can post?

 

[Cobra17]

here is my spreadsheet.

The only reason i'm doing the calculations that's the only way I could figure it out with excel. I open to a different way.

 

[Geoff13]

I used Excel for my flange design calc.

This is an xlsm file, meaning it has macros. I don't open xlsm files from the Internet.

Does this spreadsheet work as an xlsx? If so, could you re-post that version?

 

[Cobra17]

i changed it to an xlsx

 

[Geoff13]

My spreadsheet is obviously very similar to yours, so I used your inputs, and then looked for differing outputs. A couple of items outside the Table 2-7.1 formulas caught my eye as well.
[ul]
[li]Formula for He (cell M67). Should also use 0.785, though I prefer PI()/4 so I can understand what the formula is doing.[/li]
[li]Formula for C7 (cell M115). The brackets aren't correct. Should give 12.8989[/li]
[li]The additional levels of brackets in all the formulas make them impossible to review. The formula for C7 needs only one set of brackets, and then it's obvious if they're correctly located. I changed your C7 formula to: =215/2772+51*$O$103/1232+(60/7+225*$O$103/14+75*$O$103^2/7+5*$O$103^3/2)/$O$104 to check it got the right answer.[/li]
[li]Formula for C14 (cell M129). Should be /35, not *35. Should give -0.1825[/li]
[li]Formula for F (cell M180). The final term should perhaps be *(-1) to get the negative of the entire equation? I would just add a - at the formula beginning. Should give 0.8234[/li]
[li]The formulas for F, V and f are within IF statements. These simple values noted in Table 2-7.1 are to save someone doing the C1 thru C37 calcs in these special cases, but the spreadsheet is always going to do the detailed calc, so just keep the formula and delete the enclosing IF.[/li]
[li]I can't find where you are including Bsc (Appendix 2 Equation 7). This should increase all the Mo values by 1.3208.[/li]
[/ul]

Geoff

 

[Cobra17]

Geoff13,

the review is very much appreciated, thanks.

I did the updates that you mentioned, and removed all the "extra" brackets i had. I need to trust excel does the math correctly. I didn't add the Bsc because i'm not doing lethal service, and i've never had a request for a user to include it.

I'm still getting my F & V incorrect, and i think i might need to keep the if statement for f when the g0=g1

if you do take a look at it again.. i'm in the process of streamlining the code behind making the formula's and i haven't gotten it to limit the number of decimal places yet, so the long equations are pretty messy.... sorry.

 

[SnTMan]

I need to trust excel does the math correctly.

Of course it does

Regards,

Mike

The problem with sloppy work is that the supply FAR EXCEEDS the demand

 

[Geoff13]

Excel understands the proper precedence of math operators. You generally only need to add brackets where you need to put them in a hand written formula.

I only looked at column M where the math is performed so I don't "see" the remainder of your sheet.
[ul][li]You don't have a formula for C37 (cell M169). Should give -3.1597[/li]
[li]For formula E6 (cell M181) it should be /72, not /7. Should give -2.2696[/li]
[li]For V (cell M185) you removed too many brackets and wrong power. Should be =$M$177/((2.73/$M$104)^0.25*(1+$M$103)^3) and give 0.2245[/li]
[/ul]

Geoff

 

[Cobra17]

thanks for your help... that got it.

I didn't remove a bracket in C37 so when the formula for M get generated it errored out and left it blank... time to write some more code to make it stand out if that happens...

 

[r6155]

Cobra17
As you mentioned on Jun 18 20, all those who checked your calculations are not qualified to be a checker.

Regards

 

[Cobra17]

I'm glad there is somewhere to ask for help from very knowledgeable people. Most things I can search for and find answers without even posting but these spreadsheets I'm making are driving me crazy(ier).

now i just need a excel/vba expert to help me trim out some trailing decimals in my long formulas...lol

 

[Geoff13]

I'm glad this has worked out for you. You got lucky that there's too much boredom for me with the Covid stay-at-home guidelines.

Once again I'm going to push for using cell names. If you are going to develop large spreadsheets like this, cell names are invaluable for you and your reviewers. I would suspect 6 months from now you would have a tough time remembering what each formula does, and it would be an even worse challenge for a new user. Even if you started adding cell names now, Excel can automatically replace all usages in your formulas using "Apply Names ...". Highly, highly recommended.

For instance, with cell names someone could look at my spreadsheet and Table 2-7.1, and probably conclude the formulas are OK in 15 minutes (see attached screenshot). You'll notice I also like lots of spaces in formulas, but that's just my preference.

Geoff

 

[Cobra17]

I have variable names located in column 'E', column 'F' is my formula that I type out... I then run a macro to generate the result(column 'M') and the long equation(column L). This way i just "copy" a formula and run my macro to get what I want, assuming my copying skills are correct .. and i don't have to worry about named ranges so i can use C5, etc.. and they are valid variable names.

your 'A' is my 'E' and your 'B' is my 'F'... but if the formula is long it puts the long equation down a row and everything is shrunk to fit across 'F:L'

and thanks again... I have a feeling I would have lost even more hair if i had to just start over and figure it out that way.