iworknumbers
I'm creating a template on Numbers and I would like to know if there is a way for the formula to ignore, or skip over some cells or even entire columns. I need these columns for some calculations, but need them to be overlooked for others. The catch being, which column will have to be skipped over will be different every time.
Maybe there is a way to "mark" a column to be ignored, with the help of a checkbox, perhaps?
Or is there an even easier, more obvious way that has eluded me?
Simply create an Index column. 1, 2, 3 etc. (As pjctec mentions in comments.). Without some ordered list or index, there is no way for Numbers to know how to use a custom sort.
There is no need to use a formula to create the list of numbers.
A1 enter 1A2 enter 2A3 enter 3A1 to A3Sorting will not change the numbers as they are static. No need to use a formula.
Then you can always sort the index column later. If needed, the column can be hidden or shown when needed.
Apple supplies a good resource for Numbers basics on their Website. Numbers Help. Recommended for new users and anyone who wants to get the most out of using Numbers.
If you don't want to use other cells temporarily, then do the following, which assumes equal size ranges:
Now the two ranges of cells have been swapped.
Note: Both ranges do not necessarily need to be the exact same size, however there has to be an appropriate amount of cells in the area of the ranges being swapped. Use your best judgement.
By the way, this works because the drag and drop clipboard is different then the general clipboard. So the first range of cells just sits there waiting to be pasted as the drag and drop operation didn't disturb it.
Best Answer
In practice, you could sum each column and then sum just the the sum row.
That way, you can delete the sum of the column to be skipped when needed and it should be clear about which of the columns is ignored.
A more general solution will be tricky since it's programming in conditional values or conditional functions that are much harder to maintain, debug and be confident your math is correct as you use the tool over time.