Search code examples
if-statementgoogle-sheetsarray-formulas

Array formula with IF not populating column in Google sheets

I cannot seem to make my array formula populate downwards from the first referenced row for some reason.

Background: I have 15 formulas in an active document that I would like the to autopopulate with the formulas when I create new rows.

The way to do this seems to be ArrayFormula and changing the references from say O2 to O2:O to make it populate the sheet.

I am making some basic error here and I need some assistance :)

Link to test sheet *some headers are in Swedish (sorry) :)

https://docs.google.com/spreadsheets/d/1P7M73-ITRG3LNA5O7yR0grj40d9tFF8Ve6ed6hQkiyc/edit?usp=sharing

My testing; this is in the example sheet in G1

Orig formula which works fine dragging into new rows

=IF(AND(O2=""),"",IFERROR(MAX(($V2)/$X2,0)))

The formula checks for empty cells and if not it will calculate a percentage based on other formulas.

Array with original formula + header row

={"Klar"; ArrayFormula(IF(AND(O2=""),"",IFERROR(MAX((V2)/X2,0))))}

This results in O2 populating as expected

With somehow incorrect references which I hoped would populate the whole column

={"Klar"; ArrayFormula(IF(AND(O2:O=""),"",IFERROR(MAX((V2:V)/X2:X,0))))}

I expect the formula to currently populate O2-> O6 and to go into new rows with data when they appear.

I also expect to be able to do arrays for all 15 columns.

Solution

  • Use MAP() function. I have made this formula based on your provided formula =IF(AND(O2=""),"",IFERROR(MAX(($V2)/$X2,0))).

    =MAP(O2:O,V2:V,X2:X,LAMBDA(x,y,z,IF(x="","",MAX(y/z,0))))

    enter image description here