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.

	A	B	C	D	E	F	G	H	I
1			% in Tier
2		Units Sold	1	2	3	4		Tier	Unit Quota
3	Q1	100	100%					1	100
4	Q2	100		50%				2	200
5	Q3	125		50%	14.5%			3	172
6	Q4	175			85.5%	100%		4	28

<tbody>
</tbody>

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J
1			% in Tier
2		Units Sold	1	2	3	4		Tier	Unit Quota
3
4	Q1	100	100.0%					1	100
5	Q2	100		50.0%				2	200
6	Q3	125		50.0%	14.5%			3	172
7	Q4	175			85.5%	100.0%		4	28

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula C4 =IF(SUM(C$3:C3)=1,"",MIN(1,SUM(B$4:B4)/$I$4)-SUM(C$3:C3)) D4 =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))),"")) <tbody> </tbody>

<tbody>
</tbody>

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.