PO.VFP.Corr.Match


Description

Which flow correlation fits this well? The LAMBDA scores the pressures several correlations predict against what a gauge measured, by the average absolute relative error (AARE), and returns either one correlation's score or the best one. The predictions are computed on the sheet first, one row per correlation (a `PO.VFP.*.Pin` or `.Pout` each), and one column per measured point: a single flowing bottomhole pressure from a downhole gauge, or the stations of a flowing gradient survey. It takes an oil well's correlations or a gas well's alike. A row holding anything that isn't a number, such as a correlation the sheet leaves out because it doesn't apply, is not a candidate.

Formula

=LAMBDA(predicted, measured, row, LET( n, ROWS(predicted), m, COLUMNS(predicted), i, INT(row), ones, SEQUENCE(m, 1, 1, 0), num, --ISNUMBER(predicted), err, IFERROR(ABS(predicted-measured)/measured, 0), full, IF(m=1, num, MMULT(num, ones))=m, aare, IF(m=1, err, MMULT(err, ones))/m, k, COUNT(measured), valid, AND(IFERROR(ROWS(measured), 1)=1, OR(k=1, k=m), IFERROR(COLUMNS(measured), 1)=k, MIN(measured)>0), IF(OR(NOT(valid), i<0, i>n, row<>i), NA(), IF(i=0, MATCH(1, full*(aare=MIN(FILTER(aare, full, NA()))), 0), IF(SUM((SEQUENCE(n)=i)*full)=1, SUM((SEQUENCE(n)=i)*aare), NA())))))

Parameters

Parameter Unit Description
predicted psi The predicted pressures: one row per correlation, one column per measured point
measured psi The measured pressures: one value, or one row with a value per column of predicted; all above 0
row — 1 to the number of rows gives that row's AARE; 0 gives the row (1 first) with the least AARE

Examples

Invocation Expected
=PO.VFP.Corr.Match({2033;1680;1960;1847;2014;2213;1955;1636;1413}, 2056, 0) 1
=PO.VFP.Corr.Match({2033;1680;1960;1847;2014;2213;1955;1636;1413}, 2056, 5) 0.020428016
=PO.VFP.Corr.Match({500,1000,1500;520,980,1450}, {510,1010,1490}, 1) 0.012073414
=PO.VFP.Corr.Match({500,1000,1500;520,980,1450}, {510,1010,1490}, 2) 0.025385484
=PO.VFP.Corr.Match({500,1000,1500;520,980,1450}, {510,1010,1490}, 0) 1
=PO.VFP.Corr.Match({2000;"n/a";2050}, 2056, 0) 3
=PO.VFP.Corr.Match({2450;2525;2390}, 2500, 3) 0.044
=PO.VFP.Corr.Match({2000;"n/a";2050}, 2056, 3) 0.002918288

Need a custom LAMBDA? Suggest a New Lambda