Skip to main content

I have a formula which checks the numbers form Column C PRICE to see what range the number is in and puts the corresponding number Column M PRICE RANGE ID which works great as long as I want to copy to each row. I wanted to create and array so it automatically uses the formula when a row is added however my array never works correctly it only uses the number based on the first row Column C2. My array is in the Column Header.  I did try populating the row in my Zap with just the normal formula and it didnt work. Any help is appreciated. 

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

 

RESULTS BELOW *** These should be different based on the corresponding C Column Price 

 

Price Range ID
5
5
5
5
5
5
5
5
5
5

Hi @GetUWired I really like what you have done there and might have a use for it as well. Below is how I actually got it to work yesterday. A very knowledgeable individual helped me out. Thank you as well @Troy Tessalone.

=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, "out of range" ) )


Hi @Timtech 

Would you mind providing some more context via screenshots of your zap and sheet? 

Dynamic formulas can be inserted into Zapier via my method here: 

 


@Troy Tessalone 

 

No luck. I really thank you for you help. When I figure it out which I will i will post it up. 

 


@Timtech 

Try changing all of the C2:C to C1:C


Hi @Troy Tessalone TroyTessaloone

I actually had tried that already and just get all Zeros. Which to me means its not seeing the numbers in Column C in a range at all. However they are all formatted as numbers. Below is that formula and my Column C data as well.

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

 

 

Price
500000
625000
155000
379000
325000
360000
210000
469000
350000
350000

Hi @Timtech 

Check the GSheets ARRAYFORMULA help article: https://support.google.com/docs/answer/3093275?hl=en

ARRAYFORMULAs need dynamic range references.

So change all instances of C2 to C2:C