将值添加到另一个单元格，直到达到总值

I need an excel formula or VBA to solve below problem. So I need you guys to help me please:)

我需要一个excel公式或VBA来解决下面的问题。所以我需要你们帮助我:)

My problem is :

我的问题是:

I have a table as blow with given total request and values. What I need is generate new value and the total of those values should give me closest total to "Total Request"

给定总请求和值,我有一个表。我需要的是生成新值,这些值的总和应该给我最接近“总请求”的总数

So the generation of new values should stop when it reach >= Total request.

因此,当新值达到> =总请求时,应停止生成新值。

Given Table :

给定表:

Total Request   35

Value   New Value   
1       
2       
3       
14      
21      
12      

Requested Table

Total Request   35

Value   New Value   
1       1
2       2
3       3
14      14
21      
12      

Thanks in advance :)

提前致谢 :)

1 个解决方案

#1

IF SUM in expanding range

Presuming you have Value column as column A, you can use this formula and copy down. It should stop on sum=20:

假设您将值列作为列A,则可以使用此公式并向下复制。它应该在sum = 20时停止:

=IF(SUM($A$5:A5)<Total_Request;SUM($A$5:A5);"")  

Or if you want just the value as an answer:

或者,如果您只想将值作为答案:

=IF(SUM($A$5:A5)<Total_Request;A5;"")