SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3543000%22%20slang%3D%22en-US%22%3EWorking%20out%20an%20automatic%20Average%20across%20from%201%20of%203%20courses%20(in%20one%20coloumn)%20for%20each%20score%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3543000%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EHoping%20you%20Excel%20Wizards%20out%20there%20can%20tell%20me%20how%20one%20would%20go%20about%20the%20following%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20automatically%20have%20the%20scores%20give%20an%20average%20for%20the%20different%20types%20of%20course%20against%20each%20of%20the%20quantitative%20questions%2C%20ie%2C%20be%20able%20to%20see%20what%20the%20average%20score%20out%20of%20100%20on%20each%20of%20the%20questions%20that%20are%20rated%20out%20of%20100%20for%20classic%2C%20Elite%20and%20Top-up%20and%20overall%20is%3F%26nbsp%3B%20This%20is%20a%20working%20spreadsheet%2C%20so%20it%20will%20need%20to%20update%20the%20average%20score%20every%20time%20new%20feedback%20%2F%20answers%20of%20the%20questionnaire%20are%20entered.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIe%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3ECourse%20%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3EQ1%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3EQ2%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3EQ3%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3EQ4%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3EQ5%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3EQ6%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E%3CSTRONG%3EQ7%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3EElite%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E48%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E96%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E89%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E98%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E77%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3EClassic%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E59%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E96%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E88%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E79%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E99%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3EClassic%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E96%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E97%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E88%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E78%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E99%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3ETop%20up%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E70%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E92%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E89%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E97%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E96%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3EElite%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E99%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E98%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E86%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E85%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E79%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E88%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E78%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3ETop%20up%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E93%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E69%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E66%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E84%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E98%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E87%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E72%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2277%22%3E%3CP%3EClassic%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E96%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E89%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E88%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E77%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E78%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E77%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2277%22%3E%3CP%3E100%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20makes%20sense!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3543000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3543046%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20out%20an%20automatic%20Average%20across%20from%201%20of%203%20courses%20(in%20one%20coloumn)%20for%20each%20score%20colu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3543046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1433923%22%20target%3D%22_blank%22%3E%40GeorgiaW1982%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAVERAGEIF(%24A%242%3A%24A%248%2C%24A12%2CB%242%3AB%248)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20formula%20for%20the%20average%20of%20each%20course%20and%20question.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAVERAGE(IF(%24A%242%3A%24A%248%3DA17%2C%24B%242%3A%24H%248))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20formula%20for%20the%20overall%20average%20of%20each%20course.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3543108%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20out%20an%20automatic%20Average%20across%20from%201%20of%203%20courses%20(in%20one%20coloumn)%20for%20each%20score%20colu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3543108%22%20slang%3D%22en-US%22%3EThanks%20both%2C%20I'll%20try%20these%20in%20a%20bit%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3544022%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20out%20an%20automatic%20Average%20across%20from%201%20of%203%20courses%20(in%20one%20coloumn)%20for%20each%20score%20colu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3544022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3BThanks%20so%20much%20for%20your%20help%20and%20for%20coming%20back%20so%20quickly.%26nbsp%3B%20This%20will%20work%20fine%20for%20us!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!!!

3 Replies
best response confirmed by GeorgiaW1982 (New Contributor)
Solution

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

@Quadruple_Pawn Thanks so much for your help and for coming back so quickly.  This will work fine for us!