Thread: [Solved] Range Formula
View Single Post
 
Old 05-15-2009, 02:18 PM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

I see an error on zyzzyva57's sheet in Cell B12 - the value in this formula can be greater than 30000*0.075 - the argument that you have stated is

If B10 is greater than 20,000, then take the figure in B10 and subtract 20,000 and multiply the remainder by 0.075

There is no upper limit though!, so if B10=60,000 then the sum becomes

=(60,000-20,000)*0.075

ie:

40,000 x 7.5%

Whereas, the maximum MUST be 30,000 x 7.5%

You could always embed IF Functions to make it one formula, rather than notations on the sheet!

=IF(B10>20000,(IF(B10>50000,((B10-50000)*0.05+B10+(20000*0.1)+(30000*0.075)),(B10-20000)*0.075+B10+(20000*0.1))),B10=(B10+(B10*0.1)) )

All you have to do is change the Reference Cell [B10] and, if needed, the Percentages [0.05, 0.075, 0.1] in the formula!
Reply With Quote