# Maximum (highest) frequency of cells between two values in row

1. ## Maximum (highest) frequency of cells between two values in row

Hello,

This is my first post and English is not my native, so I hope I can explain my issue.
I have a spreadsheet that I want to calculate the maximum number of cells between two values. I did find a solution but it is not the smartest and requires a lot of formula typing.

Here's the issue (see example spreadsheet attached as well)

excel.PNG

I want to count what is the highest interval between two FG values - In the figure you can see it is 7 cells, between first and second "FG". I used max(frequency(if...) and it works, the problem is that I was counting blanks ("") in the beginning, but now any other value can be inserted between "FGs" in the same row, but I still want to count the maximum interval between FG, ignoring any other value in between.
In the spreadsheet attached you can see that I am using the same approach to account for different values, but I don't think it is clever to type multiple "IFs" to account for infinite values that can go between two "FG".

Any help is appreciated. If you do not understand the question, I may try to rephrase it for better understanding.

Thank you

2. ## Re: Maximum (highest) frequency of cells between two values in row

Put these values in these cells
Put this ARRAY FORMULA, completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER) in
Is that something you can work with?

3. ## Re: Maximum (highest) frequency of cells between two values in row

Ron, thank you very much for your solution, it's working, it is more elegant and allow for expansion of future codes.
I wish a very happy new year for all in this great forum.

Best regards,

Lito

4. ## Re: Maximum (highest) frequency of cells between two values in row

Glad you got something you can use!

There are currently 1 users browsing this thread. (0 members and 1 guests)