How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 36526
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

Im trying to create a formula that will calculate various

This answer was rated:

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?


Yes, it is done and you can download it from

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

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?

Of course, you can download it under File # XXXXX

Let me know if you have any difficulties please

Richard and 2 other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.

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


Thanks for your help.

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

Looking forward to the next time!


Related Microsoft Office Questions