英文:

Array formula with IF not populating column in Google sheets

问题 {#heading}

我似乎无法让我的数组公式从第一个引用的行向下填充，原因不明。

背景：我在一个活动文档中有15个公式，当我创建新行时，我希望它们能够自动填充公式。

看起来可以使用ArrayFormula，并将引用从O2更改为O2:O，使其填充工作表。

我在这里犯了一些基本错误，需要一些帮助

测试表链接 *一些标题是瑞典语（抱歉）

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

我的测试；这在G1的示例表中

原始公式，将其拖入新行中可正常工作

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

该公式检查空单元格，如果不为空，将基于其他公式计算百分比。

具有原始公式和标题行的数组

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

这会导致O2按预期填充

某种方式有错误引用，我希望它们可以填充整个列

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

我希望该公式当前填充O2->O6，并在出现数据时进入新行。

我还希望能够为所有15列创建数组。 英文:

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.

答案1 {#1}

得分: 0

=使用MAP()函数。我基于您提供的公式制作了这个公式***=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))))

英文:

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