Cumulative Sum by a single formula & more (for Samkiat)

Samkiat,

Here is your sample data which occupy G6 (stock) to N6 from the thread at 16724.html:

{35,"","","",10,20,2,10,6}

The following formula produces an array of cumulative totals/sums. The formula itself is NOT array formula.

=SUBTOTAL(9,(INDIRECT("$k$6" & ":" & ADDRESS(ROW($K$6), ROW(INDIRECT("1:" & COUNT(K6:O6))) + 10))))

This is the array result:

{10;30;32;42;48}

The problem that you want to solve is as you stated:

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

The following array-formula will do what you want:

=MATCH(G6,SUBTOTAL(9,(INDIRECT(TRANSPOSE("$K$6" & ":" & ADDRESS(ROW($K$6), ROW(INDIRECT("1:" & COUNT(K6:O6))) + 10))))) ,1)+1

Explanation:

The first arg of SUBTOTAL which is 9 means "SUM".
The second arg of SUBTOTAL builds the ranges over which to sum. In this example you get:

{"$K$6:$K$6", "$K$6:$L$6", "$K$6:$M$6", "$K$6:$N$6", "$K$6:$O$6"}

Note 1. That is MATCH that requires this formula to be entered as an array-formula.

The last arg of match generates an approximate location of the cumulative order amount that is the closest to the stock amount. The last part of the formula, which is 1, together with the location number generates the number of days when you will be ("most likely") out of stock. The result will be correct for the range of 1 to 30 days. If the cumulative amount of orders in 31st day is less than the stock amount you'll get 32 as result.

Note 2. This formula, as you'd guess, replaces your megaformula constructed with IFs.

Aladin

PS. You need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) to anter an array-formula (I suppose you already knew this).