Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

Cosine Curve - Tank settlement evaluation 5

Status
Not open for further replies.
Restivehorse

Restivehorse

Petroleum
Jun 16, 2023
6
0
0
VN
I am in the process of creating a cosine curve for the settlement survey in accordance with API 653 annex B.2.2.4 and B2.2.5. I have read through the entire annex and am pretty understanding of it, though cannot seem to understand how to acquire appropriate predicted elevations.

In API 653 it says: Where Elevpred is the elevation predicted by the cosine curve at angle theta. A typical starting point for a computer
best-fit cosine curve is a least-square fit where a, b, and c are chosen to minimize the sum of the square of the
differences between measured and predicted elevations.

However, no where in the code does it specify what R2, a , b, and C values area derived from?

R2 ≥ 0.9
Elevpred = a + b × cos (theta + c)

Can anyone help me to get R2, a , b, and c values in excel sheet attached below:

Tank parameters and elevation (height) value of tank observation is shown in excel file attached

Thanks







 
 https://files.engineering.com/getfile.aspx?folder=9b7aacf9-9592-4e7a-b8e9-fd50629d0189&file=Cosine_Curve_-_Tank_settlement_evaluation.xls
Replies continue below

Recommended for you

Sort by date Sort by votes
You have attached an XLS file. I would never open an XLS from an unknown source. I might open an XLSX file as it can't contain unknown macros.

You should look at this thread as well : thread1452-483560
 
Upvote 0 Downvote
Dear Geoff13.
Pls refer pdf file attached and send back to me an excel file with full results and formulas of R2, PE (a, b, c) after completion.
By the way, you can send me an excel file of file "EngTips_Tank_Level" which attached date: 19 Jul 21 14:36 relating to the Subject "thread1452-483560: Cosine Curve" above?
Thanks you so much.
 
 https://files.engineering.com/getfile.aspx?folder=f1e57cc2-8a5a-476e-9673-8bf099d4211d&file=Cosine_Curve_-_Tank_settlement_evaluation.pdf
Upvote 0 Downvote
What you've now attached is a PDF of some of your data. If you're not going to make any effort to create a working spreadsheet that can be reviewed, then I'm not sure why you think I should.

I think the PDF I attached in the other thread should give you some good starting hints.

Geoff
 
Upvote 0 Downvote
What have you created so far in Excel?

a is the elevation of the best-fit cosine curve since the measured elevations are never centred on zero.
b is the amplitude of the cosine curve since the measured elevations never have an amplitude of one.
c is the offset angle of the start of the cosine curve since the measured elevations never have their peak at zero degrees.

Use these values to predict an elevation at each survey point. Calculate the difference between actual and predicted for each point. Then calculate the RMS of these values. First Square each value. Then find the Mean of these squares. And finally the Root of this mean. Use Excel's Solver to minimize this RMS value by allowing it to vary a, b and c.

You can now calculate R[sup]2[/sup] between the actual and best-fit predicted values. R is the correlation coefficient, which is an Excel function.

Try to get this working. If you're still having troubles, post your XLSX file.
 
Upvote 0 Downvote
The idea of this site is that you do 95% of the work, but can't quite finish the task. You then ask a question here and someone tries to help for that last 5%.

The file you've attached is just your input data. You have made no effort to implement your own ideas of what Annex B requires nor the ideas I presented above.

Why do you hope that someone on EngTips will do 100% of your work?

Feel free to post your file once you've made a 95% effort. You may even get it working 100% without needing to ask anything more!
 
Upvote 0 Downvote
First create a spreadsheet that implements the calculations.

You can then use the Excel Solver add-on. You would specify that Solver should find the minimum value for the RMS error while allowing it to vary the a, b, c values (that I labelled amplitude, angle and centerline). Solver will try many, many, many values for a, b and c as it hunts for the minimum RMS value.

Once you have spreadsheet and have used Solver you could try varying the values of a,b and c to see if the minimum RMS has been found, but I think you'll find Excel is very good at finding these. Certainly close enough for an Annex B evaluation.

