0

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

0

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" 
  ) 
)
2
  • Wow that worked great! Thank you so so much! I had played with the * operator but couldnt get it. This is much cleaner. I cant thank you enough!!!! – TimTech May 4 at 16:55
  • It is my first question. I will definitely follow the protocol. – TimTech May 4 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.