Excel Count Concurrent Values In A Row

ilfocorp
ilfocorp used Ask the Experts™
on
Hello programmers.  I have a 2010 spreadsheet that has a column with currency values from $0 and up.  I would like to count the maximum number of times the value $0 occured concurrently before a value greater than $0 occurred.  Please see attached simple one column spreadsheet.  For example, row 2 thru 4 equals 3 and row 10 thru 11 equals 2, so I want in a separate cell, "Max Number of times $0 occurred concurrently", the number 3.  If it's possible.  Thank you.
Row 1: $80.00
Row 2: $0.00
Row 3: $0.00
Row 4: $0.00
Row 5: $87.00
Row 6: $122.00
Row 7: $108.00
Row 8: $62.00
Row 9: $20.00
Row 10: $0.00
Row 11: $0.00
Row 12: $22.00
Test-Count.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you can afford a helper column then it will be very easy.

Enter this formula in B2 (or any other cell in row 2) and copy it down.

=IF(AND(A2=0,A3=0),B1+1,1)

Then you can pick up the maximum of that column.

Author

Commented:
This will work.  Thank you.
If you are not happy with the solution why did you not say so? You should always give a chance to improve it before assigning a B grade.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial