Skip to main content
Solved

Commission Calculation Formula

  • April 11, 2023
  • 5 replies
  • 81 views
Jess_Helmstette
Forum|alt.badge.img+9

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!

 

 

Best answer by TheTimeSavingCo

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

MarcoConnex

5 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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

Adam, Airtable Consultant - thetimesaving.company
Jason_Hill
Forum|alt.badge.img+13
  • Jason_Hill
  • Inspiring
  • April 11, 2023

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}

Jess_Helmstette
Forum|alt.badge.img+9
  • Jess_Helmstette
  • Author
  • Known Participant
  • April 23, 2023

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!

Jess_Helmstette
Forum|alt.badge.img+9
  • Jess_Helmstette
  • Author
  • Known Participant
  • April 23, 2023

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!!

Jess_Helmstette
Forum|alt.badge.img+9
  • Jess_Helmstette
  • Author
  • Known Participant
  • April 24, 2023

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!

Terms & ConditionsAccessibility statement