Table to work out take home pay from total commission

**quiggdavid** · 08-23-2023, 05:40 AM

Hello All,

Just after a bit of help as I cant get my head round how to set this up.

I want to create a table showing take home pay from commission earnt

The difficulty being the commission percentage isn't flat, from 0 to 15000 the sales person earns 5%, from 15001 to £35000 the sales person earns 10% and anything above this the sales person earns £20%.

For example,

comms of 45,000
0-15000 5% = ?750
15001- 35000 10% = ?2000
35001-45000= 20% 2000
Total take home 4750

Any ideas how to set this table up?

Thanks in advance

**AliGW** · 08-23-2023, 05:42 AM

Are you still using Excel 2004 for Mac???

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**quiggdavid** · 08-23-2023, 06:22 AM

I'm not using Excel mac 2004, I'm on Microsoft 365 these days, time for a much needed profile update!

I've attached the workbook showing how I think (and would like) it to look. Comms Summary.xlsx

Thanks in Advance

**AliGW** · 08-23-2023, 06:30 AM

One way:

=IF(C10>0,750,0)+IF(C10>15000,2000,0)+IF(C10>35000,2000,0)

**quiggdavid** · 08-23-2023, 07:27 AM

I think that's part of it and you've definitely got me in the right direction.

I think I need 3 separate if functions on c 12,13 and 14 then to add them up in c15. I'm just not sure exactly how to write the last 2.

First one is
=IF($C$10<750,750,$C$10*0.05)

for the next two rows I need to isolate the amounts from 15001 to 35000
and 35,000+

**AliGW** · 08-23-2023, 07:30 AM

$C$10*0.05

Why? Where does this come from? What aren't you telling us?

**AliGW** · 08-23-2023, 07:34 AM

Maybe in C12:

=LET(v,VSTACK(IF(C10>0,750,0),IF(C10>15000,2000,0),IF(C10>35000,2000,0)),VSTACK(v,SUM(v)))

**AliGW** · 08-23-2023, 07:40 AM

Ah! YOu forgot to put the percentages in the workbook!!! No wonder I'm confused!!!

**quiggdavid** · 08-23-2023, 07:41 AM

if the commission is less than 15000 the times that amount by 5% to show total comms.
So if the generated 13000 in sales their take home would be £650.

Just spotted 2 mistakes I made in the above formula. if should read if c10 is greater than 15000 not 750 and I;ve got more than and less than wrong
=IF($C$10>15000,750,$C$10*0.05)

**AliGW** · 08-23-2023, 07:44 AM

Try this:

=LET(v,VSTACK(IF(C10>0,15000*0.05,0),IF(C10>15000,(35000-15000)*0.1,0),IF(C10>35000,(C10-35000)*0.2,0)),VSTACK(v,SUM(v)))

**quiggdavid** · 08-23-2023, 07:50 AM

99.9% of the way there thanks very much, Just having a play round with it so see if I can figure the last bit out

THanks for all your help

**AliGW** · 08-23-2023, 07:57 AM

What's the lat bit? What aren't you telling us? It's 100% of the way there based on what you've shared.

This???

=LET(a,--TEXTAFTER(B12,"-"),b,--TEXTBEFORE(B14,"+"),v,VSTACK(IF(C10>0,a*0.05,0),IF(C10>a,(b-a)*0.1,0),IF(C10>b,(C10-b)*0.2,0)),VSTACK(v,SUM(v)))

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**quiggdavid** · 08-23-2023, 08:13 AM

I'm confused here now as I cant get the answer to change.
Have a look at the attached, assuming the sales person generates over £35k of business then it works fine.
However when they generate less than that the numbers are not correct.
On the attached c12 should display 5% of 1000 and c26 should show 10% of 1000

Comms Summary DQa.xlsx

**AliGW** · 08-23-2023, 08:44 AM

Good spot!

Try this:

=LET(a,--TEXTAFTER(B12,"-"),b,--TEXTBEFORE(B14,"+"),v,VSTACK(IF(C23>0,a*0.05,0),IF(C23>a,(MIN(C23,b)-a)*0.1,0),IF(C23>b,(C23-b)*0.2,0)),VSTACK(v,SUM(v)))

Or:

=LET(a,--TEXTAFTER(B12,"-"),b,--TEXTBEFORE(B14,"+"),v,VSTACK(IF(C23>0,a*A25,0),IF(C23>a,(MIN(C23,b)-a)*A26,0),IF(C23>b,(C23-b)*A27,0)),VSTACK(v,SUM(v)))

**AliGW** · 08-23-2023, 09:07 AM

How are we doing? Any further questions?

Thanks for the rep.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

**quiggdavid** · 08-23-2023, 09:13 AM

This is more complex than I realised.

I'm not sure this works though (or I cant get it to work at least).
How do I include B13? the 15 to 35k?

I don't seem to be able to get this to work if c10 is sub £35k.

Have a look and see were I'm going wrong

Comms Summary DQb.xlsx

**AliGW** · 08-23-2023, 09:24 AM

Here you go:

=LET(a,--TEXTAFTER(B12,"-"),b,--TEXTBEFORE(B14,"+"),v,VSTACK(IF(C10>0,MIN(C10,a)*A12,0),IF(C10>a,(MIN(C10,b)-a)*A13,0),IF(C10>b,(C10-b)*A14,0)),VSTACK(v,SUM(v)))

**AliGW** · 08-23-2023, 09:43 AM

Are we OK now???

**AliGW** · 08-23-2023, 10:14 AM

I see you've marked it as solved.

Table to work out take home pay from total commission

LinkBack

Thread Tools

Rate This Thread

Display

Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Re: Table to work out take home pay from total commission

Thread Information

Users Browsing this Thread

Similar Threads

add work from home column

Help with my home work please.

I can't work out how to make a 'total' line in data table...anyone have any idea?

How to calculate total work hours for workers from week table?

Help for moving average, commission of a day and yearly total commission of salesperson

[SOLVED] How to spot home work?

[SOLVED] calculate commission $ based on total sold and commission percent

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions