MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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<=500000,A1>100000),(A1-100000)*0.015+2500,IF(A1<=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<=0, IF(B2<=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<=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<0, IF(B2>=100000, (B2-100000)*0.015 + 2500, B2*0.025),0))