numbers
I am working to create a function that keeps the data in a cell dynamic until the date is beyond that of the date column. In other words, there is a column of dates, i.e. 9/5 9/6 9/7… and the other columns are tracking data from other fields. If the row date is older than the current date, then the data in the field stops updating and remains at the last known value. Is this possible in Numbers or should I look for another solution?
Thank you!
Mike
Using the SUM(IF) function:
Because Numbers does not yet support certain Array functions, for example extracting MONTH(A1:A23), by parsing the date into a third column (which can be hidden) the function will work. This column can be in any table, not just the raw data table as long as the references are valid.
Hidden column formula:
=MONTH(A1)&YEAR(A1)
Where A1 is the cell with date.
Copy down then hide column if desired. (Importing more data by adding rows should automatically bring the formula down but unhide the column to be sure.)
=SUMIF($'Month & Year',"="&MONTH($A1)&YEAR($A1),$Value)
From the documentation regarding SUMIF():
The SUMIF function returns the sum of a collection of numbers, including only numbers that satisfy a specified condition.
SUMIF(test-values, condition, sum-values)
- test-values: The collection containing the values to be tested. test-values can contain any value.
- condition: An expression that can include comparison operators, constants, the ampersand concatenation operator, and references. The contents of the condition must be such that the result of comparing the condition to another value results in the boolean value of TRUE or FALSE.
- sum-values: An optional collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values. It should have the same dimensions as test-values.
As it turns out, I've figured it out. It did require Applescript. What I did was loop through the rows and selected the values in the row where "not" was not present in column B. Here's the Applescript I used via Automator:
on run {input, parameters}
tell application "Numbers"
tell table "Calculator 1" of sheet 2 of document of front window
set aRange to "A2:A22"
set bRange to "B2:B22"
set aRows to the rows of range aRange
set bRows to the rows of range bRange
set correctRow to -1
set correctCol to -1
repeat with i from 1 to count of bRows
set aCell to "A" & (i + 1)
set bCell to "B" & (i + 1)
if value of (cell bCell) is not "not" then
set correctRow to value of (cell aCell)
set correctCol to value of (cell bCell)
end if
end repeat
set the value of (cell "C2") to correctRow
set the value of (cell "C4") to correctCol
end tell
display dialog "Updated!"
end tell
return input
end run
Keep in mind that the cells I used are specific to my spreadsheet, but generally this method could be applied for any 2D MATCH() function.
MATCH() function per row in this way (see the picture of the table in my question).MATCH() functions and find the correct row and column. Change "C2" to whichever cell will be your Correct Row and "C4" to whichever cell will be your Correct Column.INDEX() function to get the value of the cell and put it into another cell, using the coordinates the Applescript calculated for you.
Best Answer
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..
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
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
.
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: