Average for Filtered Children Rows Only

James.son

Hi! I have sheet with a column Y or N that I filter on. I filter on all the Y and am trying to get the average for the children rows (they are a %). However, it's averaging all the children rows (not taking the filter into consideration). How can I get the average for ONLY the Y children rows?

Thanks!

Ray Lindstrom

Hi @James.son,

That can't by accomplished by using the Filter feature for the grid itself.

If you have a formula in a cell averaging a range of cells; A cell won't change it's formula to display only what the grid's filter is set to. That range of cells still have all the data that's not being displayed (because it's filter out), so you'll always get the average of all Y and N rows.

You'll need to build that "filter" into your formula, so that it only averages (or sums and then divides by the count using the sum and count formulas) the rows where your other column has a value of Y.

Hope this helps, and good luck!

All the best,

-Ray

Andrée Starå

Hi @James.son

I hope you're well and safe!

To add to Ray's excellent advice/answer.

Another option could be using a Report instead.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

James.son

@Ray Lindstrom Do you have an example of the formula? Because I'm having issues with it as I need to count the whole range instead of one cell. Thanks!