Cumulative Sum ARRAY Formula
Posted by Somkiat on May 07, 2001 10:49 PM
I have these values in Row 1 : one number in each cell
from : A1 B1 C1 D1 E1
value : 10 20 30 40 50
How could I use an array formula to generate an array of cumulative sum to be {10,30,60,100,150}?
Thank you very much,
Somkiat Foongkiat
Posted by Kevin James on May 07, 2001 11:13 PM
Hello Somkiat:
You don't need an array.
B2 formula: =sum(A$1:B1)
C2 formula: =sum(A$1:C1)
D2 formula: =sum(A$1:D1)
as so on.
Hope this helps.
Kevin
Posted by Somkiat on May 08, 2001 6:29 AM
Really need ARRAY Formula
I would like to get an array to accumulate numbers in production plannning problem.
Let see this real problem with its context:
Everday there are over 40000 items produced. If I have to use 3 cells to accumulate each production, then I have to insert more 3*40000 = 12000 cells. File will be too big.
So I need only one formula for each stock in order to minimize more new cells as few as possible.
Suppose product A has 35 units in stock.
Followings are ordering quantity of product A:
Day 1 : 10
Day 2 : 20
Day 3 : 2
Day 4 : 10
Day 5 to 31 : also has its ordering amounts.
Since product A has 35 units, so it will take 4 days to be out-of-stock. (10+20+2+10 > 35)
I need to answer that it will take 4 days and this formula should be in only one cell.
I beleive that I need to get an array of cumulated stock ordering.
My last formula to handle this problem was very mega:
=IF(K6>G6, 1, IF(SUM(K6:L6)>G6, 2, IF(SUM(K6:M6)>G6, 3, IF(SUM(K6:N6)>G6, 4, IF(SUM(K6:O6)>G6, 5, IF(SUM(K6:P6)>G6, 6, IF(SUM(K6:Q6)>G6, 7, 0)))))))
+IF(SUM(K6:Q6)>G6, 0, IF(SUM(K6:R6)>G6, 8, IF(SUM(K6:S6)>G6, 9, IF(SUM(K6:T6)>G6, 10, IF(SUM(K6:U6)>G6, 11, IF(SUM(K6:V6)>G6, 12, IF(SUM(K6:W6)>G6, 13, 0)))))))
+IF(SUM(K6:W6)>G6, 0, IF(SUM(K6:X6)>G6, 14, IF(SUM(K6:Y6)>G6, 15, IF(SUM(K6:Z6)>G6, 16, IF(SUM(K6:AA6)>G6, 17, IF(SUM(K6:AB6)>G6, 18, IF(SUM(K6:AC6)>G6, 19, 0)))))))
+IF(SUM(K6:AC6)>G6, 0, IF(SUM(K6:AD6)>G6, 20, IF(SUM(K6:AE6)>G6, 21, IF(SUM(K6:AF6)>G6, 22, IF(SUM(K6:AG6)>G6, 23, IF(SUM(K6:AH6)>G6, 24, IF(SUM(K6:AI6)>G6, 25, 0)))))))
+IF(SUM(K6:AI6)>G6, 0, IF(SUM(K6:AJ6)>G6, 26, IF(SUM(K6:AK6)>G6, 27, IF(SUM(K6:AL6)>G6, 28, IF(SUM(K6:AM6)>G6, 29, IF(SUM(K6:AN6)>G6, 30, IF(SUM(K6:AO6)>G6, 31, 0)))))))
G6 is Stock
K6:AO6 are 31 days ordering amounts
Thanks again for your help,
Somkiat
Posted by Kevin James on May 08, 2001 7:37 AM
My apologizes
I figured your example was simplified. It does help to see the bigger picture. I'm sorry but I haven't the time to spend on this.
Posted by Sean on May 08, 2001 7:46 AM
Re: Really need ARRAY Formula
I would suggest that you could do nicely by going into a user defined function (VB):
Enter VB(Tools / Macro / Visual Basic Editor)
Paste the following Macro into the module:
Function Out_Of_Stock(qty, rng)
x = 1
For Each Value In rng
qty = qty - Value
If qty < 0 Then
Out_Of_Stock = x
Exit For
End If
x = x + 1
Next
End Function
Next in Excel enter your function as:
= Out_of_Stock(Qty,Range)
Where Qty is your current order quantity
and Range is the range of cells containing your order quantities.
Hope this helps
Sean
s-o-s@lineone.net
Posted by Somkiat on May 09, 2001 2:54 AM
:-)
Thank you very much. Please help me find an array formula for this problem when you are free. VBA is another choice but I need an array so much.
Posted by Somkiat on May 09, 2001 2:57 AM
Re: Really need ARRAY Formula
Thank you very much. I would suggest that you could do nicely by going into a user defined function (VB): Enter VB(Tools / Macro / Visual Basic Editor) Paste the following Macro into the module: Function Out_Of_Stock(qty, rng)
: Hello Somkiat
Posted by Aladin Akyurek on May 12, 2001 1:58 AM
