1

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?

  • Could you please add an example? Right now I don't really understand why you can't simply point the formule to the cells you do want included in a calculation. – Saaru Lindestøkke Feb 11 '15 at 13:09
  • Thanks for your reply. As I said, the columns to skip won't be the same from one user to the next, or from one month to the next... It may be cells in columns B, D and G this time or cells in column A next time. – Moe Ron Feb 11 '15 at 13:14
  • Ah ok. So a cell should calculate A1+B1+C1+D1 for one month, and when in another month the same cell should calculate A1+D1, right? If it's actually based on months you could try writing a (nested) if-then-else condition. – Saaru Lindestøkke Feb 11 '15 at 13:23
  • Thanks, that does seem to be the general direction I want to go. However the difficulty is that the users will be the ones picking the column in which the cells to be ignored live... – Moe Ron Feb 11 '15 at 13:36
  • Hmm... There are checkmarks which translate to a 1 or 0 value. You could incorporate that value in your formule. Using my previous example, assuming checkmarks live in the first row and data lives in the second row onwards you could try something like A$1*A2+B$1*B2+C$1*C2+D$1*D2. – Saaru Lindestøkke Feb 11 '15 at 13:42
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .