Solved

I am trying to have a google sheet formula that functions whenever a new row is created. However, when I add the formula it creates “0” values even when there are no rows. Basically adding 1000 rogue rows.

This is the formula I am trying to continue down column J - =ARRAYFORMULA(HOUR(D2+E2))

How can I have my column formula work for each added row?

Check out this post: Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

Basically, you have to create an IF formula to check if there’s a value in a specific column (usually the 1st one) in order to conditionally perform the calculation, or leave the cell blank.

1 Like

You can use this formula.
=IF(ISBLANK(D2),"",ARRAYFORMULA(HOUR(D2+E2)))

This can be modified in many ways and as per requirement.

Thank you @Pratik_Shah. the document is still populating the zeros.

Where do I place the formula… in the header? or in the second row?

Basically I want the formula to work for all new added rows. But when I post it, it creates “0” down the spreadsheet therefore creating thousands of rows before they were actually created.

Can you share your sheet? or a sample sheet with your details?

Look at this.

Arrays with this particular syntax must go on first row as the formula defines the name of the row.

If it’s returning zero all the way down, be sure the ISBLANK part of the formula is truly looking at a column that should be empty or not. Also the IF statement should be within the Arrayformula. Not outside of it. And change ‘D2’ to ‘D2:D’ because you want to look at a range of cells. Not just cell D2.

Arrayformulas should use a range of cells, so also change ‘D2+E2’ to ‘D2:D+E2:E’

If you are putting the formula in the second row, then us this:
=ARRAYFORMULA(IF(ISBLANK(D2:D),"",HOUR(D2:D+E2:E)))

If putting in row 1 along with the heading, then use this:
={"Heading";ARRAYFORMULA(IF(ISBLANK(D2:D),"",HOUR(D2:D+E2:E)))}

If you still have issues, overwrite any double quotes. Sometimes they copy weird from the forum. Also, depending on the regional locale of the sheet, some countries swap commas and semicolons.

1 Like

Thanks for your help everyone. I got it figured out.

2 Likes