SQRT

What it does

SQRT(number)

SQRT(number) returns the nonnegative real square root. A positive input has two mathematical real roots; this function chooses the positive one. Zero returns zero. Microsoft documents #NUM! for a negative input.

Try it

=SQRT(81) returns 9; =SQRT(2) is about 1.414213562373095. Squaring the returned binary64 value need not recover the input exactly.

=SQRT(-9) gives #NUM!. =SQRT(ABS(-9)) returns three because it changes the input to positive nine. Use IMSQRT when a complex root of the original negative value is intended.

The retained build 20430 controls give GEOMEAN(1,2)=1.414213562373095 and SQRT(2)=1.4142135623730951, adjacent binary64 values. FN-GEOMEAN-001 retains the exact cell inputs and both answers. Their mathematical values agree, but the recorded calculation routes do not publish the same bits.

Quirks

Recorded worksheet SQRT uses a root rounded to a 64-bit significand followed by a binary64 store. That can differ by a final bit from one correctly rounded binary64 square root. In the exact-cell control 0x3fefffffffffffff (0.9999999999999999), Excel returns one; a single-rounding mathematical reference returns the preceding binary64 value.

The smallest-subnormal reproduction returned zero, but its cell input was not read back. That observation does not establish a square-root underflow rule: input transfer may already have changed it to zero. A positive finite root itself fits the binary64 normal range even for the smallest positive binary64 input.

Compositions need their own operation order. Squaring two large components before SQRT can overflow when a scaled hypotenuse would be finite. A general POWER route or a logarithm/exponential composition also has different intermediate rounding.

How to implement

For the finite nonnegative scalar route, reject negative inputs before the root and return zero for zero. For positive x:

1. Find the exponent of the root by halving floor(log2(x)), rounding downward. Obtain the input exponent from its bits or exact comparisons.
2. Round the exact square root to a 64-bit significand, ties-to-even. The portable staged-root recipe finds this with an integer square root and an exact comparison against a squared midpoint. It needs no host extended floating-point type.
3. Store that rounded root to binary64, again choosing nearest/even. Keep this second step; directly calling a binary64 sqrt removes the recorded first rounding.

Worked boundary: x=1-2^-53. The midpoint between its two possible binary64 root results is m=1-2^-54. Since m*m=x+2^-108, the true root is slightly below m. Rounding it first to 64 significant bits gives m; the binary64 midpoint tie then selects one, whose low significand bit is even. A direct binary64 root instead selects 1-2^-53.

FN-SQRT-001 reports 41,897 matching distinct normal-positive recorded inputs, including all 164 controls where the staged and single-rounding roots differ. Its reproductions name Excel build 20430 on Windows 64-bit. These results support the stated staged route on that evidence, with no published Handbook suite or whole-domain equality claim. The input route and worksheet publication remain separate from the root primitive.

Rust code

29 sqrt "SQRT" (1, 1) [V "number"] |s, a, sv| { unary(s, a, k::sqrt_fn::sqrt_kernel) }

pub fn sqrt_kernel(n: f64) -> Result<f64, WorksheetErrorCode> {
    if n < 0.0 {
        return Err(WorksheetErrorCode::Num);
    }
    Ok(crate::ported::excel_numeric::excel_sqrt_staged(n))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

• XC-SQRT-001: build 19929 versus build 20430; 2 same-input pairs, 0 differing answers. Compared axes: excel-build. Numeric answers were compared by their recorded binary64 bits; other answers by typed value.

Agreement on these compared inputs does not establish agreement on unmeasured inputs.

Recorded environments and comparison details.

Retained controls from the indexed captures:

These are earlier recorded answers, not new Excel runs or an additional cross-build comparison. The function JSON retains each source locator, exact argument bits where available, and input-route status. Call notation does not imply that captured binary64 values were entered as decimal formula literals.

Alternatives

IMSQRT accepts complex inputs. SQRTPI includes a multiplication by pi, and IMABS uses a hypotenuse route for complex magnitude. POWER accepts other exponents but must retain its own numerical graph.

Named variants

No named variants are recorded yet.