Check exact sum in a cell from a range

Asked

Viewed 345 times

1

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

It is possible without using vba, that I know exactly which cells are in this range that summed, result in the value of my highlighted cell?

Example:

inserir a descrição da imagem aqui

The yellow cells added together, result exactly in my highlighted cell.

excel

  • I think it is not possible, however it may be that someone explains better, now I am curious too. From what I understand you want to perform several sums of your range (column) and check if it matches your flagged column. That’s your problem?

  • Exactly, Claudio!

  • The problem is there are two numbers 23, so there are two answers. If not using a Solver (Linear Programming) would be enough to accomplish this with a simple model. However, with this, the model becomes more complex, and can become non-linear, but remains solvable without the use of Excel VBA. @Lucasaugusto there are some criteria for choosing the first or the second 23?

  • @danieltakeshi, I was curious about your solution proposal. Actually the image is totally illustrative, I ended up putting the number 23 twice without noticing. What the solution would be if there were only 1 number 23?

1 answer

2


Excel Solver

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

Enable Solver in Excel

The version used is Excel 2010.

  1. Enter the Office or File button.
  2. Click on Options.
  3. Click on Add-Ins.
  4. Install the Solver Add-in and click Ok.

Enable the Add-in

  1. Enable the Developer Mode
  2. On the tab Developer, click on Add-ins
  3. A window will open and the Solver Add-in must be marked.

Solver Add-in

Solution

  1. With the data provided in the example, cells A1:A10 were filled.

  2. The cell B11 is filled with the formula =SUMPRODUCT(A1:A10;B1:B10) or =SOMAPRODUTO(A1:A10;B1:B10)

  3. Open the Solver in Dice

  4. In Set Objective, select the cell B11. Janela Solver

  5. Choose the option Value of: and enter the desired sum number.

  6. In the option By Changing Variable Cells, choose the cells B1:B10.

  7. Click on Add.

  8. A window will open and cells B1:B10 should be chosen as the reference. And the option bin should be chosen. Click Ok. Add Constraint

  9. In the choice solution method Simplex LP.

  10. Click on Options and fill according to the image. Opções

  11. Click Solve and then Ok. Resultado Solver

Solver results

Resultados Solver

Filter

After the Solver solution, insert a filter for the number 1in the column B.

Upshot

With the filter we can conclude that lines 3 and 4 are the coefficients of the sum.

Resultado

Extra:

To change the optimization function, other forms of objective function or more constraints can be added. If you want to delve into the subject look for Linear Programming, Simplex Method or Solver. Or in English Linear Programming (LP), Simplex or Linear Optimization. A good tool for Excel optimization problems is the Solver Studio.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.