If formula

[PinkCoral]

Hi,
Let me know how to best handle. I need to calculate a commission at one
rate but if the amount exceeds the budget we pay the commission at a hight
rate.
budget 500,000 cell a1
billed 600,000 cell a2
cell a3 10%
cell a4 20%
i need to calculate 500,000 @ 10% and 100,000 at 20%

I need the formula to work even if the billed amount is lower than the budget.
example budget 500,000 billed 400,000, I need a calculation to pay the
400,000 at the 10% rate. please reply with cell info included.
Many thanks


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...5-fdaf64bf3c02&dg=microsoft.public.excel.misc

 

[Titus]

Try this:

=IF(A2>500000,A1*A3+(A2-A1)*A4,A2*A3)

A1=500000
A2=600000
A3=10%
A4=20%
A6=FORMULA

Regards, Titus

 

[Lee]

Hi,
Let me know how to best handle.  I need to calculate a commission at one
rate but if the amount exceeds the budget we pay the commission at a hight
rate.
budget 500,000 cell a1
billed   600,000 cell a2
cell a3 10%
cell a4 20%
i need to calculate 500,000 @ 10% and 100,000 at 20%

I need the formula to work even if the billed amount is lower than the budget.
example budget 500,000 billed 400,000, I need a calculation to pay the
400,000 at the 10% rate.  please reply with cell info included.
Many thanks

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=a4d0...

Please try this:
A1 Budget B1 500,000.00
A2 Billed B2 400,000.00
A3 Commission B3 Formula

"=IF(B2>B1,(B1*0.1)+(B2-B1)*0.2,B2*0.1)

Regards,
Lee

 

[Brad]

Another way to do this without an if statement
=min(a1,a2)*a3+max(0,a2-a1)*a4