I have a large collection of data, which is accordingly filtered to do analysis. However, I cannot seem to be using the right values when selecting data ranges which contain hidden cells/rows by the filter. The table below shows an example. If I wanted to calculate the average of column TIME =AVERAGE(E12:E16), the range will include the hidden value, row 11 and 13 in this case.
Is there anything I could do to use formulas of the filtered data without including the hidden (filtered out) values? I could work with Excel and/or Calc.