# Trying to fix a hurdle formula that's not working past a certain hurdle

Here are the two formulas I've tried:

``````=IF(B13<600000,B13*3%,IF(B13>600000,600000*3%+(B13-600000)*4%,IF(B13<1000000,B13*4%,IF(B13>1000000,1000000*4%+(B13-1000000)*5%,IF(B13<1500000,B13*5%,IF(B13>1500000,1500000*5%+(B13-1500000)*6%))))))

=IF(B13<600000,B13*3%,IF(B13>600000,600000*3%+(B13-600000)*4%,IF(B13>1000000,600000*3%+(400000*4%)+(B13-1000000)*5%,IF(B13>1500000,600000*3%+(400000*4%)+(500000*5%)+(B13-1500000)*6%))))
``````

This is the information I'm using to get the numbers that aren't equalling the correct number.

``````Jan 117,223
Feb 115,340
Mar 126,307
Apr
May
Jun
Jul
Aug
Sep 641500
Oct
Nov
Dec
TOTAL   1000370

600,000   3%
1,000,000   4%
1,500,000   5%
``````

34014.8 <- should be \$34,019

Everything is good until 1 million but after 1 million it's not calculating at 5%.

-

I think this should do the trick:

``````=IF(B13<=600000,B13*3%,IF(B13<=1000000,600000*3%+((1000000-600000)-(1000000-B13))*4%,IF(B13<=1500000,600000*3%+(1000000-600000)*4%+((1500000-1000000)-(1500000-B13))*5%,600000*3%+(1000000-600000)*4%+(1500000-1000000)*5%+(B13-1500000)*6%)))
``````

The result I get is \$34018.50.

Breaking down the formula:

• If the total is less than or equal to \$600,000, the figure is 3% of the total.

• Otherwise if the total is less than or equal to \$1,000,000, the figure is 3% of \$600,000 + 4% of however many dollars above \$600,000 up to \$1,000,000.

• Otherwise if the total is less than or equal to \$1,500,000, the figure is 3% of \$600,000 + 4% of however many dollars above \$600,000 up to \$1,000,000 + 5% of however many dollars above \$1,000,000 up to \$1,500,000.

• Otherwise if the total is greater than \$1,500,000, the figure is 3% of \$600,000 + 4% of however many dollars above \$600,000 up to \$1,000,000 + 5% of however many dollars above \$1,000,000 up to \$1,500,000 + 6% of however many dollars above \$1,500,000.

-
Oh my goodness!!!!!! You ROCK!!! This worked and you have save my poor computer from being thrown out the office door!!!! THANK YOU SOOOOOOOOOOOOO much!!! You ARE brilliant and I truly appreciate you taking the time to answer my question!!!!!!!!!! – Stephanie Fish Apr 28 '11 at 19:12