×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Cosine Curve - Tank settlement evaluation5

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

### RE: Cosine Curve - Tank settlement evaluation

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: Cosine Curve

### RE: Cosine Curve - Tank settlement evaluation

(OP)
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: Cosine Curve" above?
Thanks you so much.

### RE: Cosine Curve - Tank settlement evaluation

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

### RE: Cosine Curve - Tank settlement evaluation

(OP)
Can anyone help me to get R2, a , b, and c values by in an excel sheet.
thank you very much.

### RE: Cosine Curve - Tank settlement evaluation

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

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!

### RE: Cosine Curve - Tank settlement evaluation

(OP)
Dear Geoff.
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

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.

### RE: Cosine Curve - Tank settlement evaluation

3
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.
_______________________________________________

• 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.
• I think you can delete column F. It doesn't appear to be used.
• 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.
• In the RMS formula SUM(J8:J23)/COUNTA(J8:J23) is easier to read as AVERAGE(J8:J23)
• The values of S should be ABS() as we only need the magnitude, not the direction.
• 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.

### RE: Cosine Curve - Tank settlement evaluation

Dear Geoff13,
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

Feel free to develop your own spreadsheet based on the posting above, as well as the linked posting.

### RE: Cosine Curve - Tank settlement evaluation

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.

### RE: Cosine Curve - Tank settlement evaluation

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

### RE: Cosine Curve - Tank settlement evaluation

#### Quote (BALAMURUGAN1971)

... the attached excel sheet shows the formula with sin, but API says cos.

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

Dear Goeff,
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

Agreed. That's exactly what my 23-Jan-2023 post above says as well.

### RE: Cosine Curve - Tank settlement evaluation

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.

### RE: Cosine Curve - Tank settlement evaluation

See my 21-Jun-2023 post above for explanation of a, b and c.

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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!