consecutive number counting formula

**excelproof** · 10-09-2009, 06:23 PM

In cells v43:v52 I have data which will change often the data is always either a 1,-1 or a 0, I need a way to count from the bottom up and ignore anything that is not part of the chain. It can also contain 0 in rows that are not populated yet but the 0s will always be on the bottom and never in the middle or top.

I.E. in 3 weeks it could be

1
1
1
0
0
0
0
0
0
0
0
0

I would want the formula to spit out 3 because it was 1 3 times in a row..

Now say in week 5 it is
1
1
1
-1
-1
0
0
0
0
0
0
0

I would want it to spit out -2 because of the 2 "-1" in a row

Now say we get to week 11
1
1
1
-1
-1
1
-1
-1
1
1
-1
0

I would want it to spit out "-1" because there was only 1 week with -1 in a row before it went back to 1.

I need a pro for this one I think.

**NBVC** · 10-09-2009, 09:44 PM

Try:

Please Login or Register  to view this content.

Note: Your sample size would end at V54 if it starts at V43...

adjust if necessary and then confirm formula with CTRL+SHIFT+ENTER not just ENTER.

**excelproof** · 10-09-2009, 10:19 PM

Thank you for helping, I entered your formula and used CTRL SHIFT ENTER

I am getting a div/0 error..

**NBVC** · 10-09-2009, 10:37 PM

Does this give the error too?

Please Login or Register  to view this content.

also, in your jpeg... i don't see { } brackets around the formula.... Did you confirm with CTRL+SHIFT+ENTER so that they do appear?

**excelproof** · 10-09-2009, 11:09 PM

New formula works perfectly, thank you very much. Will click the scale and mark it as completed.

**excelproof** · 10-09-2009, 11:10 PM

Also I did use the CTRL SHIFT ENTER in the previous, when you click in the cell to edit it it takes away the {}..

**excelproof** · 10-09-2009, 11:31 PM

Is there anyway to make this formula look at the just the last 3 numbers in the series of 1, and -1s? So the max streak would be +3 and the least -3?

**excelproof** · 10-09-2009, 11:33 PM

Actually nevermind on that, I can filter it in another way. Thanks again for the help.

**Rennier** · 02-16-2011, 05:52 AM

Originally Posted by excelproof

Actually nevermind on that, I can filter it in another way. Thanks again for the help.

Hi there Excelproof!

I don't know if you are still at this address but here's trying.

Could you please forward me your spreadsheet and maybe just an explanation on how you use/d it? I have been tasked to look at absenteeism and using the Bradford Index although we are looking at a period of 26 weeks in which the absenteeism takes place and it seems what you did will help me a great deal.

Thanking you in advance,
Rennier
South Africa