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).
Using last column in Numbers
numbers
Related Solutions
This may or may not be what you are after.
This Assumes you know which cells you will be referencing. And remember this is only an example..
IF(A2<TODAY(),D2,INDEX(D,COUNTA(D) + 1))
Using the IF function.
IF(if-expression,if-true,if-false)
First Compare date value in A2 with Todays date TODAY() as the if-expression
A2<TODAY()
We use the less than symbol < to return a true or false for the expression.
If the expression returns True, then we fill the cell with The cell data in D2
If the expression returns False, then we fill the cell with the last with data in column D
The INDEX function returns the value in the cell located at the intersection of the specified row and column within a collection of cells or from an array returned by an array function.
.
The COUNTA function returns the number of its arguments that are not empty.
It is bets you look up in the Functions panel for a fuller explanation on all the functions.
Once you have set up the first row use the drag handle to drag the formula cell down to the other rows you want it on.
They will auto adjust to the correct cell references.
Notes:
- At time of this posting the date was 6 September 2014
- You will need to find a way of dealing with Blank cells in the data column. If a blank cell is found it will throw the results. You should find a resolve with a google search. It will be the COUNTA function that you need to look at.
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.
Related Question
- How to start rows with 0 (not 1) or not count header rows in Numbers
- How to see page breaks in Numbers version 3.x
- When using Numbers app, is there a setting to create a new column when user presses tab
- Save the order or a column
- Numbers: Find a cell in a table (lookup) using multiple criteria
- (Numbers) COUNTIF values from one column match another column
- Adding column to multiple identically formatted tables in Numbers
Best Answer
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.