Hello!
I would like to know if Microsoft excel has some kind of formula to be used for this application:

I would like to split total cost 54,000 and this number will not stay the same:

the first 0-20,000 of 54,000 i want it to be multiply by 10%



the 20,001-50,000 or the 54,000 to be multiply by 7.5%

the 50,001-and higher of 54,000 will be multiply by 5%

I want excel to multiply automatically when I plug any number for total cost and be split without changing formula all the time.

See below for more information.

But the total cost will not always be above $50,000. It can be below or over $50,000.

Total Cost $55,000 $35,000 $15,000

10% overhead the first $2,000 $2,000 $1,500
$20,000.00 ($0 to $20,000)

7.5% overhead next $30,000 $2,250 $1,125 $0.00
($20,001 to $50,000)

5% overhead on Balance over $250 $0.00 $0.00
$50,000

Total with overhead $59,500 $38,125 $16,500

I input these numbers manually.

I want excel to do this automatically, doesn’t matter on the Total Cost without changing formula all the time.

see attached file

Thank you,

Attached Files

Request.xls (19.5 KB, 23 views)

Maybe this will help...See Testing Attachment

Attached Files

testing.xls (33.5 KB, 38 views)

Thank you for your help it worked really good. I asked Microsoft Support for this answer but they couldnt figure it out.

Thanks,

Thanks, glad I could help some

I was putting number in enter cost over $50,000 or higher and I did manualy calculations the line where 7.5% is getting bigger when I put the bigger number in enter cost. It should stay the same when the cost is over $50,000.
I am trying to figure it out how to change it.

Thank you

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!

Can it be broken up into different cells like it shown on zyzzyva57's attachement?

Yup - all you need to do is alter zyzzyva57's formula in B12 to:

If you want the cells to show nothing in them if there is no value, then change the 0 at the end of the formula in B13 to "" as in the code above.
If you want the cells to show $0.00 in them if there is no value, then change the "" at the end of the formula above to 0.

Thank you for your help. My problem got solved.
You are smart you know hecka a lot about excel.

I attend Hard Knock U

Plus, Office Products are my "crossword puzzles"

I started to use excel when I started to work for contractor as estimator.
Its interesting I still did not get the answer from microsoft support on this question, but here i got the fastest answer. You guys should work for microsoft.
This site is awosome.
Anyways thanks for the help.