Forum Discussion

GreggK240's avatar
GreggK240
Copper Contributor
Jan 19, 2022

Lengthy If And Formula

Hi All,

I'm looking for a way to simplify the IF And formula below. I believe there should be an easier way to do this without writing numerous IF And statements in one line. You will note the formula is in increments of 500, however, we would like to go more granular at increments of 100.

This is a price calculator based on the size of a home. So for up to 2,000 sq ft we add $35, up to 2,999 sq ft we add $60. We would like to create a simplified formula if possible which does something like adding $1 for each 100 sq ft up to 1,999 sq ft, adding $2 for each 100 sq ft up to 2,499 sq ft, etc. Any help would be greatly appreciated.

 

=IF(AND(B3<2000),0,IF(AND(B3<=2499),35,IF(AND(B3<=2999),60,IF(AND(B3<=3499),85,IF(AND(B3<=3999),110,IF(AND(B3<=4499),135,IF(AND(B3<=4999),160,IF(AND(B3<=5499),185,IF(AND(B3<=5999),210,IF(AND(B3<=6499),235,IF(AND(B3<=6999),260,IF(AND(B3<=7499),285,IF(AND(B3<=7999),310,IF(AND(B3<=8499),335,IF(AND(B3<=8999),360,IF(AND(B3<=9499),385,IF(AND(B3<=9999),410,IF(AND(B3<=10499),435,IF(AND(B3<=10999),460,IF(AND(B3<=11499),485,IF(AND(B3<=11999),510,IF(AND(B3<=12499),535,))))))))))))))))))))))

excel

4 Replies

  • Benny_1857's avatar
    Benny_1857
    Brass Contributor
    Jan 19, 2022

    GreggK240 

    I will try switch first.
    =Switch(True(),B3<2000,0,B3<2499,35,B3<2999,60.....B3<11999,510,535)

    Also you could list a support table.

    Input the size in column C

    and maybe in column D use fomular as below

    =LOOKUP(C2,$A$2:$A$23,$B$2:$B$23)

    Next time you just need to check the value in A or B

     

     

    • GreggK240's avatar
      GreggK240
      Copper Contributor
      Jan 21, 2022

      HansVogelaar 

       

      Hi Hans, thank you so much for your reply. I took the formula you provided and modified it as follows:

      =MAX(5*QUOTIENT(B3-2699,100)+35,0)

      In the above, anything up to 1999 does not add anything. 2000 adds 5, 2100 adds 10 etc. which is exactly what we need.

       

      The only thing I have not figured out is what the 35 represents. Would you mind explaining?

       

      Thanks again for all your help.

Resources