Find cumulative score of consecutive cells over 100%

[zachwalkerpinto]

Hi there.

I am looking for a way to automatically add the number of consecutive cells over "100%" such that the total 'score' increases with each additional consecutive 100%. For example, in the attached sheet...

One 100%+ cell will yield 1 point. Two consecutive 100%+ will yield 3 points. (Because 1+2=3). If there were THREE consecutive 100%, this would yield 6 points (1+2+3).

However, see row 19 for an example of when this gets tricky. First, there are two consecutive 100% (1+2=3) but then there is another 100% non-consecutively. This would yield a final score of 4 points because (1+2 ... + 1 = 4).


If anyone has any VBA that can pull this off I will be forever grateful.

Thank you.

[oeldere]

If anyone has any VBA that can pull this off I will be forever grateful.

non VBA also acceptable?





Non question information.
Please also reply to the forummembers who offered you a solution on the earlier questions.
If you are satisfied with the solution, please mark the question solved.

[zachwalkerpinto]

Yes, sure of course!

[oeldere]

I re-arange the data with Power Querry.

After that

D2=IF(AND(A2=A1,C2>$J$1,C1>$J$1),2,IF(AND(A3=A2,C2>$J$1,C3>$J$1),0,IF(C2>$J$1,1," ")))

E2=VLOOKUP([@count],$I$3:$J$8,2,0)

After that a pivot table.



See the attached file.

[HansDouwe]

Here is a solution with Excel-formulae only:

Try in Cell J2 and copy down and right:

Please Login or Register  to view this content.

Try in Cell P2 and copy down

Please Login or Register  to view this content.

[protonLeah]

deleted,....