2

I have a column of scores. Assume it is column B. How would I go about tracking the most consecutive 100+ point streak within the column?

  • Are you expecting formula-based or VBA? – Vylix Jun 12 '17 at 6:27
  • Can you provide an example with what you expect as if it was working? Doesn't have to actually be 100 rows. – LPChip Jun 12 '17 at 8:11
3

The easiest way to do this would be to use column C as a 'helper' column. Assuming your actual data starts in B3, I'd use the following:

C3 =IF(B3>=100,1,0)
C4 =IF(B4>=100,C3+1,0)

Then depending on your layout, I'd put a total at the top or bottom of column C with the following:

=MAX(C3:C10000)

If you use a similar approach for each of your scoring columns, you can then find out which one has the longest streak. You could optionally hide these helper columns once they're working.

  • Thinking hard, but never came to this conclusion. Simple, but beautiful, thanks for posting this answer! – Vylix Jun 12 '17 at 10:08
  • No problem. For what it's worth, a VBA solution would run a loop for each value in the range with the same logic as above, swapping out the third step for storing the current 'streak' if it's larger than the last recorded 'streak' for the column. – Steve Taylor Jun 12 '17 at 10:31
  • I was hoping that I wouldn't have to use a helper column, but it's simpler and it works. Plus, I just make the text white so the columns look normal. – btrballin Jun 15 '17 at 5:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.