How to use a specific formula in every row in a specific column now and in the future in google sheets?

Question

In Google Sheets I have a very simple formula to subtract the value in column A from the value in column B and show the total in column C.

Column C formula:

=(B:B-A:A)*24

I've selected column C by clicking on the header and I'd like to it to apply to every cell in column C (maybe except the empty cells or header cells).

I think I have it working but it only appears to work on the first row:

I don't want to copy the formula to a range of selected cells (filling). I want all cells in that column to use the specific formula and any new cells that are created.

I hope this makes sense. I've seen similar questions that copy the formula this is slightly different so please add comments if you believe there is a similar question.

Answer 1

The easy answer is: put your formula inside the arrayformula() function.

In that way the calculation will be performed taking into consideration all the cells of columns B and C.

However there will be a problem: there will be a whole series of zeros, which we could cancel for example with:

=arrayformula(if(C:C*B:B=0,,(C:C-B:B)*24))

With this modification, the calculation will be performed only if none of the 2 values ​​of B and C will be empty or null.

To avoid the calculation also in the header cells, start the calculation from the next cell, i.e. if you have the headers in row 1, the formula must be:

=arrayformula(if(C2:C*B2:B=0,,(C2:C-B2:B)*24))

Answer 2

In order for the formula to calculate a value for each element of the specified arrays, you must specify that you mean exactly arrays. The special function ARRAYFORMULA() is used for this.

To suppress unwanted zeros, you can set the entire C column to a custom format. For example, like this: