Working out an automatic Average across from 1 of 3 courses (in one coloumn) for each score column

Hi,

Hoping you Excel Wizards out there can tell me how one would go about the following please?

I want to be able to automatically have the scores give an average for the different types of course against each of the quantitative questions, ie, be able to see what the average score out of 100 on each of the questions that are rated out of 100 for classic, Elite and Top-up and overall is? This is a working spreadsheet, so it will need to update the average score every time new feedback / answers of the questionnaire are entered.

Ie:

Course	Q1	Q2	Q3	Q4	Q5	Q6	Q7
Elite	48	96	100	89	100	98	77
Classic	59	96	88	79	99	100	100
Classic	96	97	88	78	99	100	100
Top up	70	92	89	100	100	97	96
Elite	99	98	86	85	79	88	78
Top up	93	69	66	84	98	87	72
Classic	96	89	88	77	78	77	100

I hope this makes sense!!!

BI & Data Analysis

excel

Formulas and Functions

OliverScheurich
Jun 23, 2022
GeorgiaW1982
=AVERAGEIF($A$2:$A$8,$A12,B$2:B$8)
Maybe with this formula for the average of each course and question.
=AVERAGE(IF($A$2:$A$8=A17,$B$2:$H$8))
Maybe with this formula for the overall average of each course. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
average.xlsx9 KB

Forum Discussion

Working out an automatic Average across from 1 of 3 courses (in one coloumn) for each score column

3 Replies

Resources