Using Array Functions in Petroleum Office

Some Petroleum Office functions return multiple values at once. These use Excel's dynamic array feature — you enter the formula in one cell, and the results automatically "spill" into adjacent cells.

How Spill Ranges Work

When a function returns an array, Excel fills neighboring cells with the results:

• Horizontal spill — results fill cells to the right (across columns)
• Vertical spill — results fill cells downward (across rows)

You only type the formula in one cell. The spill range is indicated by a blue border around the output cells. If any cell in the spill range is occupied, you'll see a #SPILL! error — clear the blocking cells to fix it.

Examples

Curve Fitting (Vertical Spill)

The PO.DCA.Arps.Fit function fits an Arps decline model to production data and returns three parameters:

=PO.DCA.Arps.Fit(A1:A100, B1:B100)

Where A1:A100 contains time values and B1:B100 contains rate values. The function returns [Qi, Di, b] — three values that spill into three cells below the formula cell.

All decline models have .Fit variants that work the same way: PO.DCA.Duong.Fit, PO.DCA.PLE.Fit, etc.

Component Properties (Horizontal Spill)

The PO.EoS.Component.Props function returns thermodynamic properties of a pure component:

=PO.EoS.Component.Props("methane")

Returns [Tc, Pc, omega, Mw] — four values that spill horizontally into four cells to the right:

Flash Calculation Results (Variable Output)

The PO.EoS.Flash.PR function can return either a single value or an array, depending on the output parameter:

=PO.EoS.Flash.PR(T, P, zi, props, "V", kij)

• "V" or "L" — returns a single value (vapor or liquid fraction)
• "x" or "y" — returns a column array (liquid or vapor composition per component)
• "K" — returns a column array (K-values per component)

Drive Indices (Vertical Spill)

The PO.MBE.Drives.All function calculates all drive indices at once:

=PO.MBE.Drives.All(N, Eo, m, Eg, Efw, We, F)

Returns [DDI, GDI, WDI, CDI, Sum] — five values that spill vertically. The sum should be approximately 1.0.

Tips for Working with Array Functions

Avoid #SPILL! Errors

Make sure there are enough empty cells in the spill direction. If the spill range overlaps with existing data, Excel shows #SPILL!. Clear the blocking cells or move your formula.

Referencing Spill Results

You can reference individual results from a spill range using standard cell references. For example, if PO.DCA.Arps.Fit is in cell D1 and returns [Qi, Di, b] vertically:

• D1 = Qi (initial rate)
• D2 = Di (initial decline rate)
• D3 = b (hyperbolic exponent)

Spill Range Reference

Use the # operator to reference the entire spill range. If the formula is in D1:

=D1#

This references all cells in the spill range, which is useful when passing results to other functions.

Identifying Array Functions

Array-returning functions are noted in their IntelliSense description. Look for phrases like "returns array", "returns [...]", or "spill" in the function tooltip.