Trying to find the average of multiple matched values

[dch09]

Hi,

Firstly - apologies for the awful explanation that is about to come your way. Please ask me some questions if you need to.

I am looking for a formula that goes into I1 - that looks at the students name, matches to a row on this next sheet.

then finds all the tests that have the spec reference of 1.1.1 (A5), and finds the average of those numbers (5, 3, 5 and 8)

This is what i have so far...

=AVERAGE(VLOOKUP(B1,'Y12 DQ'!B4:BX52,{17,19,23},FALSE))

​

But i have to input the columns myself (17,19 and 23) as I complete more tests against that spec the average wont change. I've tried messing around with filtering but I am not that excel savy.

Could someone please help me fix this

Thanks
Dom

 

[hagia_sofia]

Hello, could you please test this:

=MAP(A5:A10,LAMBDA(a,IFERROR(AVERAGE(FILTER(FILTER('Y12 DQ'!$C$4:$BX$52,'Y12 DQ'!$B$4:$B$52=$B$1),'Y12 DQ'!$C$3:$BX$3=a)),"")))

 

[dch09]

Hello, could you please test this:

=MAP(A5:A10,LAMBDA(a,IFERROR(AVERAGE(FILTER(FILTER('Y12 DQ'!$C$4:$BX$52,'Y12 DQ'!$B$4:$B$52=$B$1),'Y12 DQ'!$C$3:$BX$3=a)),"")))

Hi Hagia_Sofia,

Thank you so much! Works a treat. If you can - could you let me know exactly what this function/formula is doing so i can attempt to decipher it. I have not seen map and lambda before.

Thanks again.
Dom

 

[hagia_sofia]

Hello, that is good to hear. What MAP + LAMBDA do here is basically to apply the relevant calculation (IFERROR...) to each row of the array (A5:A10); so, "a" represents each element of that array. As a result, instead of copying the formulace to every row separately, it returns a spilled array.