Upgrading Workbooks from v2.3 to v2.4


Every function name from v2.3 still exists in v2.4; nothing was removed or renamed, and 39 functions were added. v2.4 runs on version 6.4.0 of the Petroleum Office engineering libraries, which checked each correlation against its published reference again. Most workbooks open and recalculate without edits. Some formulas do need attention:

  • seven functions take different arguments;
  • several results change because v2.3 calculated them wrongly, or used a different form of the method;
  • functions refuse unsorted or repeated data, and equation-of-state saturation functions return an error when they find no saturation point;
  • eleven updated blueprints need the v2.4 add-in, and three blueprints saved under v2.3 should be inserted again;
  • blueprints and workbooks built on LAMBDAs or dynamic arrays need Microsoft 365 or Excel 2024.

Work through the checklist below, then re-check any design whose numbers come from the corrected functions.

Checklist

  1. Check your Excel version if you use LAMBDA-based blueprints, lambdas or workbooks.
  2. Edit formulas that call one of the seven functions with new arguments. Start with PO.RP.Load.MPRL: it is the one that gives a wrong number with no error.
  3. Re-check designs built on changed results: rod pumps, ESP motors, chokes, multiphase flow, inflow performance, fracturing, decline, aquifers, relative permeability and equation-of-state saturation points.
  4. Expect #VALUE! or #NUM! where data or a saturation solve is now refused.
  5. Insert again the blueprints saved under v2.3 that call a changed function.
  6. Update lambdas saved in your workbooks.

Finding the formulas to check

Use Excel's Find (Ctrl+F) → Options → Within: Workbook, Look in: Formulas, and search for a function name such as PO.RP.Load.MPRL, or a family prefix such as PO.RP., PO.ESP. or PO.SF.CH.. Find All lists every cell that uses it.

Keep a copy of the workbook as it was in v2.3, so you can compare numbers after the upgrade.

Excel version

The add-in and its functions still run in Excel 2016 or later. Content built on newer Excel features does not:

  • Microsoft 365 or Excel 2024 is needed for blueprints, lambdas and workbooks that use LAMBDA, LET, TAKE/DROP or the dynamic-array functions. Older Excel shows #NAME? in those cells.
  • The Blueprint Manager and each blueprint's page on this site say "Microsoft 365" on the blueprints that need it. Every LAMBDA-based blueprint is among them.
  • In Microsoft 365, downloaded blueprints and workbooks now open without @ signs in their formulas, and keep their values when opened on a machine without the add-in.

Functions with new arguments

Argument units are as shown in each function's description in the Function Browser.

Function v2.3 v2.4 What to change
PO.RP.Load.MPRL (Wrf, F2, Skr) (Wrf, F2_over_Skr, Skr) Same positions, new meaning: the second argument is the API RP 11L chart ratio F2/Skr, and the third is the load scale Skr = S·kr in lbf (v2.3's last argument was the dimensionless pump speed). MPRL = Wrf − (F2/Skr)·Skr
PO.RP.Load.PPRL (Wrf, Fo, F1, Skr) (Wrf, F1_over_Skr, Skr) Delete the fluid load Fo. The second argument is the chart ratio F1/Skr, and the third is Skr = S·kr in lbf. PPRL = Wrf + (F1/Skr)·Skr
PO.ESP.Motor.Qcool (HP, motor_OD, casing_ID) (motor_OD, casing_ID) Delete the first argument, HP; the cooling rate depends only on the annulus. The value also changes: about ×1.20, now exactly 1 ft/s past the motor housing
PO.MBE.Ce.Geopressured (cw, cf, Sw, Rsw, Bg) (cw, cf, Sw, dRsw_dp, Bg, Bw) The fourth argument is now the slope dRsw/dp [scf/(psi·STB)], not Rsw; append the water FVF Bw [bbl/STB]. v2.3 added Rsw·Bg, a volume ratio, to a compressibility, which read about 700 times too high
PO.SF.CH.Sachdeva.Pwh, PO.SF.CH.AshfordPierce.Pwh (Ql, d_choke, GLR) (Ql, P_dn, d_choke, GLR, API, SG_gas, WC, T) Insert the downstream pressure second and add the fluid. v2.3 assumed a downstream pressure of 70 % of the answer and a fixed fluid: 35 °API, gas gravity 0.75, no water, 100 °F
PO.SF.CH.Sachdeva.Size, PO.SF.CH.AshfordPierce.Size (Pwh, Ql, GLR) (P_up, P_dn, Ql, GLR, API, SG_gas, WC, T) Insert the downstream pressure second and add the fluid. Passing 0.7*P_up and 35, 0.75, 0, 100 reproduces a v2.3 size

The Pwh and Size functions now take the same inputs as their .Rate function and invert it exactly, for any fluid: put the result back into .Rate and you get your rate.

What a v2.3 formula does in v2.4. Excel passes arguments by position, so the old values land in the wrong parameters:

  • PO.RP.Load.PPRL, PO.ESP.Motor.Qcool, PO.MBE.Ce.Geopressured and the three-argument choke Pwh and Size calls return #VALUE!, because the number of arguments no longer matches.
  • PO.RP.Load.MPRL returns a wrong number with no error. It is the one function that keeps its v2.3 number of arguments. It reads the old pump speed as Skr in lbf, so PO.RP.Load.MPRL(5000, 0.2, 0.3) returns 4,999.94, about the rod weight. Search for it first.

PO.RP.Load.PPRL.Simple and PO.RP.Load.MPRL.Simple keep their arguments.

The .SI lambdas changed the same way:

  • PO.ESP.Motor.Qcool.SI(motor_OD, casing_ID);
  • PO.RP.Load.PPRL.SI(Wrf, F1_over_Skr, Skr) and PO.RP.Load.MPRL.SI(Wrf, F2_over_Skr, Skr), with Skr in N;
  • PO.MBE.Ce.Geopressured.SI(cw, cf, Sw, dRsw_dp, Bg, Bw);
  • the four choke Pwh.SI and Size.SI lambdas, which take the same eight inputs as .Rate.SI.

New optional arguments

Existing calls are unchanged; leaving an optional argument out reproduces v2.3. Function Arguments and IntelliSense show optional arguments in brackets.

  • PO.GL.Valve.Pvc and PO.GL.Valve.TRO: [St], the valve spring;
  • PO.GL.Valve.ThornhillCraver: [k], the gas specific-heat ratio;
  • PO.RP.Pump.Stroke.Eff: [ep], plunger overtravel;
  • PO.ESP.Curve.Head, PO.ESP.Curve.Efficiency and PO.ESP.Curve.Power: [extrapolate];
  • PO.Spline.Cubic.Intersect and PO.Spline.Linear.Intersect: [occurrence]. The new .Intersect.All returns every crossing;
  • PO.EoS.Kij.Matrix: T becomes [T]. 'Chueh-Prausnitz' doesn't use it, and 'Nikos' and 'Elliot-Daubert' refuse a missing T, because their kij depend on it. For 'Chueh-Prausnitz', add the critical volume Vc [m³/mol] as a fifth column of props.

One difference: an error value such as #N/A passed to an optional argument now returns #VALUE!; v2.3 treated it as omitted.

Changed results

These functions take the same arguments but return different numbers for the same inputs, because v2.3 was wrong or used another form of the method. Each was checked against the reference the function cites. Re-run any design that depends on them.

Same arguments, different method

Function Change from v2.3
PO.PVT.Rs.ByStanding Now Standing (1947), the exact inverse of PO.PVT.Pb.ByStanding; v2.3 used the Standing (1981) form. Example: γg 0.72, 33 °API, 190 °F at 2,447.2 psia goes from 518.11 to 520.00 scf/STB. For the v2.3 result, use the new PO.PVT.Rs.ByStanding1981
PO.SCAL.Stone.I.Kro Applies Stone's (1 − Swn)(1 − Sgn) normalisation (Aziz and Settari), so it returns Krow at Sg = 0 and Krog at Sw = Swi. v2.3 read Kro low everywhere. Example: Swi 0.20, Som 0.10 at Sw 0.35, Sg 0.15 goes from 0.1410 to 0.2285. A water saturation below Swi now returns #VALUE!

Corrected results

Area Change from v2.3
Rod pump Rod weights +0.20 % (rod steel 490 lb/ft³), so buoyant weights, stretch and the .Simple PPRL and MPRL move too; pump displacement ×1.27, and fillage now falls below 1
ESP Hydraulic and brake horsepower ×1.004; PO.ESP.Motor.Qcool ×1.20; PO.ESP.Gas.Void.Full passes Bg and Bw to the right inputs
PVT Gas compressibility (an example goes from 4.21×10⁻⁴ to 5.11×10⁻⁴ 1/psi); Standing pseudo-critical pressure (+10 psia below gas gravity 0.75) and temperature; PO.PVT.Z.ByDAK by at most about 1×10⁻⁶
Multiphase flow PO.VFP.HagedornBrown.* (the modified method), PO.VFP.Orkiszewski.*, PO.VFP.Ansari.* (slug flow) and PO.VFP.PoettmannCarpenter.*; every PO.VFP.*.Pin by up to about 0.02 psi
Pipelines and chokes Liquid pipeline pressure drop ÷62.5 and its rate ×9.5; Sachdeva rates; the Ashford-Pierce Pwh solve
Inflow PO.IPR.HW.SS.PI.ByEconomides rises with vertical permeability kz; PO.IPR.HW.SS.PI.ByGRJ (an example goes from 32.50 to 44.58)
Fracturing KGD general width −11 %, PKN volume +12.5 %, PKN average width −25 %, radial net pressure ÷2, radial radius with leakoff ×2.37
Material balance Fetkovich aquifer J with the closed-aquifer default (an example goes from 21.99 to 41.19); van Everdingen-Hurst −0.19 %
Decline PLE and EEDCA cumulative and EUR (an EEDCA asymptotic EUR goes from 12,000 to 10,989); Bourdet b; the Z-score cleaner follows the trend; EEDM and LGM EUR honour Q_econ
Relative permeability PO.SCAL.IK.Sand.GasOil.Krg up to 24 % higher (an example goes from 0.1556 to 0.1931)
Equation of state 'Nikos' kij (N2–C2 was −0.588, now 0.010; N2–C1 and CO2–C1 are no longer 0); estimated kij seeds; bubble and dew temperatures. PO.EoS.Tb.PR finds the bubble point where a strong kij used to send it to a liquid-liquid edge: the Phase Envelope crude at 1,000 psig goes from −194 °F to −14.1 °F. The phase envelope's critical point moves from 1,201.9 to 1,205.9 psia and 713.8 to 714.2 °F

Refused inputs

Some calls that returned a number in v2.3 now return an error, because the number was not supported by the method.

  • Unsorted or repeated data. Interpolation and other functions that need sorted samples refuse unsorted, repeated or out-of-range samples with #VALUE!. Sort the data, or remove the duplicate rows.
  • Derivatives. The derivative functions answer only at a sample point, and in PO.Math.DataDiff the l argument means what its description says.
  • Saturation points. PO.EoS.Pb.PR, PO.EoS.Pdew.PR, PO.EoS.Tb.PR and the other saturation functions, PR and SRK, return #NUM! when the solve doesn't converge. In v2.3 they could return a value that was not a saturation point. PO.EoS.Tb.PR also returns #NUM! when the only edge it finds is between two liquids.
  • Well-test production data is checked before use, and a production history may now start after t = 0.

When a cell shows one of these errors, open the function in the Function Browser. Its description states what it accepts.

Blueprints

A blueprint inserted under v2.3 lives in your workbook and keeps its v2.3 formulas.

  • Most keep working. Their numbers move only where a function they call was corrected (see Changed results).
  • Three should be inserted again, because they call a function that changed:
    • ESP Gas Handling Analysis gives a wrong void fraction with no error (PO.ESP.Gas.Void.Full);
    • Effective Compressibility Calculations and Geopressured Gas p/z Analysis return #VALUE! (PO.MBE.Ce.Geopressured).
  • Eleven blueprints were rebuilt on the new v2.4 functions: five well-test analyses on PO.PTA.Horner.*, the two hydrate blueprints on PO.FA.Hydrate.Teq, the two aquifer blueprints on PO.MBE.Aq.Fet.We and PO.MBE.Aq.VEH.We, the p/z analysis on PO.MBE.Gas.OGIP.Reg, and the fluid model setup on PO.EoS.Pseudo.Props. A copy you inserted under v2.3 keeps running. Insert it again to get the exact methods; the hydrate, aquifer, p/z and fluid-model numbers move.
  • A v2.3 add-in can't insert those eleven. Once the v2.4 content is live, inserting one from v2.3 shows #NAME?, because the functions they call are new in v2.4. Upgrade the add-in first.

Lambdas saved in your workbooks

A lambda you installed in v2.3 lives in that workbook's Name Manager and keeps its v2.3 definition. This matters for the .SI lambdas of the functions with new arguments, and for any lambda whose function was corrected. To update one:

  1. Open Formulas → Name Manager and delete the lambda's name, for example PO.ESP.Motor.Qcool.SI.
  2. Insert it again from the Lambda Manager pane.
  3. Edit the formulas that call it, as in Functions with new arguments.

See Using Lambdas in Petroleum Office for how lambdas are installed. The upgrade from v2.2 is covered in Upgrading Workbooks from v2.2 to v2.3.

Tags:
migration