Calculate averages from several data ranges

[Andyfc]

Hello,

I have the following problem: a table contains names of pupils in the first two columns (A and B). The next columns are:

• a code for the type of grade (e.g. M for oral exam, T for test, etc.)
• the integer part of the grade (as a number, not as A, B, an so on)
• the decimal part of the grade (believe it or not, in some countries they actually do that)
• the percentage, which is kind of irrelevant in this question.

So basically there are grades in four-column-ranges or blocks, starting from column 3 (C) and going to the end of the long table.

What I would like to do is calculate the average grades for a certain type (like the average of all Ms). How do I look up what is in the first grade-column and decide if I want to add the second and third columns to the total, depending on whether that’s an M or not? And, obviously, I would need to count the Ms too, but that’s probably easier.

(Edited to add this example)

(Second edit: It may very well be that this is not the best way to organize this type of data for this problem, in which case I am open also to suggestions on how to improve the design itself)

[mariosv]

Please attach a sample file with the needed comments,to know how the data are organizer and what it’s expected to be achieved.

[keme]

There is the AVERAGEIF() function which takes the average from one dataset (e.g. the grades column) for entries where the corresponding item from another dataset (assessment type column) satisfies a given condition. If you also need the count of matches, it may be just as easy to use SUMIF() and COUNTIF() functions.

For summary with multiple conditions there are also the AVERAGEIFS(), SUMIFS() and COUNTIFS() functions (trailing “S” for “Set of conditions”).

If you need to create a weighted average, the SUMPRODUCT() function is also useful.

[Andyfc]

I have uploaded a sample file.

[Andyfc]

@keme That is very useful indeed. Now I would like to know how to specify the range in order to get the AVERAGEIF() function (or any other) to look only in the first column of each 4-column range for the condition and get the numbers from the second column of the 4-column range (the fact that I would need the third column for the decimal part I can adjust myself accordingly).

[mariosv]

Without reorganize data, with SUMPRODUCT with a complicated formula I think it can be done.

But reorganize data to one record one row, make much more simple to build the formula with the already commented functions.
AverageCalculations.ods

Alto to use pivot tables to achieve the results.