Lengthy If And Formula

[GreggK240]

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

[#2]

@GreggK240 

The formula that you posted can be simplified to

=MAX(25*QUOTIENT(B3-2000,500)+35,0)

[#3]

@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

[#4]

@Hans Vogelaar 

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.

[#5]

@GreggK240 

35 is the amount you start with when the amount is 2000.