Note : If you try to check my example your angle will be 90° different because I used sin as opposed to cos. Not sure why, but it doesn't matter for the Annex B evaluation.
 
Upvote 0 Downvote
Good work RestiveHorse. You've got it 99% solved.

The file you attached did not have your Solver settings included, so I had to play around for a bit. I was very frustrated (with Excel) for a while. My spreadsheet worked and yours did not even though it appeared both do the same calcs!

In the end it seems to be a setting on the Solver page. I do not have a check mark beside "Make unconstrained variable non-negative" in my spreadsheet. When this is checked I don't get a solution in my spreadsheet either. It's been such a long time since I developed this spreadsheet that I can't remember if I had this same issue once upon a time. Please note that sometimes I get b=0.469 and c=131.462 and other times I get b=-0.469 and c=-48.538. These are obviously identical solutions.

The value for S Allow (cell K5) should be a formula, not a fixed value (see B.3.2.1).

In cell C26 it would be better to do MAX(C8:C23)-MIN(C8:C23). This is not a Code check, but assists me in making my engineering judgment of whether I believe the results for S vs S Allow. I've seen surveys where there is a very small min to max, but the math says it fails. I've then made the judgment the tank is OK.

I think this will let you complete the Pass/Fail column, and then add the plot. The plot can also be incredibly helpful judging the results.

If you have any more trouble just post again.
_______________________________________________

Looking ahead :
[ul]
[li]This spreadsheet will need to be customized for each different number of survey points. Building 22.5 into the formulas in column D will make this difficult.[/li]
[li]I think you can delete column F. It doesn't appear to be used.[/li]
[li]You can get rid of row 24. I used that only to simplify my formulas for the B.2.2.5 evaluation, which I haven't shown.[/li]
[li]In the RMS formula SUM(J8:J23)/COUNTA(J8:J23) is easier to read as AVERAGE(J8:J23)[/li]
[li]The values of S should be ABS() as we only need the magnitude, not the direction.[/li]
[li]Please read my other posting before you do your own data. This sample data has too many survey points which results in the tank failing. I believe your original data also has too many data points and it might be better to check with only every other data point.[/li]
[/ul]
 
Upvote 0 Downvote
Feel free to develop your own spreadsheet based on the posting above, as well as the linked posting.

If you have troubles, start a new thread with your questions.
 
Upvote 0 Downvote
I should correct myself.

The spreadsheet discussed in this thread is for API 653 Annex B rules. These are only applicable to tanks that have been in service.

Tanks before and after hydrotest should meet API 650 rules and tolerances.
 
Upvote 0 Downvote
BALAMURUGAN1971 said:
... the attached excel sheet shows the formula with sin, but API says cos.
Click to expand...

Based on your review of Annex B, what do you find is the change resulting from using sine vs cosine in the B.2.2.4 calculations?
 
Upvote 0 Downvote
valuve of base elevation or centerline i got it as the average of height.
the value of b is mentioned 0.469 but what i am getting is 0.540, after subtracting max height with minimum height and dividing it by 2.
its been 3 days and i am still unable to understand how did you get angle 131.462 and 0.469.

Please can someone give clarification for the value of a and c.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andres Romero
  • Locked
  • Question
Tank shell evaluation according to API 653
Replies
3
Views
4
Andres Romero
Andres Romero
Ivan Silva
  • Locked
  • Question
Stainless storage tank fabrication
Replies
1
Views
16
JStephen
JStephen
Tony P
  • Locked
  • Question
Tank dome roof rafters
Replies
2
Views
5
JStephen
JStephen
ryuchangmyong
  • Locked
  • Question
Horizontial storage tank thickness calculation
Replies
1
Views
6
JStephen
JStephen
Andres Romero
  • Locked
  • Question
API 653 Shell evaluation discussion
Replies
1
Views
5
Dhurjati Sen
Dhurjati Sen
Back
Top