IF function Between

C

carlito_1985

Hey everyone, I'm trying to write a formula to calculate what the cost
would be with different scales of percentage.

What im working with is, in cell g12 I input cost of construction eg
1,550,000.

Now the fees are as follows.

Upto 50,000 $100
50,000 - 500,000 .23% of cost (550,000*.23%)
500,000 - 2,500,000 $1,150 + .18% of cost after $500,000

(1,150+((1,550,000-500,000)*.18%))
2,500,000 - 5,000,000 $4750 + .15% of cost after 2,500,000
5,000,000 - 21,500,000 $8,500 + .1% of cost after 5,000,000

21,500,000+ $25,000

this is the formula I used, but it didn't seem to work properly.


=IF(G12>1<50000,100,IF(G12>50000<500000,(G12*0.23)/100,IF(G12>500000<25000000,(1150+(G12-500000)*(0.18/100)),IF(G12>2500000<5000000,(4750+(G12-2500000)*(0.15/100)),IF(G12>5000000<21500000,(8500+(G12-5000000)*(0.1/100)),IF(G12>25000000,25000))))))

I'd also like it to round to the closest .05 (round 3040.14 to 3040.15)
etc

and the cell comes up as false, when it should technically come up as
3040

any idea what Im doing wrong??

Thanksyou all
 
M

mangesh_yadav

Use:

=IF(G12<=50000,100,IF(G12<=500000,0.23%*G12,IF(G12<=2500000,1150+0.18%*(G12-500000),IF(G12<=5000000,4750+0.15%*(G12-2500000),IF(G12<=21500000,8500+0.1%*(G12-5000000),25000)))))


for the rounding, you can use:
=MROUND(3040.14,0.05)
for this function, you require the Analysis ToolPak addin. This can be
done by going to Tools > Addins. Check the Analysis ToolPak checxkbox.


Mangesh
 
Top