Averages from filtered Data

[bob_evan]

Hi,

I a spreadsheet that shows different store owners, the stores that they own, and the performance for those stores. I use the search function to search by owner name, which filters the data on the screen. Is there any way to show an average row for the data that is on the screen?

So If I type in “John Doe” It will filter down to John Doe’s 5 stores and then have a bunch of stats for each one in a row. Is there a way for there to be a row below the entries for Johns 5 stores that shows the average of them all together.

[Kirk_Masden]

Hi @bob_evan! Sorry to see that no one responded. The short answer is that this is possible. I think you would use a SELECT() expression in a virtual column to find the rows with appropriate data. Then, in that same virtual column, you would use AVERAGE() to find the average. The following article shows how the two can be combined:

help.appsheet.com

AVERAGE()

Arithmetic average of list items

[OptimiX_XcrY]

I don’t know if this applies to you…

When I needed a weighted average or something similar with stats from a table I did this :

-I made a second table with only one row and only one physical column. This is to be used for filtering data, that single cell being a quickedit dropdown of the values I wanted to use to filter data (in your case, the name)
-I made a slice (or multiple slices) of my main table, witch was filtered based on the value from the cell above, and a view for this slice

• in the one cell table, I created some virtual columns, in my case one suming the quantities from the Slice , and one countinfg the rows from the Slice , one making an average from the first two VC , and some other VCs that i needed
  -I created a dashboard view with the one cell quickedit view and the VCs, and the view of the slice.

In my thinking for your case, in this dashboard I would quickedit John (instead of the usual search) , and I would see the personalized stats for him (the VCs) , and I would also see the filtered data (the slice)

The two screenshots below are based on this idea , but with multiple filtering quickedits, and the two views can be put one under another (in the firs you can see some weighted average stats below the dropdowns)