IF function Between
 

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(G121<50000,100,IF(G1250000<500000,(G12*0.23)/100,IF(G12500000<25000000,(1150+(G12-500000)*(0.18/100)),IF(G122500000<5000000,(4750+(G12-2500000)*(0.15/100)),IF(G125000000<21500000,(8500+(G12-5000000)*(0.1/100)),IF(G1225000000,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


--
carlito_1985
------------------------------------------------------------------------
carlito_1985's Profile: http://www.excelforum.com/member.php...o&userid=21390
View this thread: http://www.excelforum.com/showthread...hreadid=374116

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374116