PO.Stats.AARE


Description

How far a correlation's values fall from measured ones, as one number: the average absolute relative error, in percent. It is the score PVT and flow-correlation studies rank correlations by, because a relative error lets a 50 scf/STB miss at 100 scf/STB count for more than the same miss at 800, and the absolute value stops misses on either side from cancelling. Give it the observed values and the computed ones, of the same shape: a column of lab points and the column a correlation gives at them, or two single values. A pair is scored only when both are numbers and the observed value is not zero, so a lab table can carry blank rows and a correlation can leave a point out. With no pair to score it returns #N/A.

Formula

=LAMBDA(observed, computed, LET( use, ISNUMBER(observed) * ISNUMBER(computed) * (IFERROR(observed, 0) <> 0), k, SUM(use), IF(k = 0, NA(), 100 * SUM(use * IFERROR(ABS((computed - observed) / observed), 0)) / k)))

Parameters

Parameter Unit Description
observed any The measured values: one value, or a column or row of them (blanks and text skipped)
computed same as observed The values to score, the same shape as observed

Examples

Invocation Expected
=PO.Stats.AARE(2620, 2578.201719) 1.595354
=PO.Stats.AARE({100;200;400}, {110;190;400}) 5
=PO.Stats.AARE({100;"";400}, {110;300;380}) 7.5
=PO.Stats.AARE({0;100}, {5;90}) 10
=PO.Stats.AARE({768;628;499}, {811.062236;658.560115;"n/a"}) 5.236661

Need a custom LAMBDA? Suggest a New Lambda