Multiply ifs? Working on a commission workbook.

[Pavavion]

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??

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

my query was "meet budget on tv and get a % of radio sales...........

Sorry Oldbrewer there was a mistake in my post, have corrected it in an earlier reply

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

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 ??

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.

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

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%?

4.5% for the full 100%

 

[AlanY]

Re: Multiply ifs? Need help working on a commission workbook.

4.5% for the full 100%

one more question, if revenue is 200% of the budget, say.
will the 4.5% paid for the full 200% or just the 102%?

 

[oldbrewer]

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]

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

one more question, if revenue is 200% of the budget, say.
will the 4.5% paid for the full 200% or just the 102%?

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%.

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

[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]

no top limit on bonus. so when you hit combined OVER budget this is where the big money can be written.

 

[AlanY]

Re: Multiply ifs? Need help working on a commission workbook.

try this

Excel 2012
	A	B	C	D	E	F	G
1	Comission Worksheet	Michael
2	Reporting Month	July
3
4
5		IBMS	Actual	Budget	Credits	Variance	%
6	Total Radio Sales	52,330.00	52,330.00	62,000.00	2,971.00	-12,641.00	79.61%
7	TV QLD Agg Sales	58,657.00	40,000.00	45,000.00	0.00	-5,000.00	88.89%
8	Total Combined Personal	110,987.00	92,330.00	107,000.00	2,971.00	-17,641.00	83.51%
9
10	Commission Structure	Revenue	Rate	Commission
11	Radio
12	Achieve up to 85% of Budget all revenue Paid at 3.5%	52,330.00	3.50%	1,831.55
13	Achieve up to 102% of Budget all revenue Paid at 4.5%	0.00	4.50%	0.00
14	Achieve over 102% of Budget all revenue Paid at 3%	0.00	4.50%	0.00
15	TV
16	Achieve up to 85% of Budget all revenue Paid at 2%	38,250.00	2.00%	765.00
17	Achieve up to 102% of Budget all revenue Paid at 3%	1,750.00	3.00%	52.50
18	Achieve over 102% of Budget all revenue Paid at 3%	0.00	3.00%	0.00
19	Radio - Achieve over 102% of Budget all revenue Paid at 4.5%
20
21	Combined 102% over Budget the amount written over 102%	0.00	20.00%	0.00
22		92,330.00		2,649.05
Sheet1

Cell Formulas
Range	Formula
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)

 

[AlanY]

Re: Multiply ifs? Need help working on a commission workbook.

typo in cells A14 & A19, should read 4.5% & 3% respectively. formula are ok

 

[Pavavion]

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.