In Excel, how do I add values (running total) until and ONLY until a value is reached or breached?

Question

Link to Google Sheet : https://docs.google.com/spreadsheets/d/1e6FWnOfwzSXr6DaDvdo7Ruw_4qy9C0YUmvJlEfoQLCU/edit?usp=sharing

What I need ? I have data in column A only. From A2 to A97. In B2, B3 etc etc, I want a formula that adds value in Column A from that particular Row and onwards until the value 100% is reached or breached. I found one formula (given below) for Count from elsewhere in Stackoverflow, but it works fine for certain rows and not others. ( this formula was suggested by the user @Jeeped )

=MAX(INDEX((SUBTOTAL(9,OFFSET(A2, 0,0,ROW($1:$99),1))<=100%)*ROW($1:$99),,))+1
( if i want to copy this formula down, in all the subsequent rows, will it work ? ).
Please help me with where I am going wrong. Thanks in advance !

 100% ( should be reached or breached; then give output - Sum as well
   as Count )

   30%          8   Takes This and NEXT 7 rows to reach or breach 100%
   -65%         7   Takes This and NEXT 6 rows to reach or breach 100% 30%          6    10%            5    10%            4   
   -27%         3    44%            2    198%           1   This is correct 250%            5   This is wrong. 240% is already greater than 100%. So the result should be "1".
   -65%         11  
   -28%         8   
   -94%         6    125%           5   This is wrong. 128% is already greater than 100%. So the result should be "1".
   -18%         5    75%            3   
   -99%         5    135%           1    44%            3   
   -8%          2    250%           1   
   -19%         18  This is wrong. It should be 5.
   -78%         17   116%           16  
   -44%         15   183%           14  This is wrong 28%           17   4%         16  
   -84%         15   40%            14  
   -7%          13  
   -96%         12  
   -53%         9    163%           6   Wrong
   -81%         9   
   -30%         6   
   -31%         5   
   -65%         2    237%           1   
   -57%         4    164%           3   Wrong
   -87%         4    116%           1    62%            2    173%           30   53%            41   39%            100 Why 100 from here on ?
   -6%          100 
   -51%         100  138%           38  
   -14%         100  259%           100 
   -51%         100  44%            100 
   -94%         100 
   -85%         100 
   -25%         100 
   -10%         100  104%           100  76%            100 
   -61%         100  86%            100  87%            100 
   -14%         100 
   -68%         100 
   -95%         100 
   -100%            100 
   -94%         100  75%            100 
   -78%         100 
   -92%         100 
   -78%         15  This should be 4
   -78%         12   150%           1    150%           12

Answer 1

It seems that the formula you already have is very over complicated. If I understand your question correctly you want the totals in column B to continually increment until they hit 100% (or 1) then they just keep returning 1 thereafter.

This is simple to do. You simply put the calculation in an if statement with the condition is equal to or greater than 1 (because 1 == 100%) if the condition is true then return 1 otherwise return the result of the calculation.

There is an example sheet here (The formula in Google Sheets is the same as Excel in this case)

Otherwise in B2 you just need to take the value of A2 (as there is nothing to add it to at this point) Then you put the following into B3 and copy it down:

=if(A3+B2 >=1, 1, A3+B2)

However a word of caution to the wise:

If you ever have negative values then this is not a good idea. Lets say you add 50% + 50% + 50% = 150% but in your case this will just = 100%. If we now add -25% the total is 75% instead of 125%

So just to be crystal clear, you should only use the above formula if you are certain that all the data in column A is going to be positive.