Forum Discussion

Preston Tesvich's avatar
Preston Tesvich
Copper Contributor
Feb 02, 2018

Performing count and max commands on multiple groups of rows

Let's say you have rows:

0.000
0.000
0.000
5.000
6.000
10.000
0.000
0.000
0.000
5.000
...

There is no consistency between the sets, just groups of zeros and non-zeros. 


I need a set of commands that can count the number of zeros between each set of numbers and report it in the next column, count the number of non-zero cells in each group and report it, and find the max in each set of non-zero numbers i.e.:
Col A Col B (count) ColC (max)
0.000
0.000
0.000 3
5.000
6.000
10.000 3 10.000
0.000
0.000
0.000 3
8.000
20.000
50.000 3 50.000
0.000
...
For clarity I want to do this programmatically. So if I load up 1,000,000 rows with patterns like the above, column B and C automatically populate.


Thanks in advance for the excel fu!

Formulas & Functions
tips and tricks

2 Replies

Sort By
  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Feb 03, 2018

    AFAIK, as you have over 1,000,000 rows, and inconsistent group size, a single formula in column b will have slow performance.  Hence, I suggest to use a helper column, which keep the first row number of the first cell of the current group, which I keep in Column D.

     

    In order to make it easier to explain, I create a name, SameGroupAsPrevious

    =NOT(OFFSET(Sheet1!$A1, -1, 0)) = NOT(Sheet1!$A1)

    Not(anything, except zero) will be true false, and Not(zero) will be true.  If the not result of previous row equals to the not result of this row, this row is in the same group as previous row.

     

    Also, I create a name, SameGroupAsNext

    =AND(NOT(Sheet1!$A1) = NOT(OFFSET(Sheet1!$A1, 1, 0)), OFFSET(Sheet1!$A1, 1, 0) <> "")

    If the not result of this row and that of the next one is the same, and this row is not the last row (next row is not empty, then, this is not the last row), it is in the same group with the next row.

     

    In addition, create a name, ThisGroupData

    =INDIRECT("A" & INT(Sheet1!$D1) & ":A" & ROW())

    Base on the first row number in Column D, we can get the group data from that row to the current row.

     

    Now, I put the following formula in Column D

    =IF(ROW()=1,1,IF(SameGroupAsPrevious, OFFSET(D1, -1, 0), ROW()))

    If current row is 1, first row is 1. Otherwise, if in the same group as previous, keep its first row in Column D in this row.  if not in the same group, obviously, this is the first row of the current group, and hence, put the current row number in column D.

     

    In Column B, 

    =IF(SameGroupAsNext, "", COUNT(ThisGroupData))

    If this group has next data, do not need to show the count, otherwise, show the count of this group. 

     

    In Column C

    =IF(A1 = 0, "", IF(SameGroupAsNext, "", MAX(ThisGroupData)))

    If column A is 0, do not need to show the max.  Otherwise, if this is the last row of this group (SameGroupAsNext = FALSE), find the max. number.

     

     

    However, any changes in column A, B, C, D, and those names formula, it will take you some time to recalculate the results. 

  • Logaraj Sekar's avatar
    Logaraj Sekar
    Steel Contributor
    Feb 03, 2018

    Hi,

     

    Let your data starts from first cell.

     

    In Cell B3, Put "=COUNT(A1:A3)"
    In Cell B6, Put "=COUNT(A4:A6)"
    In Cell C6, Put "=MAX(A4:A6)"

    By coping and pasting these formula, you can get result.

     

    But count of rows should be multiple of 6, or else you can't paste for entire cell for getting result.

     

     

     

     

     

Resources