Multiply ifs? Working on a commission workbook.

P

Pavavion

New Member
Joined
Jan 10, 2013
Messages
32
Good afternoon all I need some help with reworking a commission workbook. The goals in our office have change to the below structure and I am struggling to work out how best to input the formulas to get me to where i need.

We sell two products Radio & TV airtime.

Commission is based as below.

Write up to 85% of your budget on Radio earn 3.5% of everything you wrote on Radio.
OR
Write up to 102% of your budget on Radio earn 4.5% of everything you wrote on Radio.

PLUS
Write up to 85% of your budget on TV earn 2% of everything you wrote on Radio.
OR
Write up to 102% of your budget on Radio earn 3% of everything you wrote on Radio.

PLUS
Achieve your combined Radio+TV budget over 102% earn 20% on anything written above the combined 102%


I have where I am up to now after deleting many formulas and need some guidance how to get this done please?
Dropbox - Commision Worksheet.xlsx

I would like the sheet to work out the revenue amount applicable in the B columns. Once this is worked out then D columns are a simple multiple.
It is the conditions that i struggle with formulating.

EG: RADIO
If Radio budget achievement is <85% multiply actual by 3.5%
If Radio budget achievement is >85% but less then 102% multiply actual by 4.5%
If COMBINED Radio+TV budget achievement is >102% multiply actual (minus budget multiplied by 1.02%) by 20%

Unsure if this is explained well or not ..... hope the dropbox links helps??
 
Sort by date Sort by votes

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Re: Multiply ifs? Need help working on a commission workbook.

oldbrewer said:
Write up to 85% of your budget on TV earn 2% of everything you wrote on Radio.

is this correct

also up to 85% on radio pays 3.5% so 86% or 102% or 125% pays 3%, surely, otherwise workers will stop trying ??
Click to expand...


Radio:
00.00% - 85.00% = 3.5% of every Radio $ written up to 85.00%
OR
85.01% - 102.00% = 4.5% of every Radio $ written up to 102.00%

***********************************************************************

TV:
00.00% - 85.00% = 2.00% of every TV $ written up to 85.00%
OR
85.01% - 102.00% = 3.00% of every TV $ written up to 102.00%
***********************************************************************

Plus
>102.00% of the combined Radio+TV budget earn 20% of whatever revenue is written above the 102% of the combined Radio+TV budget.
 
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

AlanY said:
for clarification, let say radio achieved 100%

will the commission be 3.5% for the 85% plus 4.5% for the rest (i.e. 100-85=15%)?

or 4.5% for the full 100%?
Click to expand...


4.5% for the full 100%
 
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

Pavavion said:
4.5% for the full 100%
Click to expand...

one more question, if revenue is 200% of the budget, say.
will the 4.5% paid for the full 200% or just the 102%?
 
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

[TABLE="width: 1091"]
<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Comission Worksheet[/TD]
[TD]Michael[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reporting Month[/TD]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IBMS[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Credits[/TD]
[TD]Variance[/TD]
[TD]%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Radio Sales[/TD]
[TD="align: right"]52,330.00[/TD]
[TD="align: right"]52,330.00[/TD]
[TD="align: right"]62,000.00[/TD]
[TD="align: right"]2,971.00[/TD]
[TD="align: right"]-12,641.00[/TD]
[TD="align: right"]79.61%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TV QLD Agg Sales[/TD]
[TD="align: right"]58,657.00[/TD]
[TD="align: right"]58,657.00[/TD]
[TD="align: right"]45,000.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13,657.00[/TD]
[TD="align: right"]130.35%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Combined Personal[/TD]
[TD="align: right"]110,987.00[/TD]
[TD="align: right"]110,987.00[/TD]
[TD="align: right"]107,000.00[/TD]
[TD="align: right"]2,971.00[/TD]
[TD="align: right"]1,016.00[/TD]
[TD="align: right"]100.95%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Commission Structure[/TD]
[TD]Revenue[/TD]
[TD]Rate[/TD]
[TD]Commission[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Radio[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 85% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]3.50%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 102% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]4.50%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 85% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]2%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 102% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]3%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Combined 102% over Budget[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]20%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]actual[/TD]
[TD]credit[/TD]
[TD]budget[/TD]
[TD]BONUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]52,330.00[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]2221.16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]60,000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]2566.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]2791.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]3016.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]3916.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is there a top limit on bonus ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

AlanY said:
one more question, if revenue is 200% of the budget, say.
will the 4.5% paid for the full 200% or just the 102%?
Click to expand...

The 4.5% gets paid UP TO the 102%. any COMBINED amount over 102% is paid at 20%...
IF (very unlikley) Radio revenue is 200% of budget and your combined TV+Radio revenue is LESS than 102% of your COMBINED radio & TV budget. Then ALL radio revenue in this scenario is calculated at 4.5%.
 
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

