At which item in a series, total becomes greater than a number

[Shantanu Somani]

Hello,

This is my first post ever on any forum ... pardon me for the long title.

Problem: I have Opening stock & forward monthly demands listed in a row. I have to find out two things - until which month am I covering demand and what is stock in terms of days. Refer uploaded sheet for an example where I have manually worked out the number and the month. Can we write single formulae to get these two details.

I have listed here only 7-8 months of demand. Sometimes this runs to 14 months or so - Nested if may not solve a problem and is very cumbersome to write as a formula.

Regards,
Shantanu

 

[XOR LX]

Hi.

In your example, if you're covered until M4, why does your Days of Cover total include the figure for M5?

Regards

 

[Shantanu Somani]

Hi.

In your example, if you're covered until M4, why does your Days of Cover total include the figure for M5?

Regards

I am covered until M4 fully and partially for M5. So for until when it says M4 while in days calculation it calculates how many days in M5 are covered. Coverage works out to 120 days [4 months] + 24 days = 144 days

 

[XOR LX]

So you mean that the entries in the Days Covered row are also to be derived by extrapolation based on the values in row 2?

And you are assuming a default 30-day month system?

Regards

 

[prasaddn]

Hi,
Assuming you have 30 days in a month, i have made use of helper rows to achieve your requirements. See if it helps.
The formula are very long.. and sure you can work it out to make short. And i have also used different formula for first column and for rest of columns same in two helper rows. So totally 4 formulas
Regards,
Prasad DN

 

[Hui]

Shantanu

Firstly, Welcome to the Chandoo.org Forums

Without helper rows

I2: =INDEX(B1:F1,MATCH(A2,MMULT(--(TRANSPOSE(COLUMN(B2:F2))>=COLUMN(B2:F2)),TRANSPOSE(B2:F2)),1)) Ctrl+Shift+Enter

H2: =SUMPRODUCT(30*(B1:G1<=I2))+(A2-SUMPRODUCT((B2:G2)*(B1:G1<=I2)))/INDEX(B2:G2,MATCH(I2,B1:G1,0)+1)*30 Enter

See attached file:

 

[NARAYANK991]

Hi Hui ,

Can you please check your formula for H2 , for values in A2 starting from 0 and going up to 260 ?

Narayan

 

[NARAYANK991]

Hi ,

This is a very lengthy formula for H2 , but I think it covers all cases.

=MIN(30 * COLUMNS($B$1:$G$1),IFERROR(30 * MATCH($I$4,$B$1:$G$1,0) + 30*($A$2 - SUMIF($B$1:$G$1,"<=" & $I$4,$B$2:$G$2))/INDEX($B$2:$G$2,MATCH($I$4,$B$1:$G$1,0)+(MATCH($I$4,$B$1:$G$1,0)<COLUMNS($B$1:$G$1))),$A$2 * 30/$B$2))

Narayan

 

[prasaddn]

Shantanu

Firstly, Welcome to the Chandoo.org Forums

Without helper rows

I2: =INDEX(B1:F1,MATCH(A2,MMULT(--(TRANSPOSE(COLUMN(B2:F2))>=COLUMN(B2:F2)),TRANSPOSE(B2:F2)),1)) Ctrl+Shift+Enter

H2: =SUMPRODUCT(30*(B1:G1<=I2))+(A2-SUMPRODUCT((B2:G2)*(B1:G1<=I2)))/INDEX(B2:G2,MATCH(I2,B1:G1,0)+1)*30 Enter

See attached file:

Hi

if the stock is less than first month demand, it shows N/A instead of value. Like if stock is 50, it should show 15 days, for the given situation.

Regards,
Prasad DN

 

[prasaddn]

Hi ,

This is a very lengthy formula for H2 , but I think it covers all cases.

=MIN(30 * COLUMNS($B$1:$G$1),IFERROR(30 * MATCH($I$4,$B$1:$G$1,0) + 30*($A$2 - SUMIF($B$1:$G$1,"<=" & $I$4,$B$2:$G$2))/INDEX($B$2:$G$2,MATCH($I$4,$B$1:$G$1,0)+(MATCH($I$4,$B$1:$G$1,0)<COLUMNS($B$1:$G$1))),$A$2 * 30/$B$2))

Narayan

Hi

Pls help me understand why you have formula referring to $I$4 and also when I apply the formula with 500 in stock, it shows 150 days cover, while it should be 144!

Regards,
Prasad DN
Edit: $I$4 to $I$2 resolves the issue.