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

 

[Muhammad_Usman]

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

Dear
Share a screen shot of your current data and requirement, Your link is not working at my side.

 

[oldbrewer]

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

 

[AlanY]

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

 

[Muhammad_Usman]

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

If i understood you want to do like this.. ???

1000	85%	850	29.75
1000	90%	900	31.50
1000	95%	950	33.25
1000	100%	1000	35.00

<tbody>
</tbody>

=IF(C1<=A1*B1,C1*3.5%,(A1*85%*3.5%)+(A1-(A1*85%*3.5%))*4.5%)

 

[oldbrewer]

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

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

 

[Muhammad_Usman]

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

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

Show your requirement in table format!

 

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

Sorry The green text i had above was wrong it should have read:

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

 

[Pavavion]

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

Hi Muhammad this is what I am trying to achieve:

Comission Worksheet	Michael
Reporting Month	July
	IBMS	Actual	Budget	Credits	Variance	%
Total Radio Sales	52,330.00	52,330.00	62,000.00	2,971.00	-12,641.00	79.61%
TV QLD Agg Sales	58,657.00	58,657.00	45,000.00	0.00	13,657.00	130.35%
Total Combined Personal	110,987.00	110,987.00	107,000.00	2,971.00	1,016.00	100.95%
Commission Structure	Revenue	Rate	Commission
Radio
Achieve up to 85% of Budget all revenue Paid at	FORMULA NEEDED	3.5%	FORMULA NEEDED
Achieve up to 102% of Budget all revenue Paid at	FORMULA NEEDED	4.5%	FORMULA NEEDED
TV
Achieve up to 85% of Budget all revenue Paid at	FORMULA NEEDED	2%	FORMULA NEEDED
Achieve up to 102% of Budget all revenue Paid at	FORMULA NEEDED	3%	FORMULA NEEDED
Combined 102% over Budget	FORMULA NEEDED	20%	FORMULA NEEDED
	0.00		0.00

<tbody>
</tbody>

 

[Pavavion]

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

Dear
Share a screen shot of your current data and requirement, Your link is not working at my side.