Filtering values and displaying in different columns

[panmuskul]

Hi All,

I would like to filter column B and display in column E all numbers that are bigger than 1.

Also if possible I would like to display in column E an average of all numbers from column B that are bigger than 1.

The reason why: daily i am downloading flow rates of our pumps. Pump is not working all the time and every time it stops it will showe it as 0. I need to calculate daily average flow but only when pump was working

Document attached

Thank you

[AliGW]

Are you still using Excel 2016 or something newer?

[Glenn Kennedy]

In E3, copied down:

=IFERROR(AGGREGATE(15,6,$B$3:$B$1500/($B$3:$B$1500>1),ROWS(E$3:E3)),"")=IFERROR(AGGREGATE(15,6,$B$3:$B$1500/($B$3:$B$1500>1),ROWS(E$3:E3)),"")

in E1:
=AVERAGE($E$3:$E$1000)

You may need ; instead of ,

[panmuskul]

Unfortunatelly yes , still using Excel 2016. Why , is it easier to do in the newer versions ?

[panmuskul]

Thanks a lot it works. Just out of interest why $ symbol in the average ?