How to find combinations whose sum is a certain value in Excel

This article brings you relevant knowledge about excel. It mainly introduces the problem of how to use functions to find combinations that sum to a certain value. Let’s take a look at it together. I hope it will help Everyone is helpful.

Related learning recommendations: excel tutorial

Today I will share with you how to use a function to find a combination that sums to a certain value.

Let me give you an example.

As shown in the figure below, columns A~B are data sources, column A is the invoice number, and column B is the invoice amount. Now you need to find the sum of the invoice combinations specified in cell F1, such as 20,089.

Regarding this issue, I shared with you the planning solution method a long time ago; today I will share with you the function method.

Enter the following formula in cell C2 and copy it to the C2:C21 range.

=IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))

After entering the formula, the system will prompt a circular reference. This is due to The formula refers to the value of the cell where the formula is located, but don't worry about it, no one cares about her.

Click the "File" → "Options" command in the upper left corner of Excel to open the File Options dialog box, switch to the "Formulas" tab, select the "Start iterative calculation" checkbox, and set the "Maximum number of iterations" ” is set to 30000. Close the dialog box with "OK".

#At this time, the formula in column C will be automatically recalculated, and the item with a non-0 recalculation result is the invoice combination. You can enter a SUM function formula in cell F2 for verification.

Let me explain to you the meaning of the formula.

=IF(SUM(C$2:C$21)=F$1,C2,B2*RANDBETWEEN(0,1))

The formula first determines C2:C21 Whether the sum of the area is equal to the target value specified in cell F1, if equal, return the value of C2 itself, otherwise, return the invoice amount in cell B2 multiplied by 0 or 1. When multiplied by 0, the result returns 0, indicating that the invoice amount is not selected. When multiplied by 1, the result returns the invoice amount itself, indicating that it is selected.

Because the formula refers to the value of the cell where the formula is located, such as the formula SUM(C$2:C$21) in cell C2, a circular reference will be triggered. At this point we enable iterative calculation, and the system will repeatedly calculate the formula until the condition for stopping iteration is established (the sum of C2:C21 is equal to the target value), or the number of iterations is exhausted.

That’s it.

Leave an exercise question at the end.

As shown in the picture above, the known sum 25 consists of 10 values. Each value is greater than or equal to 1 and less than or equal to 5. Please list these 10 values. any combination of.

Reference answer:

A3:A12 input formula

=IF(SUM(A$3:A$12)=B$1,A3,RANDBETWEEN(1, 5))

Related learning recommendations: excel tutorial

The above is the detailed content of How to find combinations whose sum is a certain value in Excel. For more information, please follow other related articles on the PHP Chinese website!