Note to readers: this post has been updated due to the inclusion – at the request of Torstein – of a further version of this solution, in which the number of values to be considered is dynamic and so may be set by the user. This version may be found at the very end of this post.
This post, inspired by a question from Patrick MacKay, from Belgium – thanks, Patrick! 🙂 – is a (rather belated) follow-up to that which I made here, in which, to recap, I presented a formula-based set-up which, given a target figure plus a series of values, determined which, if any, combination of those values had a sum equal to the target.
The only slight drawback to that solution was the caveat that, if more than one combination of values existed which satisfied that condition, then only one of those combinations was given.
Here I would like to improve upon that set-up by presenting a refined version which will return all such combinations. What’s more, at the very end of this deconstruction I will give a further version of the solution in which the number of values to be considered is a variable which may be set by the user.
In fact, that early post was also one of the very few in which I did not give an explanation as to how the solution works, which I would like to do here.
As an example of the output, imagine that our target value – £1054.35, for example – is here in A1, and that we have a list of 10 values in A2:A11, as below: