Problem Calculating an Average

[Chrishb59]

I am trying to create a formula to calculate an average in a fairly large spreadsheet that I cannot seem to do either with AverageIF, AverageIFS or Vlookups.

I have copied a sample S/Sheet to try and indicate the problem.

I need to average the Column "Rating" based upon certain criteria.

Column "H Value" needs to be a specific number, say 8 in this case.
Column "D Value" is then any value when Column "H Value" equals 8.
I then need to look up any of the values from column "D Value" where Column "H Value" equals 8 in Column "M Value" and take the average of their corresponding values in Column "Rating".

In this case I need to average cells (the number is the row)Rating 3,Rating 10,Rating 11,Rating 13,Rating 14,Rating 16 and Rating17.

I hope I have made myself clear.

Many thanks for any attempts at assistance.

H Value D Value M Value Rating
1 16 14 138
1 14 19 128
2 15 11 113
3 15 16 140
4 12 11 173
4 17 15 137
4 19 16 178
5 17 12 161
5 14 18 101
6 14 16 156
7 19 12 200
7 12 14 184
8 14 11 115
8 12 13 137
8 18 14 194
8 13 17 104
9 13 19 114

 

[energman58]

Not sure I follow what you are trying to do but this may put you on the correct track

I will imagine we have 3 values like your HDM in columns A, B and C and a Score "Rating" in column D

the following formula will give you the average rating for cases where HD&M are all equal to 2

=SUMPRODUCT(--(A2:A5=2),--(B2:B5=2),--(C2:C5=2),D2:D5)/SUMPRODUCT(--(A2:A5=2),--(B2:B5=2),--(C2:C5=2))

Is this what you are trying to do?

 

[Chrishb59]

energman,

Thanks for your reply.

Ideally I could do with being able to upload a sample spreadsheet.

Unfortunately, your suggestion will not work I don't think but will try and tinker with it.

In the example shown the "numbers" that need averaging are 138,161,101,200,184,137,194.

They are the numbers because in column "D Value" numbers 14,12,18 and 13 match number 8 from column "H" value. I then need to average the "Rating" column where numbers 14,12,18 and 13 position themselves in column "M Value". The numbers in columns "M Value" and "D Value" cannot match.

Thanks again.

 

[energman58]

So the problem is find a list of D numbers based on H

So if H is 8 D numbers that match are 12, 13, 14, 18

Then look in columm M for those numbers and average the corresponding Ratings?

 

[Chrishb59]

Spot on, why couldn't I have put it so succinctly

 

[energman58]

If you can use a helper column then this will work

=IF(ISNUMBER(MATCH(C2,($A$2:$A$5=2)*$B$2:$B$5,0)),C2,"")

You enter it as an array formula (Control - Shift - Enter) and if you have it right you get a set of {} around it automatically

Enter it in the first cell and copy it down to the end of your data

You then average this helper column to get your answer

Obviously you change 2 to whatever value you need and adjust the arrays to suit your data

There may be a way to do it with array formulas in a single step (without a helper column) but I struggled with it and couldnt think of one

 

[barry houdini]

Assuming "D Value" in Column D, "H Value" in column H, Rating in Column R etc. you could use this single "array formula"

=AVERAGE(IF(ISNUMBER(MATCH(M1:M17,IF(H1:H17=8,D1:D17),0)),R1:R17))

confirmed with CTRL+SHIFT+ENTER

 

[Chrishb59]

Thank you both energman and barry.

Your help very much appreciated. I will not only use your solutions, but work out how they apply to enhance my knowledge.

Thanks again.