Back to Forms in Excel VBA archive index

Back to archive home

I have these values in Row 1 : one number in each cell

from : A1 B1 C1 D1 E1

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

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

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

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.

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

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.

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

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.

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.