Count consecutive number of negative occurrences >=3

Hi

I have a spreadsheet that contains details of in/outflows per person. I would like to be able to count the number of negative outflows over a period of 12months, from the most recent month. Therefore if a person has had negative outflow between Jun2012 - Sep2012, this will be counted as 4, as shown in the example below.


Person Oct-11 Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 >= 3 Mths Outflow
Person A 1,294,101.20 5,472.41 (1,575,727.58) 699,893.28 (53,695.29) (12,228.73) (231,106.23) (13,428.66) (6,805.91) 159,668.38 (2,336.02) 50,226.54 0
Person B 466,765.69 1,983.01 (87,853.52) 3,477,399.89 347,771.60 (281,291.59) 272,735.38 469,410.33 (187,702.23) (952,959.25) (241,283.10) (116,687.09) 4
Person C 1,925,027.17 (575,807.64) 943,586.40 (88,957.00) 320,904.93 941,596.66 987,052.22 1,739,969.52 705,568.76 156,407.82 (325,662.34) (3,429,927.95) 0
Person D (1,487,162.98) (3,508,018.04) (3,088,244.59) 708,045.82 6,249,171.60 (3,644,102.74) (1,376,217.16) (151,572.98) (4,466,897.33) (4,665,947.72) (3,834,687.64) (887,779.03) 6
Person E 414,541.53 387,798.41 198,506.62 (83,386.66) 391,200.41 456,935.41 3,209,469.84 357,829.13 109,608.56 (79,378.77) (262,994.93) (49,441.13) 3

Any help will be greatly appreciated.

Regards

I would suggest putting it in an excel sheet and attaching it so those interested in helping don't have to retype all of this information. As it is now, it is very confusing and and difficult to understand.

Having said this, I believe what you are looking for is =countif. It will count a range based on a certain criteria. Your criteria would be =COUNTIFS(B1:B11, "<0"), if your range was B1 to B11.

Hi

Thanks for your response. I did try to attach an Excel spreadsheet and also a printscreen saved in word, unfortunatelately both failed.

Hi,
Please see attached spreadsheet.

Originally Posted by jake.masters

Having said this, I believe what you are looking for is =countif. It will count a range based on a certain criteria. Your criteria would be =COUNTIFS(B1:B11, "<0"), if your range was B1 to B11.

Hi Jake

Countif will only return the number of negative numbers in a row. What I actually need is to be able to count consecutive occurrence of negative numbers. E.g. If month 12 is the most recent month, count the number of negative occurrences from month 12 but only return a value if it occurred more than 3 times from month 12 backwards e.g if months 9,10,11,12 were negative outflows, then the result will be 4.

I have managed to attach a spreadsheet.

Regards

nbiggss,

Sorry I am unable to work out a way of doing it for all 12 months but I can do it for 10. Please see below.

=IF(AND(M6<0,L6<0,K6<0),IF(J6<0,IF(I6<0,IF(H6<0,IF(G6<0,IF(F6<0,IF(E6<0,IF(D6<0,10,9),8),7),6),5),4),3),0)

Regards

Danny

Danny

Thank you.
I didn't think it would be possible using IF statements. I think the reason why you couldn't nest the formulae beyond 10 is due to Excel version.

After changing the compatibility mode I was able to extend your formlua for 12 months and it works.

=IF(AND(M7<0,L7<0,K7<0),IF(J7<0,IF(I7<0,IF(H7<0,IF(G7<0,IF(F7<0,IF(E7<0,IF(D7<0,IF(C7<0,IF(B7<0,12,11),10),9),8),7),6),5),4),3),0)

Thank you.

Yes that was the reason. I am not familiar with excel 2010. It's great that you managed to sort it though!

Thanks for posting your formula. Please make sure to mark the thread solved.