Add value to another cell until total value reached

Question

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

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 :)


Show source
| excel-vba   | vba   | excel   | excel-formula   2017-01-06 09:01 1 Answers

Answers ( 1 )

  1. 2017-01-06 10:01

    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:

    =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;"")  
    

    enter image description here

◀ Go back