×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

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

Students Click Here

Jobs

Need Excel Calc for Linepack Calculation
2

Need Excel Calc for Linepack Calculation

Need Excel Calc for Linepack Calculation

(OP)
Hello -

I certainly hope I am in the right place for what I would hope is a simple request...

I have to keep hundreds of records in Excel each day for various linepack segments. I have, in separate cells, the necessary pressures, temperatures, gravities, and pipe diameters and lengths. What I need is a formula in Excel that I can modify to pull the necessary data from these cells. Atmospheric pressures, efficiency, etc. are all fixed data for now. Also, compressibility needs to be factored as well, which may be in a separate formula - ?

I need it in Excel because Excel is going to automate the process of pulling the data needed for the calculations, copying and pasting, self-saving, etc.

I apologize in advance for my ignorance but this seemed like a great place to ask this question. I appreciate any help in advance.

RE: Need Excel Calc for Linepack Calculation

um, rather than provide an answer . . .

is there an engineering dept with your employer?

this has been asked before in the Pipelines, Piping & Fluid Mechanics Forum.  try a search within this forum.

conducting a search by google "pipeline line pack" yields results for you to investigate.

pipeline line pack is transient in nature and each company likely conducts their own analysis.  hence, the suggestion to investigate within your own company.  if pressures/temperatures/inflows/outflows and composition were constant throughout the pipeline, then linepack calcs are straightforward.  but since they are transient, well good luck as this is not a trivial matter.

good luck!
-pmover

RE: Need Excel Calc for Linepack Calculation

(OP)
Yes I know! Actually Googling the linepack equation yields many results, so I guess I am looking more for an Excel form if you will, for lack of better description. I have tried to build the equations in Excel but come up short. I found a VBA equation that works well in Excel, but I cannot translate it inot formulas that read cells. I will search that forum... I understand what you are saying about the transient nature - I am quite familiar with it. So I get that this is open to a lot of possibilities. Thank you very much for the reply!

RE: Need Excel Calc for Linepack Calculation

Line pack is a relatively simple calculation.
You need a function for the average gas pressure of the segment (not the mean).
You need a function for the compressibility calculation, just slightly more complicated.

A VBA for reading the segment cell data and calculating LPack is super easy.

I have VBA UDFs (user defined functions) for both those avg gas pressue and compressibility equations.

In fact the only real complicated thing about it is in what units you want the answer?
 

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
Hi - thanks for the reply.

I have a table for Z factor in Excel - no problem. I use the 2/3 method for averages of P, T, and Z. That's easy. What I cannot make work is a calculation for the linepack (mcf or mmcf) in a single cell that computes correctly. It SEEMS I have all the components for the equation there, but no dice. I am comparing my equation to the same answer in SCADA, and it is off by 20 mmcf.

I have a VBA that arrives at the correct answer, but it is more a userform - you have to manually enter the P, T, etc. into text boxes.

Because my Excel sheet automatically updates with the SCADA values, I want the linepack calc in one cell to calculate based on those cells as they refresh. Make sense?

Anything you care to share I would love to see, I am learning a lot on the fly. Thanks again!

RE: Need Excel Calc for Linepack Calculation

I can imagine several possible sources of errors,

your table is not the exact equation that the SCADA system is based on,
or your table interpolation isn't correct,
the table uses a different method to calculate compressibility,
(I'd use a subroutine or UDF, rather than a table, after all it's easier than doing lookups and interpolations)
and

Even though you are looking at values supposedly calculated using data at the ends of a given segment, the SCADA analysis program is probably breaking that segment up into smaller segments internally and is in effect integrating over much finer steps than one long segment.

I also need to know the details.  If you're at 1400 psi, have wide temperature differences between ends, and your segments are long, 20 mmcf might not mean much as far as errors go, you know. A 20 MMcf difference out of 500 MM wouldn't be all that bad.

If you post the input values you have for one segment,
The formulas you use, your answer and
the value for the answer that is given by SCADA output I might be able to offer more comment, or corrections.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
Okay. First, SCADA is using the same endpoints and variables as I am. I will list them. Also, my VBA user form thingy is pretty much dead on with SCADA. Also, I know the formula that SCADA is using and I will show you that as well. Here are my variables:

P1 = 1085
P2 = 1010
T1 = 104
T2 = 87
Dia = 29.14 in
L = 63.22 mi
Z = 1.11
SpG = .573

Formula Used (with a constant): LP = 1.0218*D*D*L*((Pav/(Zav*(Tav+460))))

SCADA result: 117 mmcf
My result: 93 mmcf


Thanks again for looking at this with me.

RE: Need Excel Calc for Linepack Calculation

I'll have a look and get back to you here tomorrow.  I'm in the UAE and its getting a bit late now.

Watch this space.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
ok thanks.

RE: Need Excel Calc for Linepack Calculation

First problem is when I substitute the given numbers
(I'm not sure what units the equation is using, so I have to assume they are in the same units as given and the conversion factor using that constant to get MMCF is correct)
I don't get anything near 100 MMCF

Do you have,
Pavg = =2/3*(p1+p2-p1*p2/(p1+p2)) = 1048 psiG
and just to confirm that your pressures are Gauge pressures, right.

Tavg (average flowing temperature in the pipeline segment) should be something like 91[&deg]F, as temperature fall is proportional to the natural log of length, so not equal to the mean temperature of (104+87)/2 = 95.5[&deg]F

although I think using gauge and log temps make no real difference to the end result in this case, log temperature = 91[&deg]F.  It's just nice to know so we can keep things neat and tidy.  It's better to get into the habit of always specifying what value types you are using.

It is not clear what the LP equation is trying to do.  If it is directly calculating line pack at Standard Conditions from the actual inside volume of the pipe segment (which I get BTW = 1.546 MMCF), using flow condition averages for Z, P and T values probably won't do it.  It would seem it would need averages taken between flow conditions and standard conditions, but that would also seem to be too far a spread to work well.  

Another comment.  Are you sure that it is using compressibility factor, or is it supercompressibility factor.  Checking the value of 1.11 using the CNGA (Compressed Natural Gas Association) formula,
1/(1+(P_psiG *  344400*10(1.75* SG)/Tf3.825 ))
I think that should be around 0.90 rather than your 1.115
taking the inverse of 0.90 = 1.11 oddly enough very close to your value of 1.115, which I think might be the value for supercompressibility.

Is it possible that you can post 1 page of your spreadsheet?  It's probably easier to start with that.  Otherwise I think you need to post each equation, the values for each of the variables used .. with the units.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

Ought Oh.  Is this natural gas?

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

OK, trying to answer some of my own questions, I modeled it as a zero elevation 30" pipeline with 0.44 wt in Stoner Pipeline Simulator with natural gas and a CNGA compressibility factor for 0.573 gas gravity, used 3 pipeline segments of 20, 20 and 23.22 miles , 5, 5 and 6 internal segment intervals respectively, assumed the 1085 and 1010 pressures were gauge, used an 80°F soil temperature, giving an outlet temperature of 86.8°F  and I got a total line pack "inventory" of 118.442 MMFT3

I attach a spreadsheet with each segment's variable list pasted in.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

Scada looks to be off by 1 MMCF

I have a 0.12 % error when I check my result against Stoner Pipeline Simulator Result.  Probably because Stoner is using 16 subsegment intervals.  A lot more than my 3 segments.

 

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
Hi BigInch - sorry for the delay; I struggled to get back to this.

You are right, I was using the wrong factor - .90 was what I needed for compressibility.

I did find a VBA code for the NX-19 method and it was pretty easy to implement and seems to work. However, I like what you have posted here a lot. I think I can take your table and use it the way I want to, and with the massive file I will have to create, your table will save on file size. THANK YOU VERY MUCH!!!

My only question at this point is, why are the formulas for flows, pack rate, etc. at the top part of your table only showing values? I would love to see those but if you left them out on purpose that's fine. I really, REALLY appreciate your help with this in any event.

RE: Need Excel Calc for Linepack Calculation

Those are from the pipe segment variable lists available in Stoner Pipeline Simulator.  I just copy-pasted directly from the program's output so I could get a quick check on my hand calculations.  It was lazy of me using the mean temperature in the segments, but it didn't seem like it would make too much difference in the result, so I left it.

The CNGA method is very good for typical gas pipeline flow conditions.  If you want to post the VBA code for NX-19, I wouldn't object. smile

An isothermal flow and pressure drop equation wouldn't be that hard to add.

 

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
Thanks again for your help - now if I might trouble you a little further!

I have an engineer here that was looking at the results as well, and he asked which compressibility selection you are using in Stoner. Also, we were wondering how this model handles changes in N2 and CO2 - I didn't see them on the worksheet you submitted. Please forgive me if you have answered part of that already - I have slept since he asked and you and I have posted. I will post the VBA for you:

'Gas Property Functions at Specific Conditions
Function Z_nx19(Ppsig, Patm, Tdegf, SG, moleCO2, moleN2)
    nx19_Tdegr = Tdegf + 459.67
    nx19_Ppsia = Ppsig + Patm
    nx19_Padj = (156.47 * nx19_Ppsia) / (160.8 - (7.22 * SG) + moleCO2 - (0.392 * moleN2))
    nx19_Tadj = (226.29 * (nx19_Tdegr)) / (99.15 + (211.9 * SG) - moleCO2 - (1.681 * moleN2))
    nx19_Tao = nx19_Tadj / 500
    nx19_Tb = 1.09 - nx19_Tao
    nx19_Pii = (nx19_Padj + 14.7) / 1000
    nx19_M = 0.0330378 / nx19_Tao ^ 2 - 0.0221323 / nx19_Tao ^ 3 + 0.0161353 / nx19_Tao ^ 5
    nx19_N = (0.265827 / nx19_Tao ^ 2 + 0.0457697 / nx19_Tao ^ 4 - 0.133185 / nx19_Tao) / nx19_M
    nx19_E2 = 1 - (0.00075 * nx19_Pii ^ 2.3) * (2 - Exp(-20 * nx19_Tb)) - (1.317 * nx19_Tb ^ 4 * nx19_Pii * (1.69 - nx19_Pii ^ 2))
    nx19_BB = ((9 * nx19_N) - (2 * nx19_M * nx19_N ^ 3)) / (54 * nx19_M * nx19_Pii ^ 3) - (nx19_E2 / (2 * nx19_M * nx19_Pii ^ 2))
    nx19_B = (3 - (nx19_M * nx19_N ^ 2)) / (9 * nx19_M * nx19_Pii ^ 2)
    nx19_Dp = ((nx19_BB + (nx19_BB ^ 2 + nx19_B ^ 3) ^ 0.5) ^ (1 / 3))
    nx19_fpv = ((1 + (0.00132 / nx19_Tao ^ 3.25)) ^ -2) / (((1 / ((nx19_B / nx19_Dp) - nx19_Dp + (nx19_N / (3 * nx19_Pii))))) ^ 0.5)
    Z_nx19 = 1 / (nx19_fpv) ^ 2
End Function


Then in your cell, you just need the formula =Z_nx19(Ppsig, Patm, Tdegf, SG, moleCO2, moleN2)

Hopefully that is of use to you!  

RE: Need Excel Calc for Linepack Calculation

No problem.  
For gas mixtures Stoner recommends using BWRS equation of state.
Stoner has 28 predefined components to select from.

Quoting the user manual,

Quote:

The Benedict-Webb-Rubin-Starling (BWRS) equation of state may be used for multi-fluid simulations and is based on the Starling modification of the Benedict-Webb-Rubin formulation. It permits accessing a set of pre-defined components by name with corresponding BWRS coefficients. The BWRS equation of state may be used for gases at high pressure or mixtures of dissimilar fluids. This includes mixtures of hydrocarbons and acid gases (CO2, H2S, N2, etc.), especially in situations where known compositions of fluid sources may mix in varying ratios in the network.

BWRS may be used for liquid systems such as LPG, or dense phase fluids like ethylene or carbon dioxide. BWRS is not suitable for simulation near the two-phase region.

The BWRS equation of state is only valid for a single phase during one simulation. Conditions should not exist in the simulation that would cause more than one phase to exist. This includes the phases at the custody pressure and temperature.

The lower temperature limit is -59.69°F.

Using the BWRS equation of state automatically initializes batch tracking and/or blending simulations. If you are modeling a constant composition, you will likely want to turn this off. For more information, see NOTRACK.

BWRS can be activated in Stoner's code something like this example
 
STATE BWRS /*Benedict Webb Rubin Starling
/*           Methane  Ethane  Propane  Isobutane  Carbon Dioxide
+ NAMES      C1       C2      C3       IC4        CO2
+ INITIAL  0.85       0.07    0.03     0.03       0.02 /* inlet composition


But if it is gas pipeline transmission quality gas of a given specific gravity, the CNGA can be used and is switched on with this code.  Only the specific gravity is needed here, press & temp given elsewhere or calculated using the transthermal mode.

STATE CNGA 0.573

The AGA Report #8 can also be selected.

Quote:

The AGA equation of state may be used for multi-fluid simulations and is an implementation of the Gross Characterization Method of AGA Report No. 8, "Compressibility Factors of Natural Gas and Other Related Hydrocarbon Gases." It may also be used to track user-defined fluid properties.
But has some conditions,
0.554 <= DENS <= 0.87
CH4 > 45%
N2 < 50
CO2 < 30
ethane < 10
propane < 4
32F <= temperatures <= 130°F
pressures <= 1200 psia

-------------------------------------------------------
Thanks a bunch for the NX-19 *

 

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
You're welcome. I will submit this to my colleague as I know he is interested in this. But I missed where in your results you addressed N2 and CO2 - ? Am I just not seeing/getting it?  

RE: Need Excel Calc for Linepack Calculation

In my calculation I did not assume any N2 or CO2, because you didn't mention that you had any.  Actually I'm still not sure if you have natural gas or not.  I was asking if you had natual gas somewhere up above, but you never actually told me what the gas was.   

I don't know how much N2 or CO2 will begin to adversly affect the results when using CNGA.  I would guess that CNGA has limits similar to those for AGA8.

By the way, if choosing between NX-19 and AGA-8 you really should be using AGA-8.  For mixes outside the limits given above, go to BWRS.

N2 = 0 & CO2 = 0

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
Sorry, yes it is natural gas. And I thought the AGA-8 was no longer recgnized? We are using the NX-19 for whatever reason... I just need to be able to calculate inventory and include N2 and CO2...  

RE: Need Excel Calc for Linepack Calculation

Why do you think AGA 8 isn't recognized (and not recognized by whom).  I have not heard that, but my business is not measurement, so I could be off in the weeds again.  AGA 8 is OK for my purposes.  I don't know if the latest version of Stoner gives other options.

AGA 9 for ultrasonic meters (I think) use AGA 8 method.

AGA 10 calculates speed of sound for internal comparison in ultrasonic flowmeters.  I also don't know if anyone is reversing the equation and calculating Z from that speed?  I'm not.
www.asgmt.com/default/papers/asgmt2010/docs/W1_W2_4.pdf

Quote:

NX-19, although limited, has an important technological legacy. One of the significant problems with the NX-19
equation was that the formulation contained discontinuities in the derivatives of the compressibility factor at the
boundaries of the auxiliary fitting functions. This limited its engineering utility to supercompressibility factor
calculations. The equation could not be used to meet other gas engineering needs including critical flow nozzles
calculations, sound speed calculations, or compressor calculations. In addition, the NX-19 method was biased
for rich gases and high dilutes content gases. The concern over bias errors caused the industry to initiate the
development of a new equation of state method to replace the NX-19 work. The new work became A.G.A. Report
No.8.

Quote:

The A.G.A. Report No. 8 expression has embedded in it calculations for mixtures. The details of the mixture
calculations are not shown here. The mixture functions are mathematical relationships that permit predicting
mixture fluid properties by numerically nudging pure fluid correlation parameters. They are further refined through
the use of fluid specific regression interaction parameters. These quantities are known as binary interaction
parameters. Binary interaction parameters are common in virtually all equations of state. They require the
regression of experimental binary mixture data to improve the description of fluids containing the binary
components. A key element in the A.G.A. Report No. 8 equation of state is its ability to accurately compute the
properties for real natural gas mixtures. It accomplishes this through the use of hybrid mixing rules. The mixing
rules provide a practical means to describe fluid mixture behavior. It should be noted that the equation was not
developed for the high density fluid critical region. In most gas measurement applications the operating conditions
are far removed from the critical region.

AGA 8 "normal range" will allow up to 30% mol wt of CO2 and 50 for N2. Even more in the "extended range".  Do you have more than 30% CO2 or 50% N2?  "Expanded ranges" go to 100%.

Now we have to find a VBA for AGA-8.  

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

http://www.squinch.org/gas/aga8.html

I don't think I want to do it.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

(OP)
I speak from a place of ignorance of AGA-8 not being accepted anymore - perhaps that is just my company. I do know that we would prefer to use the Gross Characterization Method II if possible. However, as it stands right now, it looks like I will need to use the VBA in order to incorporate CO2 and N2. And no, the levels of those two components are always much lower than the values you were asking about.

And yeah, those two links are way beyond the scope of my project!  

RE: Need Excel Calc for Linepack Calculation

When I was researching to see what I could find about AGA8 being relegated, I didn't see where anybody is using anything different, including a bunch of meter manufacturers.  It would seem that no matter what type of meter you have, including ultrasonic and coriolis meters, they are still using AGA-8 when it comes to calculating Z.  

I'm not sure, but if you used AGA-8-Lite with component values less than the lower limits, I wouldn't be surprized if it worked.  Anyway, despite my comment about using AGA-8 (I thought you might have a custody transfer issue somewhere) there would be nothing wrong with you continuing to use NX-19, as long as it gives you answers within your scope of required accuracy.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Need Excel Calc for Linepack Calculation

for calculating fluid properties in Excel as gas densities according AGA 1992, BWRS, SRK, PR, GERG etc. you may use Properties, see

http;//www.prode.com/en/properties.htm

the advantage is that for many properties you can put macros directly in Excel cells, for example to get a density

=StrGD(1)

you may also calculate a table of values and then interpolate, if that is required in your procedure.

I have found that in most cases with rigorous properties and some little work in Excel I get results directly comparable with those of very expensive tools  and that's a great resource for me.

RE: Need Excel Calc for Linepack Calculation

linepacker, et. al.

in review of the Z function listing, i observed a possible anomaly.  i write this based on the attached document and that i have done many, literally hundreds, of these calcs using my trustworthy excel workbook and compared the results to company/industry results.  bear in mind that this document may be outdated, but i know it has been used by many people within the industry and by fellow co-employees.

the:   nx19_Ppsia = Ppsig + Patm

according to the attached document, the pressure should be in gage units and not absolute units.  i know this does not make sense and it may be a typo (i've not seen any errata).  keep in mind that the results using gage units have been compared with executable programs and the results matched identically.

my book was lent to someone else and i just got it back; hence the delay in responding.  believe me, i was concerned in that perhaps all my previous calcs were in error.  my only confirmation is that the results were compared with executable programs and the results matched.

take a moment and review the attached documentation.  perhaps you can investigate if there is an updated document or otherwise.  sorry, i cannot attach the entire document.

hope this helps.
-pmover

RE: Need Excel Calc for Linepack Calculation

It would make some sense, since the CNG method is also based on gauge pressure, there is some precedent for it.  Compressibility doesn't really become significant until you reach a couple hundred psi, including atm won't make much difference to the results.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources