• 100% Satisfaction Guarantee
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 33890
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now

# Im trying to create a formula that will calculate various

I'm trying to create a formula that will calculate various commission splits at certain thresholds.

Example: One could create multiple thresholds depending on the personal structure of various agents.

Agent A could have the following splits/thresholds:

Threshold 1 - Up to \$250K - 50% (or whatever one may choose)
Threshold 2 - \$250,001 - \$600K - 60%
Threshold 3 - \$600,001 - \$750K - 70%
Threshold 4 - \$750,001 - \$1M - 80%
Threshold 5 \$1,000,001 - up - 90%

Agent B could have varying thresholds/splits and could be input as such.

So, I need a formula that upon entering an agents "gross commission", the cells will populate with the proper "net" splits to the agent

So, if Agent A earned \$400K in gross commissions, \$250K would be at 50%, and \$150K would be at 60% - so "net" to agent would be \$215K (\$125K + \$90K).

Any ideas?
Thank you for your question, my name is Richard.

Ill write this for you, give me 10 minutes please

Customer: replied 3 years ago.

Great - will you have a spreadsheet with the cells created so I can work on it?

Under File ID 899564

The Formula is in B2

Let me know if you have any difficulties please

Customer: replied 3 years ago.

Okay, that works for the parameters given - I want to be able to select different parameters depending on the individual agents structure. So, another agent could have different thresholds and split levels.

So, say I wanted threshold 1 to be \$200K, or \$300K - and the split level could go to 80% after threshold 3, which may be \$500K.

I want to be have flexibility with how I set the parameters whereas this locks me into those set.

no problem, File ID 698714 at www.wikisend.com

Let me know if you have any difficulties please
Customer: replied 3 years ago.

Looks good, what if I wanted to add one more threshold, say, at 1.2M (or whatever is selected)

then, I think we're there!

Then you would need to reprogram the formula Brett, there is no automated way to do this.

Or just come back here and have me do it :)

If you would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.

So, can you add that final threshold?

Let me know if you have any difficulties please

Customer: replied 3 years ago.

i think it looks good - just going to do some "testing" then I will rate -

No problem and thanks.

Customer: replied 3 years ago.

I just see a blank message?
Customer: replied 3 years ago.

I rated your service and then it told me I didn't have acess to the page - it looks like it went through though. If I have future questions - how would I get in touch with you?

It did come through, thanks Brett.

You can always make a question to me on this link