oldbrewer said:
[TABLE="width: 1091"]
<tbody>[TR]
[TD]Comission Worksheet[/TD]
[TD]Michael[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reporting Month[/TD]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]IBMS[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Credits[/TD]
[TD]Variance[/TD]
[TD]%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Radio Sales[/TD]
[TD="align: right"]52,330.00[/TD]
[TD="align: right"]52,330.00[/TD]
[TD="align: right"]62,000.00[/TD]
[TD="align: right"]2,971.00[/TD]
[TD="align: right"]-12,641.00[/TD]
[TD="align: right"]79.61%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TV QLD Agg Sales[/TD]
[TD="align: right"]58,657.00[/TD]
[TD="align: right"]58,657.00[/TD]
[TD="align: right"]45,000.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13,657.00[/TD]
[TD="align: right"]130.35%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Combined Personal[/TD]
[TD="align: right"]110,987.00[/TD]
[TD="align: right"]110,987.00[/TD]
[TD="align: right"]107,000.00[/TD]
[TD="align: right"]2,971.00[/TD]
[TD="align: right"]1,016.00[/TD]
[TD="align: right"]100.95%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Commission Structure[/TD]
[TD]Revenue[/TD]
[TD]Rate[/TD]
[TD]Commission[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Radio[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 85% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]3.50%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 102% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]4.50%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 85% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]2%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Achieve up to 102% of Budget all revenue Paid at[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]3%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Combined 102% over Budget[/TD]
[TD]FORMULA NEEDED[/TD]
[TD="align: right"]20%[/TD]
[TD]FORMULA NEEDED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]actual[/TD]
[TD]credit[/TD]
[TD]budget[/TD]
[TD]BONUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]52,330.00[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]2221.16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]60,000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]2566.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]2791.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]3016.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]radio[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]2971[/TD]
[TD="align: right"]62000[/TD]
[TD="align: right"]3916.31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is there a top limit on bonus ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


no top limit on bonus. so when you hit combined OVER budget this is where the big money can be written.
 
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

try this


Excel 2012
ABCDEFG
1Comission WorksheetMichael
2Reporting MonthJuly
3
4
5IBMSActualBudgetCreditsVariance%
6Total Radio Sales52,330.0052,330.0062,000.002,971.00-12,641.0079.61%
7TV QLD Agg Sales58,657.0040,000.0045,000.000.00-5,000.0088.89%
8Total Combined Personal110,987.0092,330.00107,000.002,971.00-17,641.0083.51%
9
10Commission StructureRevenueRateCommission
11Radio
12Achieve up to 85% of Budget all revenue Paid at 3.5%52,330.003.50%1,831.55
13Achieve up to 102% of Budget all revenue Paid at 4.5%0.004.50%0.00
14Achieve over 102% of Budget all revenue Paid at 3%0.004.50%0.00
15TV
16Achieve up to 85% of Budget all revenue Paid at 2%38,250.002.00%765.00
17Achieve up to 102% of Budget all revenue Paid at 3%1,750.003.00%52.50
18Achieve over 102% of Budget all revenue Paid at 3%0.003.00%0.00
19Radio - Achieve over 102% of Budget all revenue Paid at 4.5%
20
21Combined 102% over Budget the amount written over 102%0.0020.00%0.00
2292,330.002,649.05
Sheet1
Cell Formulas
RangeFormula
B12=MIN(0.85*$D$6,$C$6)
B13=MIN(1.02*$D$6,$C$6)-$B$12
B14=IF(AND(($C$6+$E$6)>1.02*$D$6,($C$8+$E$8)<1.02*$D$8),$C$6+$D$6-SUM($B$12:$B$13),0)
B16=MIN(0.85*$D$7,$C$7)
B17=MIN(1.02*$D$7,$C$7)-$B$16
B18=IF(AND(($C$7+$E$7)>1.02*$D$7,($C$8+$E$8)<1.02*$D$8),$C$7+$E$7-SUM($B$16:$B$17),0)
B21=IF(($C$8+$E$8)>1.02*$D$8,$C$8+$E$8-SUM(B12:B18),0)
B22=SUM(B12:B21)
 
Last edited:
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

typo in cells A14 & A19, should read 4.5% & 3% respectively. formula are ok
 
Last edited:
Upvote 0
Re: Multiply ifs? Need help working on a commission workbook.

Thanks AlanY however the commission on both TV or radio is paid either as 85% or up to 102%.

Not cumulatively, eg TV Commissions should have a revenue figure in B17 and NO figure in B16, it's one or the other.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top