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

Question

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.

score 3 · Accepted Answer · answered Nov 19 '21 at 14:25

3

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

answered Nov 19 '21 at 14:25

player0

1

Thank you! Now I'm just wondering how was I supposed to know this. Is it documented somewhere? The error for sure is miss leading... – Waltari Nov 22 '21 at 11:00
1

@Waltari of course it not documented, like a lots of other stuff – player0 Nov 22 '21 at 11:02
1

Of course :D ... – Waltari Nov 22 '21 at 11:05

1 Answers1