API 653 Appendix B spreadsheet 1

[Sparky1949]

My Supervisor is convinced that he saw a spreadsheet for calculating the Cosine curve from the settlement data. I have now spent more than two hours searching online to no avail.​

Can anyone provide a link to such a spreadsheet?

thread404-167528

 

[JStephen]

I've used spreadsheets to calculate this, but never a canned spreadsheet, mainly due to the variable number of points.

I'm pretty sure that in two hours time, I could generate a fresh spreadsheet and find the optimal cosine fit, for what that's worth.

 

[Geoff13]

I've also used my own spreadsheet to do this. Clause B.2.2.4(e) describes the least-square method. With Solver finding the "a, b, c" parameters for the best fit cosine is very easy.

The one gotcha that I see folks doing all the time, now that they have tons of data from a 3D scan or some other fancy toy, is using way too many points. See the NOTE after clause B.2.2.4(f). If you use twice as many points as required to meet the 32 foot spacing requirment then the tolerance calculated in B.3.2.1 will be only 25% of what it could be. Foundations will "fail" the curve fit that are actually OK using the proper spacing of points.

 

[Sparky1949]

JStephen,
Thank you. I thought the same about the time. However, while I did it years ago it was a huge pain, and I doubt that I could do it again. Solver is way over my head.

Geoff13,
Thank you. I'm happy to say that there are only eight points. This was a small tank.

Here is a sheet from 2009 for what it is worth...

 

[Geoff13]

Looked at too many Appendix B curve fits, so the spreadsheet jumped out as weird, and I dug into it a little.
[ul][li]Very suspicious that the elevation for locations 1 and 20 aren't closer together. Everything looks great except that there's a sudden 2" drop between 20 and 1. It isn't obvious from the data in rows 78 thru 97 that the survey "closed", meaning they got the same reading for location 1 when the laser was at setup D.[/li]
[li]The E value in cell E67 needs another zero.[/li]
[li]The tank height in cells D34 and I68 are different. No wonder there are different S values due to these 2 differences.[/li]
[li]The plot at cell F26 is from an external spreadsheet for some obviously different data. Same for the F38 plot.[/li]
[li]There's no place to enter "a, b, c" of clause B.2.2.4(e), so there's no way to do a best fit cosine. The plotted cosine is just an arbitrary selection. Thus the result in column M is meaningless.[/li]
[/ul]
I stopped looking at this point. Too many problems.

Make a long story short, no way anybody should use this spreadsheet as-is for an Appendix B check.