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

Discuss the spreadsheet application

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

Postby Andyfc » Sun Feb 02, 2020 1:48 pm

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
(9.87 KiB) Downloaded 25 times
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

Postby FJCC » Sun Feb 02, 2020 4:20 pm

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
(11.38 KiB) Downloaded 16 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7750
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby Lupp » Sun Feb 02, 2020 6:22 pm

From München. A German teacher.
Attachments
aoo100973exampleAVERAGEIF_1.ods
(14.26 KiB) Downloaded 22 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
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

Postby Andyfc » Mon Feb 03, 2020 2:48 pm

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


Gruß Gott. I see that a colleague figured out what I needed this for! :super: 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

Postby Lupp » Mon Feb 03, 2020 3:40 pm

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
User avatar
Lupp
Volunteer
 
Posts: 2913
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 12 guests