Array Formulas enable the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
The Problem
I have a sheet I am tracking egg production from our flock of chickens. It is tedious to type the date every day, but using a formula or dragging the date to iterate a couple hundred cells is ugly, and will eventually need to be drug further.
Having dates with blank entries also causes some problems when trying to build graphs and visualizations using the dataset, and complicates simple operations like getting our most recent datapoint.
The Solution | Array Formulas
To fix this issue, I wrote an Array Formula to calculate the date for each entry if a daily egg value has been entered. Now, the dates auto-fill as I input data day-by-day as you can see in action here on the right. This creates a much cleaner spreadsheet, and makes for a satisfying user experience .
=ARRAYFORMULA(
IF(
ROW(A:A)=1,
"Date",
IF(
ISBLANK(C:C),
"",
IF(
ROW(A:A)=2,
DATEVALUE("08/16/2021"),
DATEVALUE("08/16/2021")+ROW(A:A)-2
)
)
)
)How the Code Works
In the example above, this formula is pasted in cell B1 only. The array formula then calculates down that column automatically.
NOTE: You will get an error if any content exists below your formula in that column. It is trying to warn you that this operation would overwrite data in any cells beneath it, and therefore, won’t execute until you remedy the issue.
=ARRAYFORMULA(
IF(ROW(A:A)=1, "Date",
If the row number of this row in column A is 1, value is Date. This is our header row.
IF(ISBLANK(C:C), "",
If the value of the cell in column C for this row is blank, value is empty. This is how we are keeping our dates blank until we input data in column C.
IF(ROW(A:A)=2, DATEVALUE("08/16/2021"), DATEVALUE("08/16/2021")+ROW(A:A)-2)
Now we need to build our date. Remember, this is the ‘else’ portion of IF(ISBLANK(C:C), "", so we know there is a datapoint in column C and we therefore want a date. My first datapoint is for August 16, 2021, so if the row number of this row in column A is 2 (our first row of data), value is DATEVALUE(“08/16/2021”).
Otherwise, we will build our date by adding the row number less 2 (to account for our header and first row of data) to our first date.
) ) )
Still have Questions?
Leave a question in the comments section below, or check out the Array Formula entry on the Google Sheets documentation.
