The construction I developed operates more on a left-to-right basis than on a smallest-number-of-values-which-makes-up-the-total basis: put these numbers in C1:G1: 4, 4, 4, 6, 6 and the formula will prefer the first set of three to the shorter set of two. If you put the 12 before the 2 and 10 in the list then it will indeed highlight the 12. Yes – see my point 3) above, since this is technically a case of there being more than one combination which meets the target, despite the (rather obvious) point that one of those “combinations” is in effect a single value. However, I would imagine that any more than that and Excel may begin to struggle, and so readers hoping for a solution with a dataset of such a size should probably seek a VBA-based solution insteadĢ) If no combination of the values exists for which the sum is equal to the target value then all cells remain blank.ģ) If more than one combination of values has a sum equal to the target value then one of those combinations is given (the actual reasoning behind which one is chosen is relatively complex, though again, if anyone is interested I would be happy to expound.)
C2:Q2, and it just about coped, taking 5 seconds or so to calculate. I ran it with a dataset comprising 15 values, i.e. However, it is more than likely that Excel will run out of resources in that case due to the sheer size of the calculations involved. The array formula in C2 is then: =IF(ISNUMBER(MATCH(COLUMNS($C:C),IF(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Range1))=$A$2,0),),TRANSPOSE(Arry1)),0)),"X","")ġ) This set-up has a theoretical limit on the size of the dataset of 20 values. Refers to: =ROW(INDIRECT("1:"&COLUMNS(Range1))) We then make two further additions to Name Manager, viz:
This solution will work for other ranges as well, of more or less than 9 cells, though readers should note that this solution is designed to work with a horizontal range of values: for the equivalent set-up using a vertical range, see the comments at the end of this post. In the example given, you can easily verify that 10+20+12+5 does indeed equal 47.įirst, as I often like to do, we will define the range of values, C1:K1, as Range1 using Name Manager ( Formulas tab). Using the above set-up, with our target value in A2 and our (in this case 9) values in C1:K1, we will place formulas in C2:K2 which will contain an “X” if the value in the row above forms part of our solution.
Combinations of numbers that sum to a given total excel for mac plus#
Sometimes we are in a situation where we have a target figure plus a series of values and we want to know which, if any, combination of those values has a sum which is equal to the target.Įdit: this post has now been revised here to account for multiple returns, should that be a requirement.
0 kommentar(er)
