Count periods where threshold is exceeded

**dieter99** · 03-20-2009, 10:18 PM

Hi all
I am trying to find a way to identify the nmber of periods where a particular value is exceeded.

Basically I have daily data on sales for 40 years and I would like to define a level of sales i.e. 23 units per day and a period i.e. 10 days and then output the number of times where the recorded sales level is greater than 23 units per day for 10 or more days.

Ideally I would like it so the sales level and period can be set in reference cells and the formula can adapt to different sales levels and periods.

The data is formatted as follows:
Date number of sales
01/01/1990 15
02/01/1990 16
03/01/1990 12

If anyone has any idea how to do this I would be very grateful!

**Ron Coderre** · 03-20-2009, 10:36 PM

With
A2:A1000 containing consecutive dates
and
B2:B1000 containing units sold per day

...and...
E2 containing the consecutive days parameter...eg 10
F2 containing the units threshhold....eg 23

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER),returns the number of times there were
sales of more than 23 units for 10 or more consecutive days

Please Login or Register  to view this content.

Does that help?

**dieter99** · 03-25-2009, 12:52 AM

That's great, you're now my new favourite person!

Just to clarify, to compute the opposite i.e. the number of times there were
sales of less than 23 units for 10 or more consecutive days the array would be this

Please Login or Register  to view this content.

**Domenic** · 03-25-2009, 05:48 AM

That's correct. However, if the data can contain empty cells, the formula may need to be modified.

Hope this helps!

xl-central.com

**Ron Coderre** · 03-25-2009, 07:52 AM

Domenic is correct. Blank cells would need to be addressed.

Here's one option:
ARRAY FORMULA

Please Login or Register  to view this content.

**dieter99** · 03-26-2009, 10:18 PM

Originally Posted by Ron Coderre

Domenic is correct. Blank cells would need to be addressed.

Here's one option:
ARRAY FORMULA

Please Login or Register  to view this content.

Thanks for that addition, I did have missing data but I knew it was a linear progression between data values so used a vb script to interpolate between the values. This caused the answers from the formula I posted to be slightly wrong, but now I can use the irregular data it works perfectly.

Many thanks, I would never have figured this out in a million years!

**dieter99** · 03-31-2009, 11:44 PM

So I had the formula's working perfectly and a colleague asked me to look at a different data set so I pasted this in and now the array formula's now both return 1 under all conditions. I have not changed anything in the formula's, merely pasted in continuous (no blanks) data.

I cannot spot what has gone wrong. I've attached a sample from the data, to check the array, I set the time period to 1 and compare the answer to a simple countif formula that counts the number of exceedances of the threshold value.

Can anybody shed some light?!

**Ron Coderre** · 04-01-2009, 08:01 AM

Your sample data is in descending order, begins at 125.00 and ends at 75.22.
Both formulas are structured to return the number of times that
groups of consecutive values exceed a threshhold.

The H6 formula is counting the number of times the value is
greater than 121.6 for 1 or more days. In your data, only the
first 9 values are greater than 121.6. All other values are
less than or equal to 121.6. Consequently, you have 1 group
of consecutive values that satisfies the criteria.

The H9 formula is counting the number of times the value is
less than 77.5 for multiple days. In your data, only the
last 153 values are less than 77.5. That 1 group of consecutive
values satisfies the criteria.

Both formulas are returning what they are designed to return.
In these cases: 1.

If you copy B2 into B25...that will constitute another event
and H6 will return: 2

Does that help?