Skip to main content

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!

 

 

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} +
{Admin/Office 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

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}

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} +
{Admin/Office 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


Thank you so much!

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}


Thank you for your help Jason!!

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}


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

Reply


Terms & Conditions