Finding values to a given sum.

[hand6928]

I have a given numeric sum and a column of values that are not listed in sequential order. Is there a formula that will help me identify the individual values in the range that comprise the given sum?

 

[Eloise T]

Do you have a sample file? It makes helping you a lot easier.

 

[NARAYANK991]

Hi ,

This question has been asked quite a few times in this forum , and elsewhere.

The answers are always the same :

1. It is practically impossible to have a formula-based solution.

2. Even if you use VBA , you need to have constraints :

• How many entries in all will there be ? Will it be in hundreds , thousands or more ?
• How many individual values will need to be combined in order to arrive at the given sum ? Will it be 2 , 3 , 4 or more ?
• Is it possible that there will be multiple combinations for a given sum ? In such as case what should be done ?

If you can specify all these and anything else that you can think of , a feasible solution will be available , on the Internet ; one such piece of code is Tushar Mehta's , posted here :

http://www.tushar-mehta.com/excel/templates/match_values/

Narayan

 

[bines53]

Hello friends,

Narayan my friend ,i just made something challenging,

AND I gave an example,

http://chandoo.org/forum/threads/find-the-cumulative-amount.27926/

And there is another solution, the better,

David

 

[NARAYANK991]

Hi David ,

Sorry , but I am not able to understand your problem description.

Is it that if you are given a value 29 , you are required to find how many values add up to give this value ?

For example , the first 5 values A1:A5 add up to 29.

Can you clarify whether this is the problem ?

Can you also clarify whether the values need to be consecutive , or can be non-contiguous values ? For example , the values A1 , A4 , A5 , A7 and A8 also add up to give 29. Would this be a solution ?

Narayan

 

[bines53]

Hi Narayan,

I attach a file, with two examples, but I say there is another formula for better and wider, and it will be introduced on Thursday, may have more formulas beyond the two I mentioned, that's why I put the challenge.

Solutions are found in these cells, C1,H1.
According to this formula ,
MMULT(N(ROW(A1:A8)>=TRANSPOSE(ROW(A1:A8))),A1:A8).

David

 

[bosco_yip]

Try this formula system used 10 numbers as example :

1] K2 >> Data Validation >> Settings >>"Allow", choose : List ; "Source", enter : 254,170,150 (being the proposed given sum for selection in testing purpose)

2] A7, enter the array formula, copy across and down :

=IF(ROWS(A$1:A1)>A$6,"",INDEX($2:$2,--RIGHT(SMALL(ROUND(ABS($A$2:$J$2-$K$2/A$6),)*10^3+COLUMN($A:$J),ROWS(A$1:A1)),2)))

Confirm enter with SHIFT+CTRL+ENTER instead of just ENTER.

6] A16, enter the formula, copy across :

=SUM(A7:A15)

7] A17, enter the array formula, copy across :

=IF(ABS(A16-$K$2)=MIN(ABS($A$16:$H$16-$K$2)),"The best number set","")

Confirm enter with SHIFT+CTRL+ENTER instead of just ENTER.

8] The row 17 will appear the wording "The best number set"

Regards
Bosco

 

[bines53]

Hi Narayan,

I mean, sequential numbers, cells that have zero or empty, you mean the combination of numbers that will give the required amount, I will examine whether it is feasible, what you asked me.

David