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.
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
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
RE: Need Excel Calc for Linepack 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
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
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
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
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
RE: Need Excel Calc for Linepack Calculation
(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[°]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[°]F
although I think using gauge and log temps make no real difference to the end result in this case, log temperature = 91[°]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
Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso
RE: Need Excel Calc for Linepack Calculation
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
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
Use this one, rev. 1
Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso
RE: Need Excel Calc for Linepack Calculation
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
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.
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
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
For gas mixtures Stoner recommends using BWRS equation of state.
Stoner has 28 predefined components to select from.
Quoting the user manual,
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.
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
RE: Need Excel Calc for Linepack Calculation
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
RE: Need Excel Calc for Linepack Calculation
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.a
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
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
http
Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso
RE: Need Excel Calc for Linepack Calculation
And yeah, those two links are way beyond the scope of my project!
RE: Need Excel Calc for Linepack Calculation
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
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
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
Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso