SLOPE Function (Possible) Bug, How to Fix it?

Question

Level 1

33 points

SLOPE Function (Possible) Bug, How to Fix it?

Both Apple Numbers & Microsoft Excel have SLOPE function.

Apple Numbers SLOPE function appears to have a bug and does not calculate correctly.

If you re-create this simple calculation in Apple Numbers (scroll down) as shown in example on Microsoft Excel website, you will get different result using exactly same calculation. This means that Apple Numbers are buggy, or at least SLOPE function returns inaccurate results.

Test it for yourself:

How can we fix SLOPE function so we get accurate results?

iMac (27-inch, Late 2013), macOS Sierra (10.12.1)

Posted on Oct 22, 2017 1:52 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

Oct 22, 2017 5:34 PM in response to DanielDay

Is the table shown above an Excel table?

Here's the same data, shown in a Numbers table:

Entering the data as shown (assuming all data in column A are dates in January, 1900) gives the following result in C11:

0d 7h 20m 0s 0ms

Obviously not what you expected, but it is a correct result given the data supplied to the formula.

Replacing the Dates in column A with the day numbers in column C, then using the same SLOPE formula (with the column A reference replaced with column C) gives the result in C10—the same result as you are getting in Excel:

0.305555555555556

Which is also a correct result, given the data supplied to the formula.

Cell B11 contains the result of the multiplication of the slope, expressed as a number (C10) by a duration of one day (24h), As you can see, it is the dame as the result in C11.

"Dates" in Numbers are always date and time values, with the time set to 00:00:00 (midnight at the beginning of that day).

differences between dates are durations.

Division of a set of durations be a set of numbers (s done by SLOPE in C11) results in a Duration.

Regards,

Barry

Reply

Answer 2

SGIII

Level 8

37,562 points

Oct 23, 2017 10:01 AM in response to DanielDay

Hi Daniel,

Note that this results from a difference in the way Excel and Numbers store date-time values. Excel stores as a serial number-- the number of days after 1 Jan 1900 (or in some systems after 1 Jan 1904). Numbers stores date-time strings, and uses a Duration data format to express differences between data-times. You can use the DUR2DAYS function (which Excel doesn't have) to arrive at an direct equivalence with the example given for Excel:

SG

Reply

Answer 3

DanielDay Author

Level 1

33 points

Oct 22, 2017 5:30 PM in response to Barry

Hi Barry,

Let's reproduce the above example from Microsoft Excel in Apple Numbers. We should be getting this Result: 0.305556

In Apple Numbers we get this (we selected A2:A8 for "y" and B2:B8 for "x") and we got this:

Now, if we try using SLOPE function and selecting in reverse, B2:B8 for "y" and A2:A8 for "x" we get this result:

None of the 2 above results are accurate.

Here is the result that Microsoft Excel returns using same data and same function:

As you can see, SLOPE function is not returning accurate results in Apple Numbers, which is a huge shame for Apple as a company. Apple Numbers developers should be carefully screened and those who are incompetent should be terminated employment immediately, without further notice.

Reply

Answer 4

DanielDay Author

Level 1

33 points

Oct 22, 2017 5:49 PM in response to Barry

Hi Barry,

Thank you for your workaround. Appreciate it.

Reply

Answer 5

Barry

Level 9

60,023 points

Oct 22, 2017 4:03 PM in response to DanielDay

Hi Daniel,

Copy and paste into cell A1 won't reproduce the table in either Excel or Numbers if what's copied is an image of the original data in its cells, which is what you have posted above.

Please resubmit a copy of the data:

Click once on cell A1

Shift-click on cell C10

Copy.

Click once in your reply.

Paste.

Your message will show a table, from which the data may be copied.

The copied data may then be pasted into a Numbers or Excel table by clicking once on cell A1, then Paste.

Row 10 will likely require editing to place the formula in C10.

Regards,

Barry

Reply