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))
