Highlighted
New Contributor

Returning a value - Calculating Commissions

Hi need to calculate commissions

There is a different commission % depending on the value of the sale

Here's what I have done (attached)

How do I write a formula that compares the value of the sale with the commission structure and returns the correct amount, please?

I got only so far (in yellow) - but I can't work out how to check multiple commision ranges to select the correct comm structure (my yellow example only checks the \$0-\$100k range).

Thanks

Mark

5 Replies
Highlighted

Re: Returning a value - Calculating Commissions

Hi @Mark Angus ,

Please see attached the solution file and the explanation of the formula, I can assure you it works, since I usually use it in my job.

Regards,

Karchavez

Highlighted

Re: Returning a value - Calculating Commissions

@karchavez Thanks!

Slight problem...

The calculation is not quite right.  The commission is a percentage of the Agents Commission but it is triggered by the Rent pa (per annum figure). For Example;

If the rent per annum is \$273,300, then the commission should be 5% of the Agents Commision (\$46,461), which equals \$2,323.

At the moment the formula is calculating the commission as a percentage of the Rent pa (not the agents commission).

Mark

Highlighted

Re: Returning a value - Calculating Commissions

@Mark Angus Move your percentages to F and use VLOOKUP to avoid nested IF statements. See attached.

Highlighted

Re: Returning a value - Calculating Commissions

Hi @Mark Angus ,

Attached the revised file.

Best Regards!

Highlighted

Thanks very much