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

1 Answer 1

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, 2021 at 16:55
  • It is my first question. I will definitely follow the protocol.
    – TimTech
    May 4, 2021 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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