Distributing units across quotas

[kcmuppet]

Hi, I'd greatly appreciate help finding a formula that will calculate the figures in red (cells C3:F6) in my mockup below.

Column I contains sales thresholds at which different bonuses are paid in 4 tiers labelled in Column H. If I sell 100 units in Quarter 1 (Cell B3), that would mean all of the units expected in the Tier Quota in will have been sold in Quarter 1, so cell C3 is 100%.

I've got quite a lot of excel experience but can't fathom how to do this so would greatly appreciate some help.




[TABLE="class: grid, width: 622"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]% in Tier[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"]Units Sold[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Tier[/TD]
[TD="align: right"]Unit Quota[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: center"]Q1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: center"]Q2[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]50%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: center"]Q3[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]14.5%[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]172[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: center"]Q4[/TD]
[TD="align: right"]175[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]85.5%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]28[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J
% in Tier
Units Sold	Tier	Unit Quota
Q1
Q2
Q3
Q4

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]100.0%[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]50.0%[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]125[/TD]

[TD="align: right"]50.0%[/TD]
[TD="align: right"]14.5%[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]175[/TD]

[TD="align: right"]85.5%[/TD]
[TD="align: right"]100.0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=IF(SUM(C$3:C3)=1,"",MIN(1,SUM(B$4:B4)/$I$4)-SUM(C$3:C3))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=IF(SUM(C$3:C4)<1,"",IFERROR(1/(1/(MIN(1,(SUM($B$4:$B4)-SUM(OFFSET($I$4,0,0,COLUMNS($D:D))))/OFFSET($I$4,COLUMNS($D:D),0))-SUM(<font c
D$3:D3))),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Notice that I put an empty row above the data to make the formulas easier(!). You can hide it if you want. Put the C4 formula in and drag it down to C7. Then put the D4 formula in and copy it to D5:D7 and E4:F7.

 

[kcmuppet]

Eric, Ingenious - many thanks

 

[Eric W]

Glad to help.