Commission Calculation

**smarch001** · 03-13-2009, 10:38 AM

Could someone please help me with a commission calc?

This is what I need the spread sheet to calc:

Column A are the various revenue amounts the system would generate per month.
Column B is my commission percentage

I would like to be able to enter a number for office expenses (F3) and a number of total revenue (F5) and have the spreadsheet calc my commission (F7) and the commission that goes to the other 2 partners (F9)

If there was just one scenario the formula for F7 would be something like this =sum(F5-F3)*the correlating percentage in column B from F5.

I know how to figure it out for each scenario, but I don't know how to have excel point to the correct percentage in column B when an amount is entered into F5.

F9 would be (F5-F3-F7)/2

I attached the file for review. Could you please help me with this? I really appreciate it!

**Special-K** · 03-13-2009, 10:57 AM

Problem No. 1: Your finish revenue is the same as your start revenue, e.g. 10000 appears as a finish % and a start percentage so which percentage applies. You need to fix this first. It should prob read.

0 - 10000
10001 - 15000
15001 -
etc

In column A enter the start price ONLY

In column B enter the finish prices 10000,15000,20000 etc

In column C enter this formula

=(IF(AND($H$5>=A3,$H$5<=B3),1,0)) and copy it down column

In another cell (anywhere) enter

=INDEX($A$3:$D$13,MATCH(1,$C$3:$C$13,0),4)

This will be the percentage you're after

**Ron Coderre** · 03-13-2009, 10:58 AM

I edited your posted workbook so it now included a lookup table in A1:C13

Please Login or Register  to view this content.

Now....With
G1: (office expenses.....eg 500)
G5: (dialer revenue......eg 15,000)

Then....
Net Revenue
G6: =+G5-G3

Comp Pct
G7: =VLOOKUP(G6,A3:C13,3,1)

March's Share
G9: =G6*G7

Boney/Hobby Share
G11: =(G6-G9)/2

In the above example:
Net Revenue: 14,500
Comp Pct: 30%
March's share: 4,350
Boney/Hobby Share: 5,075

Does that help?

**smarch001** · 03-13-2009, 11:14 AM

Originally Posted by Ron Coderre

I edited your posted workbook so it now included a lookup table in A1:C13

Please Login or Register  to view this content.

Now....With
G1: (office expenses.....eg 500)
G5: (dialer revenue......eg 15,000)

Then....
Net Revenue
G6: =+G5-G3

Comp Pct
G7: =VLOOKUP(G6,A3:C13,3,1)

March's Share
G9: =G6*G7

Boney/Hobby Share
G11: =(G6-G9)/2

In the above example:
Net Revenue: 14,500
Comp Pct: 30%
March's share: 4,350
Boney/Hobby Share: 5,075

Does that help?

Perfect. Thank you very much!