Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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%.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.