DSTDEV

Microsoft publishes this example's result as 2.96648. Derived independently from the documented definition ("Estimates the standard deviation of a population based on a sample"). 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 and squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 summing to 35.2; the sample variance is 35.2/4 = 8.8 and the sample standard deviation is sqrt(8.8) = 2.9664793948382653. Rounded to the five decimals the page prints, that is exactly 2.96648, so the published figure is reproduced. SIBLING PAGES DISAGREE ABOUT THE DOCUMENTED RANGE, AND ONE OF THE TWO READINGS CANNOT WORK. All six database-function pages in this batch (DCOUNTA, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP) print a byte-identical example table, but they cite two different database ranges for it: DCOUNTA, DVAR and DVARP write A4:E10 while DPRODUCT, DSTDEV and DSTDEVP write A5:E11 -- and DSTDEV repeats its version in prose ("if the data in A5:E11 is only a sample"). For that table pasted at A1 the database header row (Tree/Height/Age/Yield/Profit) is row 4 and the last record is row 10, so A4:E10 is the reading that works; A5:E11 starts one row BELOW the header, which leaves the field label "Yield" unresolvable and could not produce any of the six published results. Every one of those results is reproduced by the A4:E10 reading, which is the one used here (shifted two rows, as A6:E12). 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".

Derived independently from the documented definition ("Estimates the standard deviation of a population based on a sample"). 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 and squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 summing to 35.2; the sample variance is 35.2/4 = 8.8 and the sample standard deviation is sqrt(8.8) = 2.9664793948382653. sqrt(8.8) = 2.9664793948382653 rounds to 2.966479395 at 9 dp. Asserted at the deeper precision because the value is derived here rather than copied from the page. 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".

Microsoft documents the field argument as accepting a 1-based column position; column 4 of A6:E12 is Yield, so this must equal the 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 DSTDEV page, which lists no error conditions at all; derived from the documented definition instead. DSTDEV "estimates the standard deviation of a population based on a sample", i.e. the n-1 denominator estimator, and the criteria block in H3:H4 selects exactly one record (the cherry), making that denominator zero. Excel's documented behaviour for the same n-1-denominator situation in its non-database sibling STDEV is #DIV/0!, and that is the value asserted here; an engine returning 0 instead is claiming a sample of one has no spread rather than an undefined one. 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 the #DIV/0! this estimator's zero denominator calls for -- an error-CLASS collapse (a division by zero reported as an out-of-range argument), not a computation difference. Both engines agree that the case is an error; they disagree about which one, exactly as batch B recorded for COT(0).

Microsoft publishes this example's result as 2.96648. Derived independently from the documented definition ("Estimates the standard deviation of a population based on a sample"). 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 and squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 summing to 35.2; the sample variance is 35.2/4 = 8.8 and the sample standard deviation is sqrt(8.8) = 2.9664793948382653. Rounded to the five decimals the page prints, that is exactly 2.96648, so the published figure is reproduced. SIBLING PAGES DISAGREE ABOUT THE DOCUMENTED RANGE, AND ONE OF THE TWO READINGS CANNOT WORK. All six database-function pages in this batch (DCOUNTA, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP) print a byte-identical example table, but they cite two different database ranges for it: DCOUNTA, DVAR and DVARP write A4:E10 while DPRODUCT, DSTDEV and DSTDEVP write A5:E11 -- and DSTDEV repeats its version in prose ("if the data in A5:E11 is only a sample"). For that table pasted at A1 the database header row (Tree/Height/Age/Yield/Profit) is row 4 and the last record is row 10, so A4:E10 is the reading that works; A5:E11 starts one row BELOW the header, which leaves the field label "Yield" unresolvable and could not produce any of the six published results. Every one of those results is reproduced by the A4:E10 reading, which is the one used here (shifted two rows, as A6:E12). 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".

Derived independently from the documented definition ("Estimates the standard deviation of a population based on a sample"). 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 and squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 summing to 35.2; the sample variance is 35.2/4 = 8.8 and the sample standard deviation is sqrt(8.8) = 2.9664793948382653. sqrt(8.8) = 2.9664793948382653 rounds to 2.966479395 at 9 dp. Asserted at the deeper precision because the value is derived here rather than copied from the page. 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".

Microsoft documents the field argument as accepting a 1-based column position; column 4 of A6:E12 is Yield, so this must equal the 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 DSTDEV page, which lists no error conditions at all; derived from the documented definition instead. DSTDEV "estimates the standard deviation of a population based on a sample", i.e. the n-1 denominator estimator, and the criteria block in H3:H4 selects exactly one record (the cherry), making that denominator zero. Excel's documented behaviour for the same n-1-denominator situation in its non-database sibling STDEV is #DIV/0!, and that is the value asserted here; an engine returning 0 instead is claiming a sample of one has no spread rather than an undefined one. 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 the #DIV/0! this estimator's zero denominator calls for -- an error-CLASS collapse (a division by zero reported as an out-of-range argument), not a computation difference. Both engines agree that the case is an error; they disagree about which one, exactly as batch B recorded for COT(0).

Microsoft publishes this example's result as 2.96648. Derived independently from the documented definition ("Estimates the standard deviation of a population based on a sample"). 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 and squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 summing to 35.2; the sample variance is 35.2/4 = 8.8 and the sample standard deviation is sqrt(8.8) = 2.9664793948382653. Rounded to the five decimals the page prints, that is exactly 2.96648, so the published figure is reproduced. SIBLING PAGES DISAGREE ABOUT THE DOCUMENTED RANGE, AND ONE OF THE TWO READINGS CANNOT WORK. All six database-function pages in this batch (DCOUNTA, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP) print a byte-identical example table, but they cite two different database ranges for it: DCOUNTA, DVAR and DVARP write A4:E10 while DPRODUCT, DSTDEV and DSTDEVP write A5:E11 -- and DSTDEV repeats its version in prose ("if the data in A5:E11 is only a sample"). For that table pasted at A1 the database header row (Tree/Height/Age/Yield/Profit) is row 4 and the last record is row 10, so A4:E10 is the reading that works; A5:E11 starts one row BELOW the header, which leaves the field label "Yield" unresolvable and could not produce any of the six published results. Every one of those results is reproduced by the A4:E10 reading, which is the one used here (shifted two rows, as A6:E12). 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".

Derived independently from the documented definition ("Estimates the standard deviation of a population based on a sample"). 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 and squared deviations 19.36, 0.16, 0.16, 2.56, 12.96 summing to 35.2; the sample variance is 35.2/4 = 8.8 and the sample standard deviation is sqrt(8.8) = 2.9664793948382653. sqrt(8.8) = 2.9664793948382653 rounds to 2.966479395 at 9 dp. Asserted at the deeper precision because the value is derived here rather than copied from the page. 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".

Microsoft documents the field argument as accepting a 1-based column position; column 4 of A6:E12 is Yield, so this must equal the 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 DSTDEV page, which lists no error conditions at all; derived from the documented definition instead. DSTDEV "estimates the standard deviation of a population based on a sample", i.e. the n-1 denominator estimator, and the criteria block in H3:H4 selects exactly one record (the cherry), making that denominator zero. Excel's documented behaviour for the same n-1-denominator situation in its non-database sibling STDEV is #DIV/0!, and that is the value asserted here; an engine returning 0 instead is claiming a sample of one has no spread rather than an undefined one. 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 the #DIV/0! this estimator's zero denominator calls for -- an error-CLASS collapse (a division by zero reported as an out-of-range argument), not a computation difference. Both engines agree that the case is an error; they disagree about which one, exactly as batch B recorded for COT(0).