Help

Re: Commission Calculation Formula

Solved
Jump to Solution
853 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jess_Helmstette
6 - Interface Innovator
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
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

See Solution in Thread

Jason_Hill
6 - Interface Innovator
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}

See Solution in Thread

5 Replies 5
TheTimeSavingCo
17 - Neptune
17 - Neptune

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
6 - Interface Innovator
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}

Thank you so much!

Thank you for your help Jason!!

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