We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calculate averages from several data ranges

asked 2020-01-25 18:47:07 +0200

Andyfc gravatar image

updated 2020-01-26 09:29:19 +0200


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)

edit retag flag offensive close merge delete


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

m.a.riosv gravatar imagem.a.riosv ( 2020-01-25 19:24:36 +0200 )edit

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.

keme gravatar imagekeme ( 2020-01-25 19:48:16 +0200 )edit

I have uploaded a sample file.

Andyfc gravatar imageAndyfc ( 2020-01-26 09:17:56 +0200 )edit

@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).

Andyfc gravatar imageAndyfc ( 2020-01-26 09:22:45 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-01-26 11:12:27 +0200

m.a.riosv gravatar image

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.

image description

Alto to use pivot tables to achieve the results. image description

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-01-25 18:47:07 +0200

Seen: 258 times

Last updated: Jan 26 '20