May 26 2020 06:00 PM
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
May 26 2020 09:13 PM
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.
If this formula fit your expectations, please accept as best response.
Regards,
Karchavez
May 26 2020 09:49 PM
@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).
Can you provide an adjusted formula to reflect this please?
Really helpful - Thanks
Mark
May 26 2020 10:30 PM
@Mark Angus Move your percentages to F and use VLOOKUP to avoid nested IF statements. See attached.
May 26 2020 10:47 PM