0

I would like to subtract the last column of a specific row. Since the number of columns is always changing, I need to get it dynamically. I figured there is a function that gives you the number of columns of a row. But I am not able to tell Numbers to use it (because I don't know the syntax).

  • The number of columns is always changing, or the number of rows? I am only asking because I see a lot more of the latter case than the former. – samh Aug 31 '14 at 12:40
  • Not in my case, I really change the number of columns. If a column is added, I want to use this new column to calculate something. – DaPhil Aug 31 '14 at 12:45
  • What version of Numbers, please? – samh Aug 31 '14 at 12:48
  • Numbers version 3.2.2. – DaPhil Aug 31 '14 at 13:32
1

I'm not 100% clear what you're asking to do since you haven't posted any of the contents of the sheet, but you can use the INDEX, ROWS and COLUMNS formulas to get what you want. INDEX() will return the contents of a cell within a certain range of cells, and you can use rows() to count the number of rows and columns() to count the number of columns, depending on exactly what you need. Without knowing how ranges are defined and things are labelled it's hard to be more specific, but I think that will get you started in the right direction.

If that doesn't help, maybe you could post what you've tried and give us some example data & formulas.

1

If the value you want to reference is always in the last column of your sheet you can try:

INDEX(2:2,1,COLUMNS(2:2,0),area-index)

If you have no empty cells between the starting and final value (zeros are fine here) and empty cells (no zeros) after the desired reference you can try:

INDEX(2:2,1,COUNTA(2:2)+1,area-index)

If your table is made up of entered values you would need to fill the blank spaces for this to work.

This second one will return the final value of row 2 in a sheet with one header column. Because COUNTA() excludes header columns, replace “+1” with the number of header columns in your sheet. This is a potential bug source if this detail is forgotten and the header setting is changed. The following formula avoids this problem:

INDEX(2:2,1,COLUMNS(2:2,0)−COUNTBLANK(2:2),area-index)

If your table has zero values beyond the desired reference (likely the case for formulas rather than entered values) you can try one that subtracts null values, but this is problematic if you have zeros in your data:

INDEX(2:2,1,COLUMNS(2:2,0)−COUNTIF(2:2,"=0"),area-index)

I hope this helped.

You must log in to answer this question.

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