1

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:

enter image description here

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.

2
  • Use the BYROW formula.
    – Blindspots
    Nov 30, 2022 at 5:59
  • 1
    array formula: ctrl+shift+enter
    – Lee
    Nov 30, 2022 at 7:45

2 Answers 2

2

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))
1

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.

Enter ARRAYFORMULA

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

Suppress zeros

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.