Using avg formula across multiple sheets

nick.wilson

how would i write a formula to pull the avg number of something in the same field across different sheets, or better yet how could i write an avg formula to pull the data from a report into a sheet instead?

JamesB

@nick.wilson Unfortunately you cannot pull data from a report into a sheet. I agree this would be a valuable feature.

The best way to do this is create a grid, in your first column put the name of each sheet so you know your reference, in the next column link each cell to get the data from each sheet then use a formula to average those numbers.

nick.wilson

the problem with that is that i am trying to grab the total number of task per user and if i do it the way you suggested

1) I cannot link the cells to a filtered version of the source link

2) it does not allow me to group everything under into just one cell

Unless i am missing something i am not sure this would work in this scenario.

please advise

Paul Newcome

You would need to pull in the number from each sheet and then wrap everything in an AVG function.

For example, if you are using a COUNTIFS to pull in the total number of tasks, it would look something like this:

=AVG(COUNTIFS({Sheet 1}, @cell = "Name"), COUNTIFS({Sheet 2}, @cell = "Name"))