CODEWITHSUNDEEP
vbaexcel
Moe.A
Moe.A

Reputation: 81

Statistics of Variable Range Using Criteria

I need help to calculate min, max, average using data criteria. The image below shows two columns of data namely time and speed. I want to calculate the average speed for the range from time 0 (cell 2) to cell 14. Then, calculate the average speed from the next 0 (cell 15) to cell 20, and so forth. Obviously, the same formula can be applied to calculate min, max, and other statistics.

Is there a way to write a smart formula that knows how to average each segment separately? The image below illustrates three segments, but my original dataset is much bigger: enter image description here

Upvotes: 1

Views: 62

Answers (2)

T.M.
T.M.

Reputation: 9948

Approach with helper column only in summary

You don't need to fill all data rows with a helper column, you can confine input to the summary range. This approach uses the Indirect function in the summary range to Match each last cell in a data block (identified by 0) and calculate results via the Subtotal function (arguments 1-average, 5-minimum, 4-maximum).

  1. Initialize cell E1 with =1

  2. Then you can write the following formulae into row E2:H2 and copy them down as long as you find data blocks. Column E shows the last cells in each block, F:H display averages, minima and maxima:

    E2 =MATCH(0,INDIRECT("$A" &$E1+2 & ":$A$10000"),0)+$E1

    F2 =SUBTOTAL(1,INDIRECT("$B$" & $E1 +1 & ":$B$" & $E2))
    G2 =SUBTOTAL(5,INDIRECT("$B$" & $E1 +1 & ":$B$" & $E2))
    H2 =SUBTOTAL(4,INDIRECT("$B$" & $E1 +1 & ":$B$" & $E2))

Note

a) It's necessary to have a closing zero (0) in the last data row.

b) This example assumes the helper items in column $E; you can easily change that to any column.

Upvotes: 1

virtualdvid
virtualdvid

Reputation: 2421

You can add a column to group when it finds a 0.

  1. Initialize the cell with 1 and in the next cell use =IF(A3=0,C2+1,C2)

  2. Then you can use the formulas, AVERAGEIF, MINIFS, MAXIFS. (Available only in Excel v2016).

    =AVERAGEIF($C$2:$C$17,E2,$B$2:$B$17)
=MINIFS($B$2:$B$17,$C$2:$C$17,E2)
=MAXIFS($B$2:$B$17,$C$2:$C$17,E2)

Upvotes: 2

Related Questions