1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Goal seek or Solver?

Discussion in 'Ask an Excel Question' started by Julie18, May 16, 2018 at 6:09 PM.

  1. Julie18

    Julie18 New Member

    Messages:
    2
    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.

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,644
    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

    Attached Files:

Share This Page