Hi,
I am trying to create a commission calculator based on a number of variable factors:
1) If the Broker Commission (Column F) is either (variable 1) $0, or (variable 2) an actual $ amount
2) If the Revenue Type (Column D) is either (variable 3) "IAF", or (variable 4) "Commission"
Depending on combination of these 4 variables I need the formula in Column G to be as follows:
Combination of Variable 1 & Variable 3
If Column F is $0 and Column D is "IAF" then the formula should be E*0.84
Combination of Variable 2 and Variable 3
If Column F contains a $ amount and Column D is "IAF" then the formula should be (E-F)*0.84
Combination of Variable 1 and Variable 4
If Column F is $0 and Column D is "Commission" then the formula should be (E*0.66)*0.84
Combination of Variable 2 and Variable 4
If Column F contains a $ amount and Column D is "Commission" then the formula should be ((E*0.66)*0.9)*0.84
All the examples in the attached image are correct (Row 3,4,5,6) as I have just used basic formulas to ensure the above formulas are correct for each combination... its just amalgamating these 4 combinations of Column G into a single formula that works no matter what the combination of variables is that is beyond me.
I have tried (see my formula in the text box) in what I hoped would be correct in Row 8 with "AND" function/s but nothing worked. If all is working then G8 should read $498.96.
Appreciate any help possible
Capture.JPG
Bookmarks