LOGEST

The array form of the assertion: LOGEST returns a 1x2 array {m, b} = {2, 1}. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values. ENTERED AS AN ARRAY FORMULA. The page is explicit -- "the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER" -- so the harness writes it as a legacy CSE array formula covering the whole result range, not as a plain scalar formula. Writing it as a scalar string makes even a fully supported matrix function return a single cell or #VALUE!, which would be a harness artefact rather than a finding.

The first element of the returned array is m, the base of y = b*m^x. On this data set it is exactly 2. Asserted as a scalar as well as inside the array so the value is checked even by a consumer that cannot spill an array. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

The page's Remarks give this exact formula: "When you have only one independent x-variable, you can obtain y-intercept (b) values directly by using the following formula: INDEX(LOGEST(known_y's,known_x's),2)". So the extraction is documented, not improvised; the two-index form INDEX(...,1,2) used here addresses the same element of the 1x2 result and is written out explicitly so the case tests LOGEST rather than INDEX's one-argument overload. On this data set b is exactly 1. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's." The x values supplied explicitly in the other cases ARE 1,2,3,4, so omitting them must give the identical answer, m = 2. That equality is the assertion: it cannot hold by coincidence. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m^x." On this data set b is 1 already, so constraining it changes nothing and m must remain exactly 2. Choosing data where the constraint is non-binding is deliberate: it turns a hard-to-verify optimiser result into an exact equality that any conforming engine must satisfy. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If stats is TRUE, LOGEST returns the additional regression statistics, so the returned array is {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r2,sey; F,df; ssreg,ssresid}", and refers the reader to LINEST for their meaning. Row 3 column 1 of that array is r^2. The data lie EXACTLY on the fitted curve, so the residual sum of squares is zero and r^2 is exactly 1 -- the one statistic in that block that is forced by the data rather than by an optimiser's choices, which is why it is the one asserted. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

The array form of the assertion: LOGEST returns a 1x2 array {m, b} = {2, 1}. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values. ENTERED AS AN ARRAY FORMULA. The page is explicit -- "the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER" -- so the harness writes it as a legacy CSE array formula covering the whole result range, not as a plain scalar formula. Writing it as a scalar string makes even a fully supported matrix function return a single cell or #VALUE!, which would be a harness artefact rather than a finding.

The first element of the returned array is m, the base of y = b*m^x. On this data set it is exactly 2. Asserted as a scalar as well as inside the array so the value is checked even by a consumer that cannot spill an array. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

The page's Remarks give this exact formula: "When you have only one independent x-variable, you can obtain y-intercept (b) values directly by using the following formula: INDEX(LOGEST(known_y's,known_x's),2)". So the extraction is documented, not improvised; the two-index form INDEX(...,1,2) used here addresses the same element of the 1x2 result and is written out explicitly so the case tests LOGEST rather than INDEX's one-argument overload. On this data set b is exactly 1. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's." The x values supplied explicitly in the other cases ARE 1,2,3,4, so omitting them must give the identical answer, m = 2. That equality is the assertion: it cannot hold by coincidence. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m^x." On this data set b is 1 already, so constraining it changes nothing and m must remain exactly 2. Choosing data where the constraint is non-binding is deliberate: it turns a hard-to-verify optimiser result into an exact equality that any conforming engine must satisfy. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If stats is TRUE, LOGEST returns the additional regression statistics, so the returned array is {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r2,sey; F,df; ssreg,ssresid}", and refers the reader to LINEST for their meaning. Row 3 column 1 of that array is r^2. The data lie EXACTLY on the fitted curve, so the residual sum of squares is zero and r^2 is exactly 1 -- the one statistic in that block that is forced by the data rather than by an optimiser's choices, which is why it is the one asserted. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

The array form of the assertion: LOGEST returns a 1x2 array {m, b} = {2, 1}. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values. ENTERED AS AN ARRAY FORMULA. The page is explicit -- "the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER" -- so the harness writes it as a legacy CSE array formula covering the whole result range, not as a plain scalar formula. Writing it as a scalar string makes even a fully supported matrix function return a single cell or #VALUE!, which would be a harness artefact rather than a finding.

The first element of the returned array is m, the base of y = b*m^x. On this data set it is exactly 2. Asserted as a scalar as well as inside the array so the value is checked even by a consumer that cannot spill an array. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

The page's Remarks give this exact formula: "When you have only one independent x-variable, you can obtain y-intercept (b) values directly by using the following formula: INDEX(LOGEST(known_y's,known_x's),2)". So the extraction is documented, not improvised; the two-index form INDEX(...,1,2) used here addresses the same element of the 1x2 result and is written out explicitly so the case tests LOGEST rather than INDEX's one-argument overload. On this data set b is exactly 1. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's." The x values supplied explicitly in the other cases ARE 1,2,3,4, so omitting them must give the identical answer, m = 2. That equality is the assertion: it cannot hold by coincidence. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m^x." On this data set b is 1 already, so constraining it changes nothing and m must remain exactly 2. Choosing data where the constraint is non-binding is deliberate: it turns a hard-to-verify optimiser result into an exact equality that any conforming engine must satisfy. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.

Excel documents: "If stats is TRUE, LOGEST returns the additional regression statistics, so the returned array is {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r2,sey; F,df; ssreg,ssresid}", and refers the reader to LINEST for their meaning. Row 3 column 1 of that array is r^2. The data lie EXACTLY on the fitted curve, so the residual sum of squares is zero and r^2 is exactly 1 -- the one statistic in that block that is forced by the data rather than by an optimiser's choices, which is why it is the one asserted. WHY THIS DATA SET. Microsoft's LOGEST page publishes its worked example as an IMAGE ("Example 1 -- LOGEST function") with no readable figures, so there is no published result to reproduce and none is invented. Instead the assertions are made on a data set whose answer is forced and exact: y = 2, 4, 8, 16 at x = 1, 2, 3, 4 is exactly y = 1 x 2^x, so ln(y) = x ln(2) is an EXACT straight line through the origin. LOGEST fits y = b*m^x by least squares on ln(y), and a least-squares fit to points that already lie exactly on a line returns that line, so m = 2 and b = 1 with zero residual -- integers, not rounded decimals, and true of every conforming implementation rather than of one engine's optimiser. The page's own statement that "The array that LOGEST returns is {mn,mn-1,...,m1,b}" fixes the order of the two values.