Commision Figure Sheet: Figuring Commission After a Certain $ Amount

Good afternoon!

I'm trying to put together a spreadsheet that will figure the commission of a sales person, once they make $5000 in sales. I've attached the screenshot of my sheet.

Essentially, we want to be able to put the totals of orders in a log format, but, once the total sales reaches $5000, we want a formula to kick in calculating the commission amount based on each job after $5000.

So, if we have three jobs in the F column at $1500, $2500, & $1250, we want the spreadsheet to take the balance over $5000 and apply a percentage to it, such as 10% (the G column). After that point, we'd like the sheet to continue calculating the commission into the H column.

Does anyone know of a way to apply this? I've been working at it for almost a day, but I'm stumped. Any and all help is appreciated!

Thanks!

Melvosh:

We're wanting it based on each row, if possible, but only after the total in F comes to $5000. Anything over that is available for commission earnings.

Originally Posted by modytrane

Attach a sample file with some data.
How are the order amounts organized in column F?
Is it one row for an order or multiple rows for same order?
Where is the total calculated?
Your attached copy shows different % for each row in column H.
Not clear on how to figure out total.
How does the formula know which cells to total?

I've attached a version of the spreadsheet from when I was working on it earlier today. My boss essentially wants column F to add together until it reaches $5000. Once it reaches $5000, take any over $5000 and multiply it by the percentage in column H.

In the version I'm attaching, there is an extra column with the running total hidden. That one was created when I thought she wanted the sum to reset after each $5000, which is not the case.

The organization in column F is just in order of entry. I assume she's going to be putting them in based on oldest job to newest job, but there is no set sorting in column F.

Sorry if I didn't quite answer your questions, I'm still a bit of a novice with Excel.

One change to mehmetcik's formula to account for the row that hits the 5000 mark:

Please Login or Register  to view this content.

Originally Posted by modytrane

HTML Code: 

How do you separate jobs? What defines each job?

We're a printing company, so a "job" to us is a set of printing we have created for a customer, so, for example, a job might be printing 500 business cards. The invoice total would be what is placed in column F. After that might be brochures for another customer, that would be the next row, etc.

If these need to be limited / grouped by job #s or customer, we'll need a larger set of data to account for that.

Originally Posted by Melvosh

If these need to be limited / grouped by job #s or customer, we'll need a larger set of data to account for that.

Nope, they'll just be entered in as we get them. But, Melvosh, your formula is doing exactly as I needed! I'm going to send the sheet over to the one who is going to be using it the most, but I think she'll be happy. Just entering in some test data, everything seems to be working fine!

Please keep in mind that I just made a slight modification to mehmetcik's formula Glad we could help.