Google-sheets – Array Formula Issue

google sheetsgoogle-sheets-arrayformula

So I have ArrayFormula checking Column C Price to see what range it falls in. Then it assigns the number based on that range it puts it in Column M. It works perfectly as a formula but when I make it an array it just post all Zeros. So not seeing any in the range even though they all are in one range or another. Any help is a appreciated.

=ARRAYFORMULA(IF(ROW(C1:C)=1,"Price Range ID", IF(ISBLANK(C1:C),"",IF(AND(C2:C>=1, C2:C<=150000), 1,IF(AND(C2:C>=150001, C2:C<=250000), 2, IF(AND(C2:C>=250001, C2:C<=350000),3, IF(AND(C2:C>=350001, C2:C<=450000),4, IF(AND(C2:C>=450001, C2:C<=550000),5, IF(AND(C2:C>=550001, C2:C<=650000),6, IF(AND(C2:C>=650001, C2:C<=750000),7, IF(AND(C2:C>=750001, C2:C<=900000),8, IF(AND(C2:C>=900001, C2:C<=1200000),9,0))))))))))))

Best Answer

and() is an aggregating function and will not work the way you seem to expect in an arrayformula() wrapper. You can use Boolean arithmetic and replace and() with the * operator, but I do not think it would actually be necessary here. Try this:

=arrayformula( 
  ifs( 
    row(C1:C) = 1, "Price Range ID", 
    isblank(C1:C), iferror(1/0), 
    C1:C < 1, 0, 
    C1:C <= 150000, 1, 
    C1:C <= 250000, 2, 
    C1:C <= 350000, 3, 
    C1:C <= 450000, 4, 
    C1:C <= 550000, 5, 
    C1:C <= 650000, 6, 
    C1:C <= 750000, 7, 
    C1:C <= 900000, 8, 
    C1:C <= 1200000, 9, 
    true, "that's one big number" 
  ) 
)
Related Topic