AverageIF - array - averaging test results

WorthJ

New Member
Joined
May 4, 2018
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I'm trying to average a set of test results based on a lookup criteria.

The criteria is someone's ID number and i'm creating a separate sheet with everyone persons averaged results (there are 600 people).

What is the forumla for calculating an average from an array when using a look up?. I think it must be some kind of an array but I can't get it to work.

What I'm looking for is:

Average value for Questions 1, 2 and 3 (columns b,c,d) when Response ID (column A) = A cell reference of my choice (i.e. b8)

abcd
1Response IDQuestion 1Question 2Question 3
244345
345672
446456
547278
648257
7
8Average mark across Q1, Q2 and Q344What is the formula?
9Average mark across Q1, Q2 and Q345What is the formula?

Thanks for your help,

J
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
Thanks for updating to your profile.
How about
+Fluff New.xlsm
ABCDEFG
1Response IDQuestion 1Question 2Question 3ID
244345455
345672475.666667
446456465
547278484.666667
648257444
Master
Cell Formulas
RangeFormula
G2:G6G2=AVERAGE(INDEX($B$2:$D$6,MATCH(F2,$A$2:$A$6,),))
 

WorthJ

New Member
Joined
May 4, 2018
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
Works an absolute treat, thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,110
Messages
5,545,993
Members
410,720
Latest member
SSL
Top