0

I have thousands of rows in Excel. In six columns I have percentage values. For example: [68%][92%][72%][109%][55%][86%]

In these rows I would like to find the longest streak, from the RIGHT, of values that are below or higher than 100%

[68%][92%][72%][109%][55%][86%] Streak is 2 below 100% -> Below 2

[62%][101%][43%][65%][78%][111%] Streak is 1 above 100% -> Above 1

[89%][108%][55%][114%][111%][120%] Streak is 3 above 100% -> Above 3

[---][---][---][---][147%][154%] Streak is 2 above 100% -> Above 2

[---][---][---][---][---][45%] Streak is 1 below 100% -> Below 1

[116%][58%][89%][66%][33%][74%] Streak is 5 below 100% -> Below 5

Didn't quite find what I was looking for in "Calculate current streak in Excel row" Calculate current streak in Excel row

COUNTIF($KJ2341:$KO2341;"<1") This gives me the total number that are below 1 but I only look for streak from right.

Can you help please?

Lookingforexperts
  • 5
  • 3

1 Answers1

1

I assumed you want to group any that are exactly 100% with the above group. If that is not the case, please specifically state how you want that handled.

Here is one method, assuming the sixth column always has the defining entry:

Note that if your data starts in columns other than A, modification to the formula may be required

Edit: Formula changed to handle partially filled rows

=IF(F1>=1,"Above ","Below ") &  IFERROR(COLUMNS($A:$F)- LOOKUP(2,1/IF(F1>=1,A1:F1<1,A1:F1>=1),COLUMN($A:$F)),COUNT(A1:F1))

enter image description here

If the sixth column is not always the last entry, formula solution still possible; but easier if you have Office 365.

  • F1 > 1 determines if we are looking for a streak of aboves or belows
  • A1:F1>1 (or <=1) creates an array of appropriate {TRUE,FALSE} depending on meeting the condition.
  • 1/(the array) changes it to an array of {1,DIV/O}
  • LOOKUP(2,the_array,array of column_numbers) returns the column number matching the last TRUE in the original array we set up.
  • Then it's just simple subtraction, with an IFERROR to handle the situation where there is only a single entry in the streak.
  • If the formula returns an error, then it will be the case that all entries are the same (or blank); so we can just use a slightly different formula in the event of an error.
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Hi, Thanks for the support! Column starts in KJ so I added 295+ in order to get the correct values. That worked for all cases except when all 6 numbers were below or above 100% Also, when there was the case of empty cells it didn't get it right I'm afraid. IF(KO2340>=1;"Above ";"Below ") & 295+COLUMNS(KJ2340:KO2340)-IFERROR(LOOKUP(2;1/IF(KO2340>=1;KJ2340:KO2340<1;KJ2340:KO2340>=1);COLUMN($KJ2340:$KO2340));COLUMNS($KJ2340:$KO2340)-1) – Lookingforexperts Mar 04 '21 at 14:26
  • @Lookingforexperts Sorry about that. But your observations about "all 6" makes sense. I won't be able to get back to this until this evening or tomorrow AM. Out of town and away from a computer right now. – Ron Rosenfeld Mar 04 '21 at 15:48
  • @Lookingforexperts Formula edited and I think it should handle those situations correctly now. If it does not, please provide the data, the output from the formula, and the expected output. – Ron Rosenfeld Mar 04 '21 at 19:14
  • Was out of town myself :) It works! IF(KO2345>=1;"Above ";"Below ") & IFERROR(COLUMNS($KJ:$KO) -LOOKUP(2;1/IF(KO2345>=1;KJ2345:KO2345<1;KJ2345:KO2345>=1);COLUMN($KJ:$KO))+295;COUNT($KJ2345:$KO2345)) Me adding 295 due to not starting in A. Great help! – Lookingforexperts Mar 09 '21 at 09:57