## Cosine Curve - Tank settlement evaluation

## Cosine Curve - Tank settlement evaluation

(OP)

**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

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

## RE: Cosine Curve - Tank settlement evaluation

mightopen an XLSX file as it can't contain unknown macros.You should look at this thread as well : thread1452-483560: Cosine Curve

## RE: Cosine Curve - Tank settlement evaluation

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: Cosine Curve" above?

Thanks you so much.

## RE: Cosine Curve - Tank settlement evaluation

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

Geoff

## RE: Cosine Curve - Tank settlement evaluation

thank you very much.

## RE: Cosine Curve - Tank settlement evaluation

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

^{2}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.

## RE: Cosine Curve - Tank settlement evaluation

Please check attached file below and can you help me to establish formals for R, Elevpred (PE) and cosine curve chart in excel file?

Really high appreciate and thank you so much if you help me to resolve it.

## RE: Cosine Curve - Tank settlement evaluation

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!

## RE: Cosine Curve - Tank settlement evaluation

Relating to the thread "thread1452-483560: Cosine Curve", can you show me why amplitude =0.469in and angle = 131,462 degree?

Thank you so much

## RE: Cosine Curve - Tank settlement evaluation

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.

## RE: Cosine Curve - Tank settlement evaluation

Really i am facing the difficulty to find out the b and c values in excel file. Can you help me to solve this problem by excel file attached?

Thank you very much.

## RE: Cosine Curve - Tank settlement evaluation

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 :

## RE: Cosine Curve - Tank settlement evaluation

we are getting a project for tank settlement evaluation during hydrotest.Can i get your contact for further discussion.

## RE: Cosine Curve - Tank settlement evaluation

If you have troubles, start a new thread with your questions.

## RE: Cosine Curve - Tank settlement evaluation

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.

## RE: Cosine Curve - Tank settlement evaluation

I have one clarification, column H in the attached excel sheet shows the formula with sin, but API says cos.

Please advise.

## RE: Cosine Curve - Tank settlement evaluation

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?

## RE: Cosine Curve - Tank settlement evaluation

There is no change while solving with Excel using COS formula.

Only the angle is reduced by 90 deg in COS normally.

## RE: Cosine Curve - Tank settlement evaluation

## RE: Cosine Curve - Tank settlement evaluation

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.

## RE: Cosine Curve - Tank settlement evaluation

I use Solver to find the values of a, b and c that provide the best fit, as I discussed in my 28-Jun-2023 posting above. While trial-and-error may or may not get you these values after a lot of work, Solver will get the answer in a fraction of a second.