Planning solution, Excel's hidden powerful tools can easily engage in the calculation of the formula

Today we will understand how to solve the solution. This is a very powerful feature in Excel, but many people do not know that it can help us find the optimal solution to the problem. Today we will briefly understand

Want to learn from zero, here ↓↓↓

1. Call out planning solution

Planning and solution: In short, by changing other cell data to obtain a cell data of a given condition.

It has no default in Excel. We need to call it. First of all Load components], I will see the plan and solve the plan on the far right of the [Data] function group

Second, chicken and rabbit in the same cage

The most classic case of planning and solving is the problem of chicken and rabbits in the same cage. Let's use the equation to solve this problem.

Today, there are chickens and rabbits in the same cage, with thirty -five heads on the top,

There are ninety -four feet below, ask the chicken and rabbit geometry?

Through the method of the equation, the chicken can be set to X, the rabbit is set to y, and 2 equations can be obtained

X+y = 35

2X+4Y = 94

It can be obtained by solving the equations, chicken 23, rabbit 12, we can regard the equation as the constraint conditions in [planning solution], through this constraint condition to obtain the correct number

3. Case demonstration

1. Set formula

First of all, we need to set the conditions for the restrictions, as shown in the figure below

Enter formula in cell B2: = B8+B9, this means the total calculation

Enter the formula in cell B3: = B8*2+B9*4, this calculates the total number of feet

2. Setting constraints

Set the target value to cell B2, and set the target value to 35. The formula of the B2 cell is the total number.

Set the [Variable Unit] to B8: B9, which is the number of rabbits and chickens. It is unknown

There are 2 conditions, the first is: $ B $ 8: $ B $ 9 = integer. There is no doubt that the number cannot be decimal, the second is: $ b $ 3 = 94, and the cells of B3 are calculated. The number of feet needs to be equal to 94. This is a known condition

At this point, the conditions are set. Just click to solve the specific result. Finally, click OK.

The above is a simple way to use the solution. In short, through the value of constraints and control of the two cells of B8 and B9 to meet the results of the B2 cells.

In actual work, it can calculate the lowest fee, make up the number, etc. If you are interested, we can continue to share next time.

The above is all the content shared today. I am Excel from zero to one, follow me, and continue to share more Excel skills

- END -