Check exact sum in a cell from a range

Navigation
1

I have a range in Excel with N values. I also have a cell with an x value.

Is it possible without using vba, that I know exactly which cells in this range that added up, result in the value of my highlighted cell?

Example:

The cells in yellow summed up exactly in my highlighted cell.

    
asked by anonymous 11.10.2017 / 23:41

1 answer

2

Excel Solver

To perform these tasks without using VBA, the solver needs to be used and simple binary logic is used.

Enable Solver in Excel

The version used is Excel 2010.

  • Enter the Office or File button.
  • Click Options .
  • Click Add-Ins .
  • Install the Solver Add-in and click Ok.

    • Enable Add-in

  • Enable Developer Mode
  • On the Developer tab, click Add-ins
  • A window will open and the Solver Add-in should be marked.

    • Solution

  • Withthedataprovidedintheexample,theA1:A10cellswerefilled.
  • ThecellB11ispopulatedwiththeformula=SUMPRODUCT(A1:A10;B1:B10)or=SOMAPRODUTO(A1:A10;B1:B10)
  • OpenSolverunderData

  • InSetObjective,selectthecellB11.

  • ChoosetheValueof:optionandenterthedesiredsumnumber.

  • IntheBychangingVariableCellsoption,choosethecellsB1:B10.

  • ClickAdd.

  • AwindowwillopenandB1:B10cellsshouldbechosenasareference.Andthebinoptionmustbechosen.ClickOk.

  • InthesolutionmethodchooseSimplexLP.

  • ClickOptionsandfillinasshown.

  • ClickSolveandthenOk.

    • SolverResults

    Filter

    Afterthesolversolution,enterafilterforthenumber1intheBcolumn.

    Result

    Withthefilterwecanconcludethatlines3and4arethecoefficientsofthesum.

    Extra:

    Tochangetheoptimizationfunction,otherformsofobjectivefunctionormoreconstraintscanbeadded.Ifyouwanttodelveintothesubject,lookforLinearProgramming,SimplexMethodorSolver.OrinEnglishLinearProgramming(LP),SimplexorLinearOptimization.AgoodtoolforExceloptimizationproblemsisthe Solver Studio .

        
    12.10.2017 / 20:16
    Open app google maps via link by ionic 3 Display data in PHP table