Nov 02 2021 06:42 AM
Hi there,
I have attached an example spreadsheet which I could do with some help on please.
I am trying to acheive the following:-
caluclating the average of each column zz, zy, zx & zw. I want to caluclate this within a section.
The section is determined by 2 rows of blue. Where there is 1 row of blue this can be disregarded.
Ideally, each time there is a double row blue, this is a new section, which would have a section name.
What would be perfect, is if there was a way to return a result which just has secion name, and the 4 averages for each section.
I hope this all makes sense.
Rob
Nov 02 2021 08:19 AM
Solution
What you're asking makes sense. What you have as a spreadsheet, however, raises a number of questions.
I've given you a solution. But I made some modifications to the way you arrayed your data, AND to the nature of the data.
The solution I offered uses a new function, FILTER, to select for the calculation only the rows that meet the criterion of belonging to the designated group. Here's the formula:
=AVERAGE(FILTER(G$3:G$47,$A$3:$A$47=$M3))
Where $G3:$G47 is the column containing the values for ZZ (I designated it as ZZ2)
$A$3:$A$47 is the column containing the group designation
$M3 is the cell containing the criterion for the filter......
You do need the most current version of Excel for FILTER to function. If you don't have that, I recommend updating your system. This is only one of a number of really useful new functions.
Nov 02 2021 09:07 AM
Nov 02 2021 09:36 AM
You're very welcome.
I'm glad my response made sense and that you're able to use FILTER.
For further education on it and other of what are called "Dynamic Array Functions" you might find this video helpful. It'll take close to 30 minutes to view, but will introduce you to some very useful and powerful functions, FILTER being just one of them. https://www.youtube.com/watch?v=9I9DtFOVPIg
Nov 02 2021 08:19 AM
Solution
What you're asking makes sense. What you have as a spreadsheet, however, raises a number of questions.
I've given you a solution. But I made some modifications to the way you arrayed your data, AND to the nature of the data.
The solution I offered uses a new function, FILTER, to select for the calculation only the rows that meet the criterion of belonging to the designated group. Here's the formula:
=AVERAGE(FILTER(G$3:G$47,$A$3:$A$47=$M3))
Where $G3:$G47 is the column containing the values for ZZ (I designated it as ZZ2)
$A$3:$A$47 is the column containing the group designation
$M3 is the cell containing the criterion for the filter......
You do need the most current version of Excel for FILTER to function. If you don't have that, I recommend updating your system. This is only one of a number of really useful new functions.