Sum until function

[Andre Pires]

Dear all,

I would like an excel formula to provide me how many rows until a certain sum is reached:

For instance:

here you'll see that I have 383 in stock and I would like to know how many week until I deplete the stock.

According to the calculus it should cover until W51 (where the final outcome by excel should be 6). Notice also that the opening stock might not sum exactly the value (this would be it would be between 4 or 5 weeks)

In general it would a function Sum until x value - but such function doesn't exist.

Can you help me with this?

Regards

 

[sunilbsrv2k]

This will calculate the difference between a cell in column M and the cells in 4th row from column Q onwards(since you have told it can be from 4th week onwards). If its less than 5, the count of the cell from column "N" will be displayed in N6. You can customize as per your needs.

The formula is like: =IF((M11-Q9)<5,COUNT(N9:Q9),IF((M11-R9)<5,COUNT(N9:R9),IF((M11-S9)<5,COUNT(N9:S9),COUNT(N9:T9))))

 

[Roger Govier]

Hi
Assuming your weekly totals are in cells B3 through H3 and your Opening stock is in B4
then the following array formula entered or amended by pressing Control+Shift+Enter (CSE) and not just Enter will give the result

{=MIN(IF(SUBTOTAL(9,OFFSET($B$3,0,0,1,COLUMN($B$3:$H$3)-COLUMN(B3)+1))>=B4,COLUMN($B$3:$H$3)-COLUMN(B3)+1))}

Do not type the curly braces { } yourself, Excel will enter thn when you use CSE

 

[Andre Pires]

Hey Roger,

It's working than

Hi
Assuming your weekly totals are in cells B3 through H3 and your Opening stock is in B4
then the following array formula entered or amended by pressing Control+Shift+Enter (CSE) and not just Enter will give the result

{=MIN(IF(SUBTOTAL(9,OFFSET($B$3,0,0,1,COLUMN($B$3:$H$3)-COLUMN(B3)+1))>=B4,COLUMN($B$3:$H$3)-COLUMN(B3)+1))}

Do not type the curly braces { } yourself, Excel will enter thn when you use CSE

hey Roger,

It's working perfectly with that formula you just provided me.

Thank you so much for that.

If you ever come to Portugal I'll be more than happy to pay you a beer ahah

Cheers