Solver or Goal Seek?

thelwellj6

New Member
Joined
May 16, 2018
Messages
1
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.

[TABLE="width: 727"]
<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>[TR]
[TD="colspan: 4, align: center"]%age of Repairs[/TD]
[TD][/TD]
[TD="colspan: 4, align: center"]Total of Bicycles & Motorbike Repairs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]John[/TD]
[TD]Peter[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD] [/TD]
[TD]John[/TD]
[TD]Peter[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]4%[/TD]
[TD]14%[/TD]
[TD]38%[/TD]
[TD][/TD]
[TD]Product A[/TD]
[TD]12[/TD]
[TD]43[/TD]
[TD]37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]20%[/TD]
[TD]48%[/TD]
[TD]26%[/TD]
[TD][/TD]
[TD]Product B[/TD]
[TD]65[/TD]
[TD]150[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]49%[/TD]
[TD]24%[/TD]
[TD]9%[/TD]
[TD][/TD]
[TD]Product C[/TD]
[TD]156[/TD]
[TD]75[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD]27%[/TD]
[TD]14%[/TD]
[TD]28%[/TD]
[TD][/TD]
[TD]Product D[/TD]
[TD]87[/TD]
[TD]44[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bicycles[/TD]
[TD]Total[/TD]
[TD]John[/TD]
[TD]Peter[/TD]
[TD]Mark[/TD]
[TD][/TD]
[TD]Motorbikes[/TD]
[TD]Total[/TD]
[TD]John[/TD]
[TD]Peter[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]34[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Product A[/TD]
[TD]58[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]120[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Product B[/TD]
[TD]120[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]119[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Product C[/TD]
[TD]121[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD]78[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Product D[/TD]
[TD]80[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
ABCDEFGHIJK
1Proportion of RepairsTotal of Bicycles & Motorbike Repairs
2JohnPeterMarkJohnPeterMarkTotal
3Product A4%14%38%Product A12433792
4Product B20%48%26%Product B6515025240
5Product C49%24%9%Product C156759240
6Product D27%14%28%Product D874427158
732031298730
8
9BicyclesTotalJohnPeterMarkMotorbikesTotalJohnPeterMark
10Product A344.4315.8913.67Product A587.5727.1123.33
11Product B12032.5075.0012.50Product B12032.5075.0012.50
12Product C11977.3537.194.46Product C12178.6537.814.54
13Product D7842.9521.7213.33Product D8044.0522.2813.67
14Total351157.23149.8043.97Total379162.77162.2054.03
Sheet68
Cell Formulas
RangeFormula
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top