Commission formula-help please :)

[Lighthouseskrapbooker]

I need help with a formula for commissions:
In cell #C35, if the combined loan amounts (total found in cell D32) are
between 0 - 10,000 the commission is 30%, which will show in cell# D35.
In cell #c36, if the combined loan amounts (found in cell# D32) are between
1000.01 - 15,000 then the first 10000 stays at the 30% but the next 5000
commission is at 35%. (Cell #D35 still needs to indicate the commission for
the first 10,000) This commissioned amount needs to be in Cell# D36.
In cell #C37, if the combined loan amounts are over 15,000.01, then anything
over 15,000.01 needs to indicate the commission at 40% and show in cell
#d37(Cell #D35 and Cell#D36 still need to indicate their respective
commissions at their respective %) I hope this makes sense. After reading
some of the entries on this forum I feel it is far away from rocket science
and that it should be an easy formula for everyone but me! Thanks for your
help!

 

[Niek Otten]

Look here:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Lighthouseskrapbooker]

Thank you for your time Niek, I really appreciate it,but this really didn't
solve my dilemna. The financial specialist want it spelled out clearer by
showing exactly how much commission is being paid at what level. I need the
commission amounts to show in three separate cells. The current formula is
like this: Cell #35 =IF(D32<=5000,D32,IF(D32=0,0,5000)) with the commission
amount showing in D#35. Then the next cell # C36 has the formula
=IF(D32<=5000,0,IF(D32<=10000,D32-C35,5000)) with the commission amount
showing in cell #D36 and so on. These current cells need to be refigured
using the new rates. Also, we currently have 5 tiered levels but we are
cutting back to three. So the final cell would need to be for anything over
$15,000. I've tried every way I can think of to use the existing cells but I
can't get them to work. Anyways, thanks again.

 

[Bob Greenblatt]

Thank you for your time Niek, I really appreciate it,but this really didn't
solve my dilemna. The financial specialist want it spelled out clearer by
showing exactly how much commission is being paid at what level. I need the
commission amounts to show in three separate cells. The current formula is
like this: Cell #35 =IF(D32<=5000,D32,IF(D32=0,0,5000)) with the commission
amount showing in D#35. Then the next cell # C36 has the formula
=IF(D32<=5000,0,IF(D32<=10000,D32-C35,5000)) with the commission amount
showing in cell #D36 and so on. These current cells need to be refigured
using the new rates. Also, we currently have 5 tiered levels but we are
cutting back to three. So the final cell would need to be for anything over
$15,000. I've tried every way I can think of to use the existing cells but I
can't get them to work. Anyways, thanks again.

I think part of your confusion is that a formula can only return a value to
the cell which contains it. It can not set the value of another cell. So
just set up the intermediate cells with their portion of the formula and
then sum these cells for the final commission.