Help with formula to find consecutive occurrences/duration of a situation(stockout)

[aaronmaize3]

I hope someone is able to help - really been going in circles on this one trying to figure out what to do. To give some background on my situation:

I have 182 days of data for 500+ stores, and am tasked with finding of those 182 days, how many times inventory at the store level fell below 10 units. I have done a simple SUMIF for each line to find the # of times this occurred, but I also am looking to find the average time that a store stays below that level. So for instance, store #50 has had 60 days where stock was below 10 units. Of those 60 days, there were "stockout" durations of 10, 15, 5, 12, and 18 for an average period of 12 days.

Is there a way/formula to get to where I am trying to go? Spent about 3 hours yesterday searching all through posts on here to no avail. I found a lot of information using FREQUENCY and the MAX functions but haven't had any luck getting to where i need to. Below is where i got(with the data from C41:GB41 for store #50

=MAX(FREQUENCY(IF(C41:GB41<10,ROW(C41:GB41)),IF(C41:GB41>10,ROW(C41:GB41))))

Thanks for any help that can be offered!!

[Tony Valko]

You need to use the COLUMN function instead of the ROW function. Also, you're completely excluding any counts of 10.

IF(C41:GB41<10
IF(C41:GB41>10

What if the count IS 10?

Maybe you meant:

IF(C41:GB41<10
IF(C41:GB41>=10

=MAX(FREQUENCY(IF(C41:GB41<10,COLUMN(C41:GB41)),IF(C41:GB41>=10,COLUMN(C41:GB41))))

Still array entered.

For the average, try this array formula**:

=AVERAGE(IF(FREQUENCY(IF(C41:GB41<10,COLUMN(C41:GB41)),IF(C41:GB41>=10,COLUMN(C41:GB41))),FREQUENCY(IF(C41:GB41<10,COLUMN(C41:GB41)),IF(C41:GB41>=10,COLUMN(C41:GB41)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[aaronmaize3]

Tony,

You are THE MAN. Thanks for the help on this - got me to exactly where I needed to be.

[Tony Valko]

For the average, try this array formula**:

=AVERAGE(IF(FREQUENCY(IF(C41:GB41<10,COLUMN(C41:GB41)),IF(C41:GB41>=10,COLUMN(C41:GB41))),FREQUENCY(IF(C41:GB41<10,COLUMN(C41:GB41)),IF(C41:GB41>=10,COLUMN(C41:GB41)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Improvement! This will only work in Excel 2007 and later.

=AVERAGE(IFERROR(1/(1/FREQUENCY(IF(C41:GB41<10,COLUMN(C41:GB41)),IF(C41:GB41>=10,COLUMN(C41:GB41)))),""))

Still array entered.

[aaronmaize3]

Awesome! Thanks again Tony - very helpful, and the improvement is definitely shorter/less complicated and should allow for easier manipulation

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[aaronmaize3]

I was looking for that - couldn't seem to find it. good to go now