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).
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.
If the value you want to reference is always in the last column of your sheet you can try:
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:
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:
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:
I hope this helped.