Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: Cumulative Sum ARRAY Formula

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


Really need ARRAY Formula

Posted by Somkiat on May 08, 2001 6:29 AM
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


My apologizes

Posted by Kevin James on May 08, 2001 7:37 AM
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.

Re: Really need ARRAY Formula

Posted by Sean on May 08, 2001 7:46 AM
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.


Re: Really need ARRAY Formula

Posted by Somkiat on May 09, 2001 2:57 AM
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


Re: Cumulative Sum ARRAY Formula

Posted by Aladin Akyurek on May 12, 2001 1:58 AM
See

17245.html

Aladin

=====================



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.