Solver or Goal Seek?

[thelwellj6]

I am working on a problem that I just can't solve. It may be that I can solve with good old IFs but I want to see if Goal Seek or Solver would work better.

Basically, I have 2 tables. One table has John, Peter and Mark repairing bicycles. Bicycle A totals up to 34, B total is 120, C total is 119, D total is 78.

Table 2 has the same people repairing motorbikes. Type A total is 58, B is 120, C is 121 and D is 80.

The total overall productivity for Type A (across bicycles and motorbikes) is John = 12, Peter = 43, Mark = 37. Product B has John = 65, Peter = 150, Mark = 25. Product C has John = 156, Peter = 75 and Mark = 9 and Product D has John = 87, Peter = 44, Mark = 27.

My question is, how many bicycles and motorbikes (broken down by each product category A to D) would each of them make?

I hope this makes sense and I would appreciate your help. I have also posted a table below if this helps. Thanks so much in advance.

%age of Repairs		Total of Bicycles & Motorbike Repairs
	John	Peter	Mark			John	Peter	Mark
Product A	4%	14%	38%		Product A	12	43	37
Product B	20%	48%	26%		Product B	65	150	25
Product C	49%	24%	9%		Product C	156	75	9
Product D	27%	14%	28%		Product D	87	44	27
Bicycles	Total	John	Peter	Mark		Motorbikes	Total	John	Peter	Mark
Product A	34					Product A	58
Product B	120					Product B	120
Product C	119					Product C	121
Product D	78					Product D	80

<colgroup><col style="text-align: center;"><col span="5" style="text-align: center;"><col style="text-align: center;"><col span="4" style="text-align: center;"></colgroup><tbody>
</tbody>

 

[DRSteele]

Welcome to the forum.

Unless I'm missing something, all you need is to prorate the proportions.

Copy the formulas across and down as appropriate for each grid.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Proportion of Repairs						Total of Bicycles & Motorbike Repairs
2		John	Peter	Mark				John	Peter	Mark	Total
3	Product A	4%	14%	38%			Product A	12	43	37	92
4	Product B	20%	48%	26%			Product B	65	150	25	240
5	Product C	49%	24%	9%			Product C	156	75	9	240
6	Product D	27%	14%	28%			Product D	87	44	27	158
7								320	312	98	730
8
9	Bicycles	Total	John	Peter	Mark		Motorbikes	Total	John	Peter	Mark
10	Product A	34	4.43	15.89	13.67		Product A	58	7.57	27.11	23.33
11	Product B	120	32.50	75.00	12.50		Product B	120	32.50	75.00	12.50
12	Product C	119	77.35	37.19	4.46		Product C	121	78.65	37.81	4.54
13	Product D	78	42.95	21.72	13.33		Product D	80	44.05	22.28	13.67
14	Total	351	157.23	149.80	43.97		Total	379	162.77	162.20	54.03
Sheet68

Cell Formulas
Range	Formula
B3	=H3/H$7
B14	=SUM(B10:B13)
K3	=SUM(H3:J3)
H7	=SUM(H3:H6)
H14	=SUM(H10:H13)
C10	=H3/$K3*$B10
I10	=H3/$K3*$H10

 

[Joe4]

Cross-posted here: https://chandoo.org/forum/threads/goal-seek-or-solver.38561/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.