Counting Streaks

Umbaste

New Member
Joined
Feb 11, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello. I apologize in advance, since English isn't my first language, and there's a chance this question might have already been solved without me knowing the exact terms.

On to the problem. I'm doing analysis of variance on a set of data, and I'm stuck on a bit where I have a row of ten closely grouped values, either positive or negative (residuals, to be exact). What I need now is a formula so Excel can automatically count the number of streaks (as in, how many groups of uninterrupted sequences there are in the block of data). How could I do this?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Umbaste

New Member
Joined
Feb 11, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Sorry. Can't download xl2bb for some reason, let's see if I can't explain myself like this. Say the data line goes something like this:

1-2-1-122-1222

The first streak would be the first one; the second would start from the first negative two and through the two negative ones, the third would be the two twos, the fourth would be the negative one, and the fifth would be the final three twos. I need a formula for Excel to look at this row and give me the number of streaks.
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,799
Office Version
  1. 2016
So, moving from the left, if value change the sign( neg to pos or vice versa) then counts 1? Is that right?
 

Forum statistics

Threads
1,176,119
Messages
5,901,483
Members
434,896
Latest member
Derquila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top