I entered the numbers 1, 2, 3, 4 and 6 into the cells A1-A4 and A6. I then click A8 and type =, and drag across A1 to A4, hit 'minus' and click A6. This gives: SUM((A1:A4) - A6), which Numbers calculates as -14 (see screenshot).
Obviously what I mean and need is (SUM(A1:A4) - A6), but really...? What's going on here? Where does it get -14 from?
[Edited by Moderator]
MacBook Pro 14″, macOS 15.6
It's subtracting the 6 from each of those numbers individually instead of the total.
(261128)
It's subtracting the 6 from each of those numbers individually instead of the total.
(261128)
Ah, thank you. But is that what it should be doing? Surely dragging across A1 to A4 and then subtracting A6 should give the sum of A1 to A4, and then A6 subtracted? That seems much more logical than what it's actually doing.
Martin S Taylor wrote:
But is that what it should be doing?
It wouldn't have done that in the old days. But now Numbers has dynamic array formulas.
In general that kind of structure for calculations in a table in Numbers is not practical.
It pays to have a Header Row, and often a Footer Row to hold formulas.
If you haven't done so already, highly recommend having a look at the built-in templates at File > New in the menu.
SG
Thanks!
It wouldn't have done that in the old days. But now Numbers has dynamic array formulas.
Is there anywhere dynamic array formulas are documented? I often wondered what that green dotted line meant.
In general that kind of structure for calculations in a table in Numbers is not practical.
Sorry, what kind of structure? The one I illustrated? Don't you ever make a report like this?
If you haven't done so already, highly recommend having a look at the built-in templates at File > New in the menu.
Which templates in particular?
Thanks in advance.
Martin
[Edited by Moderator]
I don't think Apple has separately documented the dynamic array formulas, except for brief references in certain entries in Help > Formulas and Functions Help.
There have been some posts in this forum, including this one:
Array Functions … no documentation, no se… - Apple Community
This type of formula was first adopted in Excel. So if you do a search for them in Excel, much of what you will find will be applicable to Numbers, though you'll want to adapt them to the Numbers design philosophy.
In general, in Numbers (with its ability to have multiple smaller tables on one sheet) you don't need to refer to a range within a column. Instead of SUM(A1:A4) you'll use SUM(A). And make use of Header Rows and Header Columns.
Have a look at most of the built-in templates to quickly get an idea of how things can be done in Numbers.
SG
Yes, that is what it is supposed to be doing. What you meant to do was =SUM(A1:A4,-A6). Or SUM(A1:A4) - A6
Numbers oddity – what am I doing wrong?
