calculate the total commissions

[angus]

Need help with setting the correct formula.

Column A (Starting Revenue)
Column B (Increase Revenue)
Column C (Reduce Revenue)
Column D (New Business)

I need a formula in Column F that would calculate the total commissions earned based on the following createria:

6% on Starting Revenue
3% on Reduce Revenue
12% on Increae Revenue
12% on New Business

N.B:
If there is a figure in Column A & C, then the formula should only calculate using Column A-Column C*3%.
If there is a figure in Column A & B, then the formula should be based on the values of on both columns (6% on Column A + 12% on Column B)
If there is a figure in only Column D, then the formula should be column D*12%

I have this formula in column E =(A1)*6% + (B1)*12% + (C1)*12% + (D1)*3%

With this formula it works well for everything except the Column C as it returning to values, that of Column C (3%) plus that of column A (6%). I need it to return on the 3%.

Please help.

Thank you in advance

Angus

[Simon Lloyd]

Looks like you need a nested IF statement:
look here for explanation How to use nested IF's