DVAR

Microsoft publishes this example as '=DVAR(A6:E12, "Yield", A3:A5)' with the result 8.8. Derived independently: Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. Their yields are 14, 10, 10, 8, 6 with mean 9.6; the squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 sum to 35.2, and the sample variance (n-1 denominator, matching the documented "only a sample of the total orchard population") is 35.2/4 = 8.8 exactly. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Column 4 of A6:E12 is Yield, so the positional field form must equal the documented label form. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Not spelled out on the DVAR page, which lists no error conditions; derived from the documented n-1 sample estimator, whose denominator is zero when the criteria select a single record (here the cherry). Excel's documented behaviour in the same situation for the non-database sibling VAR is #DIV/0!. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear". EXECUTED RESULT: all four LibreOffice builds return #NUM! rather than #DIV/0!, the same error-class collapse as DSTDEV_single_record_div0 on the same one-record sample.

Microsoft publishes this example as '=DVAR(A6:E12, "Yield", A3:A5)' with the result 8.8. Derived independently: Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. Their yields are 14, 10, 10, 8, 6 with mean 9.6; the squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 sum to 35.2, and the sample variance (n-1 denominator, matching the documented "only a sample of the total orchard population") is 35.2/4 = 8.8 exactly. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Column 4 of A6:E12 is Yield, so the positional field form must equal the documented label form. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Not spelled out on the DVAR page, which lists no error conditions; derived from the documented n-1 sample estimator, whose denominator is zero when the criteria select a single record (here the cherry). Excel's documented behaviour in the same situation for the non-database sibling VAR is #DIV/0!. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear". EXECUTED RESULT: all four LibreOffice builds return #NUM! rather than #DIV/0!, the same error-class collapse as DSTDEV_single_record_div0 on the same one-record sample.

Microsoft publishes this example as '=DVAR(A6:E12, "Yield", A3:A5)' with the result 8.8. Derived independently: Criteria A3:A5 is the Tree column header plus the two tree names, so it selects the five apple and pear records (yields 14, 10, 10, 8 and 6) and excludes the single cherry. Their yields are 14, 10, 10, 8, 6 with mean 9.6; the squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 sum to 35.2, and the sample variance (n-1 denominator, matching the documented "only a sample of the total orchard population") is 35.2/4 = 8.8 exactly. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Column 4 of A6:E12 is Yield, so the positional field form must equal the documented label form. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear".

Not spelled out on the DVAR page, which lists no error conditions; derived from the documented n-1 sample estimator, whose denominator is zero when the criteria select a single record (here the cherry). Excel's documented behaviour in the same situation for the non-database sibling VAR is #DIV/0!. Set up from Microsoft's own worked example for the database functions: the orchard table, which is one criteria header row, two criteria rows, a database header row and six records. HARNESS PLACEMENT: the page pastes the table at A1, but this harness writes the formula under test into cell F1, which the table's second "Height" criteria header would occupy, so the whole block is pasted two rows lower instead. Every relative offset the page describes is preserved -- the database header is still the row immediately below the last criteria row -- which puts the criteria at A3:F5 and the database at A6:E12 here. One further deviation: the page writes its tree criteria as the cell entry ="=Apple" (the documented trick for storing the literal text =Apple, which forces an exact match). openpyxl cannot author that cell -- any string beginning with = is serialized as a formula -- so the criteria cells here hold the plain text Apple and Pear. On this data the two forms select the same records, because no other tree name in the table begins with "Apple" or "Pear". EXECUTED RESULT: all four LibreOffice builds return #NUM! rather than #DIV/0!, the same error-class collapse as DSTDEV_single_record_div0 on the same one-record sample.