# I need a formula!

Posted by Lora on June 08, 2001 8:23 AM

This may not be too hard, but I've been struggling with it for 2 days now, so I need an Excel expert to help me out. I need a formula that will calculate 2.5 percent of the first \$100,000 of a figure, 1.5% of the next \$400,000 and 1% of everything above \$500,000. So if my figure is \$585,000, I want the formula to return \$9350 (\$2500 is 2.5% of \$100,000; \$6000 is 1.5% of \$400,000; and \$850 is 1% of \$85,000) Can this be done? Thanks for any help you can provide.

Lora

Posted by IML on June 08, 2001 8:54 AM

Assuming your figure is in A1, try the following:

=IF(A1>500000,(A1-500000)*0.01+8500,IF(A1>100000,(A1-100000)*0.015+2500,A1*0.025))

good luck

Posted by lora on June 08, 2001 9:06 AM

Thanks for the quick answer. It didn't work-it returned \$2853.20, but at least I have a base to work with now.

Posted by Chuck on June 08, 2001 9:09 AM

Lora,

Assuming your number is in cell(a1) the following formula should work for you:

=IF(A1>500000,(A1-500000)*0.01+2500+6000,IF(AND(A1&LT;=500000,A1>100000),(A1-100000)*0.015+2500,IF(A1&LT;=100000,A1*0.025)))

Posted by IML on June 08, 2001 9:10 AM

Your welcome. When I paste what I posted, I get the \$9350 you mentioned, so you may want to try re-pasting it.

Posted by Katsy on June 08, 2001 9:22 AM

=SUM(IF(B2-500001>=0, 8500 + B2*0.01, 0) + IF(B2-500000&LT;=0, IF(B2&LT;=100000, B2*0.025, 2500+(B2-100000)*0.015),0))

Posted by lora on June 08, 2001 9:23 AM

Sorry IML, I did do something wrong. Your formula worked fine. I GREATLY appreciate the help!

Posted by Katsy on June 08, 2001 9:31 AM

Whoops: try this:

=SUM(IF(B2-500001>=0, (B2-500001)*0.01 + 8500, 0) + IF(B2-500000&LT;=0, IF(B2>=100000, (B2-100000)*0.015 + 2500, B2*0.025),0))

Posted by Katsy on June 08, 2001 9:34 AM

Three Times is a Charm:

=SUM(IF(B2-500000>=0, (B2-500000)*0.01 + 8500, 0) + IF(B2-500000&LT;0, IF(B2>=100000, (B2-100000)*0.015 + 2500, B2*0.025),0))