Returning a value - Calculating Commissions

%3CLINGO-SUB%20id%3D%22lingo-sub-1419235%22%20slang%3D%22en-US%22%3EReturning%20a%20value%20-%20Calculating%20Commissions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419235%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20need%20to%20calculate%20commissions%3C%2FP%3E%3CP%3EThere%20is%20a%20different%20commission%20%25%20depending%20on%20the%20value%20of%20the%20sale%3C%2FP%3E%3CP%3EHere's%20what%20I%20have%20done%20(attached)%3C%2FP%3E%3CP%3EHow%20do%20I%20write%20a%20formula%20that%20compares%20the%20value%20of%20the%20sale%20with%20the%20commission%20structure%20and%20returns%20the%20correct%20amount%2C%20please%3F%3C%2FP%3E%3CP%3EI%20got%20only%20so%20far%20(in%20yellow)%20-%20but%20I%20can't%20work%20out%20how%20to%20check%20multiple%20commision%20ranges%20to%20select%20the%20correct%20comm%20structure%20(my%20yellow%20example%20only%20checks%20the%20%240-%24100k%20range).%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EMark%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1419235%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419405%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20value%20-%20Calculating%20Commissions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419405%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F115392%22%20target%3D%22_blank%22%3E%40Mark%20Angus%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EPlease%20see%20attached%20the%20solution%20file%20and%20the%20explanation%20of%20the%20formula%2C%20I%20can%20assure%20you%20it%20works%2C%20since%20I%20usually%20use%20it%20in%20my%20job.%3C%2FP%3E%3CP%3EIf%20this%20formula%26nbsp%3B%20fit%20your%20expectations%2C%20please%20accept%20as%20best%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EKarchavez%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419449%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20value%20-%20Calculating%20Commissions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419449%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F670788%22%20target%3D%22_blank%22%3E%40karchavez%3C%2FA%3E%26nbsp%3BThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESlight%20problem...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20calculation%20is%20not%20quite%20right.%26nbsp%3B%20The%20commission%20is%20a%20percentage%20of%20the%20Agents%20Commission%20but%20it%20is%20triggered%20by%20the%20Rent%20pa%20(per%20annum%20figure).%20For%20Example%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20rent%20per%20annum%20is%20%24273%2C300%2C%20then%20the%20commission%20should%20be%205%25%20of%20the%20Agents%20Commision%20(%2446%2C461)%2C%20which%20equals%20%242%2C323.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20moment%20the%20formula%20is%20calculating%20the%20commission%20as%20a%20percentage%20of%20the%20Rent%20pa%20(not%20the%20agents%20commission).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20provide%20an%20adjusted%20formula%20to%20reflect%20this%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20helpful%20-%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMark%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419496%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20value%20-%20Calculating%20Commissions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F115392%22%20target%3D%22_blank%22%3E%40Mark%20Angus%3C%2FA%3E%26nbsp%3BMove%20your%20percentages%20to%20F%20and%20use%20VLOOKUP%20to%20avoid%20nested%20IF%20statements.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419533%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20value%20-%20Calculating%20Commissions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419533%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F115392%22%20target%3D%22_blank%22%3E%40Mark%20Angus%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EAttached%20the%20revised%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419565%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20value%20-%20Calculating%20Commissions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419565%22%20slang%3D%22en-US%22%3EThanks%20very%20much%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

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

Highlighted

@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

Highlighted

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

 

Highlighted

Hi @Mark Angus ,

Attached the revised file.

 

Best Regards!

Highlighted
Thanks very much