Goal seek or Solver?

[Julie18]

Hi,

I am working on a problem that I just can't solve. It may be that I can solve with with good old IFs but I think what if analysis would work best. Please can you help.

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 produced 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 A to D) would each of them make?

I hope this makes sense and I would appreciate your help. I also uploaded a file to make it visual. The areas shaded in blue are the ones I need to populate with data.

 

[bosco_yip]

Doesn't use Solver or Goal Seek, just use Sum, Index & Match function and formulas as in below.

1] In "Bicycles table" D17, copied right to E17 and all copied down :

=ROUND($C17/SUM(INDEX($H$10:$J$13,MATCH($B17,$G$10:$G$13,0),))*INDEX($H$10:$J$13,MATCH($B17,$G$10:$G$13,0),MATCH(D$16,$H$9:$J$9,0)),0)

2] In "Bicycles table" F17, copied down :

=C17-D17-E17

3] In "Motorbikes table" J17, copied across right to L17 and all copied down :

=INDEX($H$10:$J$13,MATCH($B17,$G$10:$G$13,0),MATCH(D$16,$H$9:$J$9,0))-INDEX($D$17:$F$20,MATCH($H17,$B$17:$B$20,0),MATCH(J$16,$D$16:$F$16,0))

Regards
Bosco