Count the number of cells' values higher than X%

This solution uses three helper columns, which may be hidden for aesthetic purposes. The first helper column gives the number of columns that needs to be added to the address of the first cell in 'Last Group' for that row. The array entered formula* that does this is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
The second helper column uses the following formula to find the first cell of the range whose values are to be compared:

Formula:

Please Login or Register  to view this content.

The third helper column uses the following formula to find the last cell of the range whose values are to be compared:

Formula:

Please Login or Register  to view this content.

Column N is then populated with the formula:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

@Leslie

Please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

Please attach the workbook so that your attempt to use the formulae can be looked at. A screenshot tells us very little.

It's exactly the same as it was when you attached a file to your opening post.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

The {1,2,3,4} counts the four cells in 'Last Group'. When you run Evaluate Formula in any cell P2:P12 you see that the last four cells in the five cell range are compared to the first four, 'First Group'.
I am not seeing an error with the zeroes being displayed in N5:N12 based on the directions given in the picture attached to post #1. Even though the formula in column P identifies a change of more than 4% in each of those rows, there are no subsequent cells with a larger value, as was the case with the alpha row.
Let me know if you have any questions.

Originally Posted by JeteMc

The {1,2,3,4} counts the four cells in 'Last Group'...

@JeteMc I should have been more clear. Excel file, which I uploaded in the recent reply, when updated with higher value (next to the percentage calculated cells values) the right count value does not get shown.
I mean, please update cells next to percentage calculated cells with higher numbers (randomly). Right count not shown.
May I kindly request to look-in (I may be wrong somewhere, please correct me).

Originally Posted by LesliePrabakar

...Note: 5 cells is a range where the range is dynamic (for instance 10, 12, 15, 20..)...

@JeteMc If 5 is the range then no need to change the formula as its already {1,2,3,4}. What if the range is 12, 20..? Can this {1,2,3,4} be automatically formula written..

Help appreciated.

As to point number one, looking at the gamma row there are two places, within the 'Last Group', where the percent increase is greater than 4%. Since the first of these is highlighted I assume that the count should commence from that point. To that end exchange the function SUMPRODUCT with SMALL.
As to point number two, if the range is changed then there will need to be a change in the OFFSET based formula that names the range as 'Last Group'. I would suggest doing both by putting a number in a cell, say T1, and referencing that cell in both formulas. The updated, now array entered, formula* to populate column Q and feed the range on to the COUNTIFS formula in column N is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
I am going to leave it to you to test with other sized ranges.
Let me know if you have any questions.

@JeteMc
Thanks so much for your response.

I still see the constant number (5) from the below formulas.

Can you please look-in and help (like did in the above reply for adding-up a cell for feeding range):

Name: FirstGroup (Name Manager)
Refers to: =OFFSET('% CALC WITH CONDITION'!$A2,,COUNT('% CALC WITH CONDITION'!$B2:$M2)-1,,-5+1)


Name: LastGroup (Name Manager)
Refers to: =OFFSET('% CALC WITH CONDITION'!$A2,,COUNT('% CALC WITH CONDITION'!$B2:$M2),,-5+1)

First Group: =OFFSET('% CALC WITH CONDITION'!$A2,,COUNT('% CALC WITH CONDITION'!$B2:$M2)-1,,-'% CALC WITH CONDITION'!$T$1)
Last Group: =OFFSET('% CALC WITH CONDITION'!$A2,,COUNT('% CALC WITH CONDITION'!$B2:$M2),,-'% CALC WITH CONDITION'!$T$1)
Let me know if you have any questions.