Salesperson Commission Formula

[Jess_Helmstette]

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!

[TheTimeSavingCo · Accepted solution]

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

[Jason_Hill · Accepted solution]

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]

Thank you so much!

[Jess_Helmstette]

Thank you for your help Jason!!

[Jess_Helmstette]

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