Returning a value - Calculating Commissions

[Mark Angus]

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

[#2]

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

[#3]

@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

[#4]

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

[#5]

Hi @Mark Angus ,

Attached the revised file.

Best Regards!

[#6]

Thanks very much :)