I have a spreadsheet that I regularly update, which populates blank cells in columns. I want to be able to retrieve to the contents of the last cell in a row for a formula (which updates as cells become populated). I have found ways to do that in both Excel and GoogleDocs but the formulae don’t work in Numbers. The Excel formula includes a FILTER function that doesn’t exist in Numbers. The Google one makes use of the INDEX and COUNTA formulae but I just get an exclamation mark in a red triangle. Is there a way in Numbers?
I want to create a line in a chart that represents the average achievement at any given moment and extrapolates it over the remainder of the period. For example, if the figures suggest that I am achieving sales of $100/day today, a cumulative total would show a straight rising line on a graph. But if my sales start running at $150/day (the last figure in the row), I now want to show a cumulative total that will give a more sharply inclined line on the graph. I already have a straight line showing the target achievement and I want to be able to project how close to target achievement is looking as time passes.
Device: Mac Running Big Sur
App: Numbers
—–
Simon Evans
You can use the LOOKUP function to find the last cell with a number.
LOOKUP(REGEX(".*"),B,B)
This looks in column B for the closet match to "anything" and returns the value in that cell. So you get the value of the last cell in the column that is not blank.
Just tried that and it works like a dream. So neat and concise too. Thanks for taking the time to respond. I would never have worked that out for myself.