Special Countif Function that I cannot figure out

[Brad B]

Hello,

I have a worksheet for demand planning and forecasting that basically shows:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Stock[/TD]
[TD]Monday Demand[/TD]
[TD]Tuesday Demand[/TD]
[TD]Wed Demand[/TD]
[TD]Thurs Demand[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


I need to write a formula that will tell me how many days my stock will last (ie: how many days, after fulfilling that days demand, is the stock >= 0)

The Result I need the formula to return from the above example would be "2". (because Monday's demand + Tuesday's demand <=10, but Monday + Tuesday + Wed > 10, so on Wednesday we would be out of stock or stock would be negative)

I wish it were as simple as countif(b2:e2>a2), but I don't need to know how many cells individually are less than stock, I need to know how many cells it takes to add up to more than stock.

Any help would be greatly appreciated. By the way, I'm working in excel 2010, but it has to remain compatible with excel 97 for other users...

Thanks

 

[Snakehips]

Brad,

Does it help to consider using conditional formatting?

Maybe apply the custom formula as below to the range B2:E2 and indicate visually when there will be insufficient stock.

Excel Workbook
	A	B	C	D	E
1	Stock	Monday Demand	Tuesday Demand	Wed Demand	Thurs Demand
2	10	4	5	4	3
Sheet12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =SUM($B2:B2)>$A2	Abc
C2	1. / Formula is =SUM($B2:B2)>$A2	Abc
D2	1. / Formula is =SUM($B2:B2)>$A2	Abc
E2	1. / Formula is =SUM($B2:B2)>$A2	Abc

Otherwise you may could employ some helper columns, which you could hide, that would make the count task easy. That's always assuming you are not looking to monitor too many days / columns?

Hope that helps.

 

[AlanY]

or with a helper row?

Excel 2012
	A	B	C	D	E	F
1	Stock	Monday	Tuesday	Wednesday	Thursday	Days while last
2	15	4	5	4	3	3
3		4	9	13	16
Sheet3

Cell Formulas
Range	Formula
F2	=MATCH(A2,B3:E3)
B3	=SUM($B$2:B2)

 

[Brad B]

Hi AlanY and Snakehips,

Thank you for the replies, I should have specified that I am trying to find a way around using a helper row.

I already have a similar sheet that has a helper row, which shows what's left of our inventory on each day after demand (leftover stock in helper row decreases each day, eventually goes negative). We use conditional formatting on this sheet and it's great for visualization of which items are running low soonest.

I started out on this task trying to ref the sheet with helper rows and countif(value<0). It returns the desired result...However, I am trying to build a summary sheet that maintains the same rows as the demand data (ie 1 row per item) so that I can simply drag the formula down (hundreds of items) rather than referencing each item. There's a lot of different information in the two sheets, so in short, it's better if i could ref the sheet with one row per item, than 2 rows per item: a row and a helper row.

I am hoping to find a formula that would accomplish this, but can't seem to wrap my mind around it. Something like countif(sum($b2:e2)>a2) but obviously that syntax won't work, and I'm kind of stuck.

Otherwise, I will have to use helper rows and delete / filter / hide them once I get my formulas. This would also be a less flexible option, since it would require more work every time we add or remove items.

Again that you guys for your suggestions, and let me know if you have any other ideas!

 

[Eric W]

How about something like this?

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Stock[/TD]
[TD="bgcolor: #FFFFFF"]Monday[/TD]
[TD="bgcolor: #FFFFFF"]Tuesday[/TD]
[TD="bgcolor: #FFFFFF"]Wednesday[/TD]
[TD="bgcolor: #FFFFFF"]Thursday[/TD]
[TD="bgcolor: #FFFFFF"]Friday[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Stock will last[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]{=MAX(IF(SUBTOTAL(9,OFFSET(B2,0,0,1,ROW(INDIRECT("1:5"))))<=A2,ROW(INDIRECT("1:5"))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



That is an array formula, confirm with Control+Shift+Enter.

 

[Brad B]

Thank you Eric,

This is exactly what I was looking for. I have adapted it to my needs and it seems to be working perfectly.

If you wouldn't mind, could you help me understand the formula by breaking down what it's doing?

I am familiar with all the functions in it except for subtotal, and indirect, both of which I looked up, but still don't have a good grasp on. I am not super comfortable with array formulas either. I'd like to make sure I fully understand a formula before I incorporate it all through my work.

Thanks!

 

[Eric W]

Sure. What we're trying to do is calculate 5 sums: B2, B2:C2, B2:D2, B2:E2, and B2:F2. This give us an array of 5 values {4,9,13,16,18}. Since that's an array, we need to use an array function (also called a CSE function, for Control+Shift+Enter). To get the 5 ranges, I use the OFFSET function:

OFFSET(B2,0,0,1,1)
OFFSET(B2,0,0,1,2)
OFFSET(B2,0,0,1,3)
OFFSET(B2,0,0,1,4)
OFFSET(B2,0,0,1,5)

where the last parameter is the number of columns to include in the range. To get an array of {1,2,3,4,5} which I can then give to OFFSET to get those 5 ranges, I use a trick: 1:5 is a description of Rows 1 to 5. ROW(1:5) within an array formula returns {1,2,3,4,5}. And in fact, you can use ROW(1:5) within the formula without using the INDIRECT if you want. INDIRECT takes a text description of a range "1:5" and converts it into an actual range 1:5. The reason I use INDIRECT is that if you have ROW(1:5) in your formula, and you add a row between 1 and 5, Excel will helpfully adjust that part of the formula to ROW(1:6), which will break your formula. But Excel will not adjust text "1:5" within the formula. That's why INDIRECT is used.

And the reason I use SUBTOTAL instead of SUM is because, as a rule, you cannot put an OFFSET function within an array formula. But an exception to the rule is you can use OFFSET within SUBTOTAL within an array formula. SUBTOTAL has many options, but option 9 is just a SUM.

So with that background, here's how it works:

=MAX(IF(SUBTOTAL(9,OFFSET(B2,0,0,1,ROW(INDIRECT("1:5"))))<=A2,ROW(INDIRECT("1:5"))))

=MAX(IF(SUBTOTAL(9,OFFSET(B2,0,0,1,ROW(1:5)))<=A2,ROW(1:5)))

=MAX(IF(SUBTOTAL(9,OFFSET(B2,0,0,1,{1,2,3,4,5}))<=A2,{1,2,3,4,5}))

=MAX(IF(SUBTOTAL(9,{B2,B2:C2,B2:D2,B2:E2,B2:F2})<=A2,{1,2,3,4,5}))

=MAX(IF({4,9,13,16,18}<=A2,{1,2,3,4,5}))

=MAX(IF({4,9,13,16,18}<=15,{1,2,3,4,5}))

=MAX({1,2,3,FALSE,FALSE})

3


Hope this helps!

 

[Brad B]

Wow thanks for the break down, that was incredibly helpful.

So glad for this community. Makes my work much easier.

 

[Eric W]

You're welcome.

 

[Snakehips]

@ EricW

+1 Thanks for that insight.