My formula is as follows:

=ARRAYFORMULA(IFS(AND(H2:H >= -B39, H2:H <= B40), 100, H2:H > B37, 0))

However this line keeps throwing the error:

IFS has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 1000, column count: 1.

Any idea how to fix this?

I have similar formulaes like this one that works:

=ARRAYFORMULA(IFS(F2:F <= 0, 100, F2:F >= B19, 0, true, (B19 - F2:F) / B19 * 100))

Not sure what I'm doing so differently with the first formula that would cause the issue.

CodePudding user response：

AND is not supported under ARRAYFORMULA

=ARRAYFORMULA(IFS((H2:H >= -B39)*(H2:H <= B40), 100, H2:H > B37, 0))

and IFS is in some cases not suited for ARRAYFORMULA as well

try:

=ARRAYFORMULA(IF((H2:H >= -B39)*(H2:H <= B40), 100, IF( H2:H > B37, 0, )))