[Solved] Average every n-th column based on condition

[Solved] Average every n-th column based on condition

In the attached example-table I have person's names and then a set of data that takes on 4 columns and repeats. The only thing that changes in the data is the first field "Type" and the numbers. As suggested in the example itself, I would like to put in column "Average ALs" the average of all the fields "Points" where "Type"="AL". Accordingly for the other types "M" and "T". The table will expand with time, so that the averages will not stay in columns T, U and V as they are now.

What is the formula to calculate the averages based on the content of field "Type" and looking every 4 columns?
Attachments
beispiel-noten-2.ods
Last edited by MrProgrammer on Sun Feb 09, 2020 7:27 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
LibreOffice 6.4 on Ubuntu 18.04
Andyfc

Posts: 2
Joined: Sat Feb 01, 2020 10:37 pm

Re: Calculating average every n-th column based on condition

Your data layout makes calculations difficult. I cannot think of a way to calculate averages without manually designating the cells, which will make adding data tedious and prone to error. I suggest you rotate the data as I show in Sheet2 of the attached file. You can then use either AVERAGEIFS() or a Pivot Table (menu Data -> Pivot Table) to easily do the calculations. Right click on the pivot table and select Edit Layout to see how I set it up.
Attachments
beispiel-noten-2_fjcc.ods
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7750
Joined: Sat Nov 08, 2008 8:08 pm

Re: Calculating average every n-th column based on condition

From München. A German teacher.
Attachments
aoo100973exampleAVERAGEIF_1.ods
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2913
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calculating average every n-th column based on condition

Lupp wrote:From München. A German teacher.

Gruß Gott. I see that a colleague figured out what I needed this for! Plus, your solution seems to really work, which impresses me.

I will play with it more as soon as the Winterferien are over and report back, but it looks very promising indeed.
LibreOffice 6.4 on Ubuntu 18.04
Andyfc

Posts: 2
Joined: Sat Feb 01, 2020 10:37 pm

Re: Calculating average every n-th column based on condition

An additional suggestion: Place the evaluation next to the participants' names.

Also: Most recent data you are working on in sight: Left hand again.
Prepare for the insertion of additional couples of data.
Don't merge cells.
Check for the column names ("Type", "Points") ...
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2913
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany