# Commission Calculation Formula

Topic Labels: Formulas
Solved
910 5
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Hello!

I have a formula that calculates commission and I need to add an additional calculation that is tripping me up. I am starting with the following formula.

SUM(IF({Commission Based On},{Commission Based On},{Purchase Price (Current/Final)})*{Commission %})+ {Commission Flat Fee}+{Admin/Office Fee}+{Other Fee}-{Concession}-{Referral Flat Fee}

I added the following field to my table and need to add it to the formula.
{Referral %}

The first part of the current formula takes the purchase price and multiplies it by the commission % plus the commission flat fee.
SUM(IF({Commission Based On},{Commission Based On},{Purchase Price (Current/Final)})*{Commission %})+ {Commission Flat Fee}

But next I need it to take the result of the first part of the formula (which would be the total commission amount), multiply it by {Referral %}, add {Referral Flat Fee}, then subtract those two resulting amounts from the commission amount.

Then I will add/subtract the remaining items.

Any help would be appreciated!

2 Solutions

Accepted Solutions
16 - Uranus

Hi Jess, maybe this will work:

``````IF(
{Commission Based On},
{Commission Based On},
{Purchase Price (Current/Final)}
) *
{Commission %} +
{Commission Flat Fee} -
(
(
IF(
{Commission Based On},
{Commission Based On},
{Purchase Price (Current/Final)}
) *
{Commission %} +
{Commission Flat Fee}
) &
{Referral %}
) -
{Referral Flat Fee}	+
{Other Fee} -
{Concession} ``````

I removed the "SUM" bits because it doesn't look like they're doing anything?  Sorry if that breaks your formula!

I would also suggest considering splitting the formula out so that it's clearer, e.g. creating a "Commission" formula field that displays the commission amount

6 - Interface Innovator

Maybe this. It's just a quick stab at it without re-creating the structure to test it.

(SUM(IF({Commission Based On},{Commission Based On},{Purchase Price (Current/Final)})*{Commission %})+ {Commission Flat Fee}) * (1 - {Referral %}) + {Referral Flat Fee} + {Admin/Office Fee} + {Other Fee} - {Concession}

5 Replies 5
16 - Uranus

Hi Jess, maybe this will work:

``````IF(
{Commission Based On},
{Commission Based On},
{Purchase Price (Current/Final)}
) *
{Commission %} +
{Commission Flat Fee} -
(
(
IF(
{Commission Based On},
{Commission Based On},
{Purchase Price (Current/Final)}
) *
{Commission %} +
{Commission Flat Fee}
) &
{Referral %}
) -
{Referral Flat Fee}	+
{Other Fee} -
{Concession} ``````

I removed the "SUM" bits because it doesn't look like they're doing anything?  Sorry if that breaks your formula!

I would also suggest considering splitting the formula out so that it's clearer, e.g. creating a "Commission" formula field that displays the commission amount

6 - Interface Innovator

Maybe this. It's just a quick stab at it without re-creating the structure to test it.

(SUM(IF({Commission Based On},{Commission Based On},{Purchase Price (Current/Final)})*{Commission %})+ {Commission Flat Fee}) * (1 - {Referral %}) + {Referral Flat Fee} + {Admin/Office Fee} + {Other Fee} - {Concession}

6 - Interface Innovator

Thank you so much!

6 - Interface Innovator

Thank you for your help Jason!!

6 - Interface Innovator

I did some testing and yours works as well! Thanks Jason!