Mismatch Range error on using IFs in sheets-CodePudding

I am using the formula below and getting an error "IFS has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2, column count: 2." The code is to ignore Sat/Sun and pick High values of Thurs/Fri in case of Sat/Sun.

$c$1 is the present date.

Can someone help me debug this

=ifs(weekday($C$1-1,2)=6,GOOGLEFINANCE($S$1,"high",today()-(today()-$C$1 4)),weekday($C$1-1,2)=7,GOOGLEFINANCE($S$1,"high",today()-(today()-$C$1 4)))

CodePudding user response：

Issue:

IFS syntax is:

IFS(condition1,value1,condition2,value2,...)

It's important to realize all arguments should be of the same size. Same rows and columns. If there's a mismatch, this error is thrown

Expected row count: 1. column count: 1. Actual row count: 2, column count: 2.

What this says is, 1x1 array is expected, but instead the function got 2x2 array and it doesn't know what to do with that.

In this specific case, GOOGLEFINANCE returns a 2x2 array with it's headers, while WEEKDAY returns a 1x1 array.

Solution:

Either increase the 1x1 array to 2x2 array or reduce the 2x2 array to 1x1 array. Increasing can be done with array operations like ARRAYFORMULA(IF()) or IF(SEQUENCE()). Decreasing can be done with INDEX/QUERY or FILTER

Snippet:

=ifs(weekday($C$1-1,2)=6,INDEX(GOOGLEFINANCE($S$1,"high",today()-(today()-$C$1 4)),2,2),weekday($C$1-1,2)=7,INDEX(GOOGLEFINANCE($S$1,"high",today()-(today()-$C$1 4)),2,2))

CodePudding user response：

try:

=INDEX(IF(WEEKDAY($C$1-1,2)=6, 
 GOOGLEFINANCE($S$1,"high",TODAY()-(TODAY()-$C$1 4)),
       IF(WEEKDAY($C$1-1,2)=7, 
 GOOGLEFINANCE($S$1,"high",TODAY()-(TODAY()-$C$1 4)), )), 2, 2)

reason why your IFS wont work: https://webapps.stackexchange.com/a/124685/186471