PROPORTIONATE DISTRIBUTION - CHALLENGING QUESTION 4 SURE

[FARAZ QURESHI]

I have actually a very lengthy file of statistics however a small sample is
provided as follows:

Column B reflects Sales of different products as per column A, in year 2007
as follows:

Col - A Col - B
Product 2007
Item 1 5,000
Item 2 2,100
Item 3 800
Item 4 7,100
Item 5 8,400
Item 6 4,800
Item 7 9,700
Item 8 8,800
Item 9 2,000

while columns C:F reflect the contributions made by sales agents as follows:

Col - C Col - D Col - E Col - F
Agent 1 Agent 2 Agent 3 Agent 4
40% 10% 30% 20%
10% 40% 0% 50%
20% 40% 20% 20%
40% 20% 30% 10%
30% 20% 10% 40%
10% 30% 50% 10%
30% 50% 0% 20%
0% 40% 10% 50%
30% 0% 30% 40%

now Column G shows the sales of the year 2008, while Column H shows the
agents contributing this time as follows:

Col - G Col - H
2008 Agents
9,000 1, 2, 4
1,100 2, 3
2,100 1, 3, 4
15,600 4
15,400 1, 4
7,700 1, 2, 3, 4
18,600 3, 4
11,300 1, 2, 4
3,800 2, 4

I want a formula in Columns I:L distributing the amount of sales 2008
amongst the agents participating this time in the following format:

Col - A Col - I Col - J Col - K Col - L
Product Agent 1 Agent 2 Agent 3 Agent 4
Item 1
Item 2
Item 3
Item 4
Item 5
Item 6
Item 7
Item 8
Item 9

Where any of the agent was not found to be contributing any amount last
year, i.e. 0%, then the amount he should be denoted with an equally
proportionate amount of 2008 while the others would share the remaining
amount in their former ratio. For example, item 8's sales of 2008 of 11,300
would be divided as 3,767 (i.e. 1/3) for Agents 1 because Agent had
contributed nothing last year and this time there are 3 agents involved.
While the remaining 7,533 would be divided in the proportion of 4:5 amongst
Agents B & D, i.e. 3,348 & 4,185 repectively. Sure is a challenge but sure am
relying upon your expertise blind-folded.

Thanx in advance,
&
Best Regards,

FARAZ A. QURESHI

 

[Lars-Åke Aspelin]

I have actually a very lengthy file of statistics however a small sample is
provided as follows:

Column B reflects Sales of different products as per column A, in year 2007
as follows:

Col - A Col - B
Product 2007
Item 1 5,000
Item 2 2,100
Item 3 800
Item 4 7,100
Item 5 8,400
Item 6 4,800
Item 7 9,700
Item 8 8,800
Item 9 2,000

while columns C:F reflect the contributions made by sales agents as follows:

Col - C Col - D Col - E Col - F
Agent 1 Agent 2 Agent 3 Agent 4
40% 10% 30% 20%
10% 40% 0% 50%
20% 40% 20% 20%
40% 20% 30% 10%
30% 20% 10% 40%
10% 30% 50% 10%
30% 50% 0% 20%
0% 40% 10% 50%
30% 0% 30% 40%

now Column G shows the sales of the year 2008, while Column H shows the
agents contributing this time as follows:

Col - G Col - H
2008 Agents
9,000 1, 2, 4
1,100 2, 3
2,100 1, 3, 4
15,600 4
15,400 1, 4
7,700 1, 2, 3, 4
18,600 3, 4
11,300 1, 2, 4
3,800 2, 4

I want a formula in Columns I:L distributing the amount of sales 2008
amongst the agents participating this time in the following format:

Col - A Col - I Col - J Col - K Col - L
Product Agent 1 Agent 2 Agent 3 Agent 4
Item 1
Item 2
Item 3
Item 4
Item 5
Item 6
Item 7
Item 8
Item 9

Where any of the agent was not found to be contributing any amount last
year, i.e. 0%, then the amount he should be denoted with an equally
proportionate amount of 2008 while the others would share the remaining
amount in their former ratio. For example, item 8's sales of 2008 of 11,300
would be divided as 3,767 (i.e. 1/3) for Agents 1 because Agent had
contributed nothing last year and this time there are 3 agents involved.
While the remaining 7,533 would be divided in the proportion of 4:5 amongst
Agents B & D, i.e. 3,348 & 4,185 repectively. Sure is a challenge but sure am
relying upon your expertise blind-folded.

Thanx in advance,
&
Best Regards,

FARAZ A. QURESHI

Try this:

Note: replace all ; with , in the formulas below if that is your
setting for delimiter character

Introduce three helper columns (M, N and O)
Those columns can be hidden if you dont like to see them.

In cell M2 you put

=SUM(- -ISNUMBER(SEARCH({"1";"2";"3";"4"};H2)))
This is an array formual that has to be entered using CTRL+SHIFT+ENTER

In cell N2 you put:

=SUMPRODUCT(- -ISNUMBER(SEARCH({"1";"2";"3";"4"};H2));(-
-(TRANSPOSE(C2:F2)=0)))
This is an array formual that has to be entered using CTRL+SHIFT+ENTER

In cell O2 you put:

=SUMPRODUCT(- -ISNUMBER(SEARCH({"1";"2";"3";"4"};H2));-
-TRANSPOSE((C2:F2)))
This is an array formual that has to be entered using CTRL+SHIFT+ENTER

In cell I2 you put:

=$G2*IF(ISNUMBER(SEARCH(RIGHT(I$1;1);$H2));IF(C2=0;1/$M2;C2/$O2*($M2-$N2)/$M2);0)

Copy cell I2 to the cells J2, K2, and L2 (fill to the right)

Finally, copy cells C2 to O2 down your list of items, i.e. to row 10
(fill down)

Columns I to L should now hold the requested 2008 figures for all 4
agents.

Explanation of the helper columns:

- Column M holds the number of agents that are present in column G

- Column N holds the number of agents that are present in column G and
that has a zero contribution for year 2007, i.e. the number of 0% in
columns C to F.

- Column O holds the sum of the 2007 percentages for the agents that
are present in column G.

Hope this helps / Lars-Åke