I want to count the maximum consecutive occurrences inside a range.
The formula i use now can only search in one column or one row.
I want to count the maximum consecutive occurrences inside a range.
The formula i use now can only search in one column or one row.
Do the columns carry over to the next column? For instance, would a "1" in C19 and a "1" in D4 count as consecutive entries? If the answer is yes, then the formula below appears to be working for me. It should be array entered with Ctrl + Shift + Enter:
=MAX(FREQUENCY(IF(C4:L19=O3,ROW(C4:L19)+16*(COLUMN(C4:L19)-3)),IF(C4:L19<>O3,ROW(C4:L19)+16*(COLUMN(C4:L19)-3))))
If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".
If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
https://www.excelforum.com/the-water...ke-thread.html
Well no, but that should not be a big problem, but if i search for "3" instead of 7 which should be the correct result i get a 3
Last edited by thusarix; 04-20-2017 at 05:32 PM.
Maybe I'm misunderstanding the question, then. I only see 3 consecutive threes. Where do you see 7 consecutive threes?
Here,
Your seems to have an extra "3" in D7
Ah! There are definitely a few differences between the data in my attachment in post #2 and your initial sample. I changed several boxes to test out whether my formula was working properly or not, and I'm not surprised that I didn't switch them all back correctly. I apologize if that created confusion. My intent was that you would try using the formula I came up with in your actual data. Does the formula produce the answer you were expecting when it's used on your actual data?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks