Thread: Calculating with some rules

Hi All. Perhaps someone might be able to assist.

I need to a formula which can calculate the cost of various types of glass subject to the following rules.
1. if area is below 0.4 m2 then the cost should not be less than 0.4m2 cost.
2. if area is above 3.2m2 cost should be plus 35%.
3. if area is above 4.2m2 cost should be plus 45%.


Best regards

size in a1 cost per m2 in b1

Code:

=if(and(a1>0,a1<=0.42),0.42*b1,if(and(a1>0.42,a1<=3.2),a1*b1,if(and(a1<=4.2,a1>3.2),(a1*b1)+(a1*b1)*35%,if(a1>4.2,(a1*b1)+(a1*b1)*45%,""))))