#1

I have a spreadsheet with two columns, “Name” and “Score” and hundreds of rows. Each row can contain a non-unique name.

I want to create a different view, that automatically updates, which aggregates these rows and calculates the average score for each unique name. In SQL, this would be done trivially with a statement like “SELECT name, AVG(score) FROM table GROUP BY name;”

I believe Libreoffice has some similar functionality, but I’m not familiar enough with it. Would this be case for pivot tables or some sort of conditional row expressions? How would I do this in Libreoffice?

#2

A simple but possibly insufficient solution is to use a formula. If we were to assume that you already had a list of unique names in the A column, you could fill another column with this:

=AVERAGEIFS(score,name,"="&$A1)

If we need to create the list of unique names however, we’re going to have to use a pivot table (or macros; or a really hacky formula solution).

Select the two columns, click Insert → Pivot Table… → Current Selection → OK. Drag Name over in the Row Fields list, and drag Score over in the Data Fields list. Double-click on Sum - Score in the Data Fields list and select Average from the Function dialog.

(If this works for you, please click on the ✓ to accept the answer.)

How to count the number of text value appearances?
#3

Use a pivot table (formerly ‘DataPilotTable’) with your data range as the source, the Name-column as the (only) row field and the Scoure column as the only data field with evaluating function AVERAGE.

You may add a filter and define the output to start on the same sheet if you want.

The output will not refresh automatically, but on ‘Refresh’ chosen from the context menu of any cell used for it.
A slightly annoying fact is (as I see it) that formats once set for the output range are ignored by the refresh.

#4

You can use Subtotal as shown in this post. I found it useful.

1 Like