• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Sequential occurances of less than value in excel

I'm looking for a formula that will return the value of 4 in cell A2.  It answers the question:  What is the maximum number of times in sequence was the value of -10 exceeded in A4:A16?

ThanksExample.xlsx
0
ronadair
Asked:
ronadair
1 Solution
 
dani gammonProduct Operations ManagerCommented:
Hi there,

My first response would be to create a countif formula such as: =COUNTIF(A4:A16,">-10")

However, where is your sequence within a4:a16 ? because that would add to the formula to get an answer of 4.
0
 
Saqib Husain, SyedEngineerCommented:
=COUNTIF(A4:A16,">"&A1)

but for the given data the answer is 7
0
 
Naresh PatelTraderCommented:
Try this but i don't know why your answer is 4 mine is 6
=FREQUENCY(A4:A16,A1)

Thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
MacroShadowCommented:
Put this array formula (Press CTRL+SHIFT+ENTER) in A2:
=MAX(FREQUENCY(IF(A4:A16<=-10,ROW(A4:A16)),IF(A4:A16>-10,ROW(A4:A16))))

Open in new window

0
 
ronadairAuthor Commented:
Worked like a charm!
0
 
Naresh PatelTraderCommented:
Mr.MacroShadow,

will you pls explain the logic behind the  formula & why answer is 4?

Thanks You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now