0

I am using Google sheets withe the following formula in one of the columns

=ARRAYFORMULA(IF(ISBLANK(M2), "",  ROUND(M2/G2, 2)))

Whenever I add a new row anywhere in the sheet, the formula does not get copied and I have to use other means to copy the formula in each cell of the inserted row.

How should I be writing this formula so that it automatically works for newly inserted row?

Improve this question

1 Answer 1

Reset to default
1

When using array formulas, they only know to which range they should apply calculations if you include that information. It can be confusing, since conditional formatting formulas will apply an array given just the top cell in the column range.

Try this in place of your current formula:

=ARRAYFORMULA(IF(ISBLANK(M2:M), "", ROUND(M2:M/G2:G, 2)))

Improve this answer
3
  • it didn't work.. more over, i am getting an error that "Array result was not expanded because it would overwrite data in F4"
    – Varun Bhatia
    Commented Oct 22, 2018 at 5:55
  • Varun, an array formula doesn't just take up one cell; it takes up however many rows you tell it to. If you placed this formula into cell F2 but you have data in cell F4, it won't work because the array formula as written applies to "every row in column M." And if you have data in cell F4, then the most an array could expand if placed in F2 would be two cells: F2 and F3. Then I would ask, why do you need an array formula for two cells? Please post a version of your sheet along with a description of what you want the formula to do; then people here can see what you see and better direct you.
    – Erik Tyler
    Commented Oct 22, 2018 at 22:16
  • Additional thought until I can see your sheet: if your data rows are being filled automatically, say with data from Google Forms, then you can't use an array on that sheet. Again, an array needs an empty range below it to fill. There are standard practices for manipulating data that comes into Sheets from an outside source, the first of which is to set up a second sheet to process the raw data the way you want to use it. That is where the array formula would be used.
    – Erik Tyler
    Commented Oct 22, 2018 at 22:19

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.