Average of matching data only

[technik]

Hi

Apologies if this has been answered before, I can't find the thread and not having any joy هn the net ....

I am trying to find the average of matching candidates' scores only.

On sheet1 I have a list of candidates. On sheet2 I have a selected candidate list from the Sheet1 list - this list can vary in length. There is no other data on sheet2 and I need to keep it that way.

I am trying to find the average point score of the list of candidates on sheet2 by using a vlookup but am not getting the correct average score (which should be 51 but I am getting 76).

I suspect there is more to just doing

Please Login or Register  to view this content.

. Am I supposed to be using the MATCH function here also?


Sheet attached and help/pointers much appreciated.

[CARACALLA]

Sheet3

F7=SUMPRODUCT((ISNUMBER(MATCH(Sheet1!B3:B29,Sheet2!B3:B7,0)))*(Sheet1!E3:E29))/SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!B3:B29,Sheet2!B3:B7,0))))

[José Augusto]

Hi

You can try this array formula (enter with CTRL+SHIFT+ENTER)

Please Login or Register  to view this content.

[technik]

Thanks guys!

[technik]

Hi jose

Just checking whether the array formula only works if the list on sheet2 is in consecutive order ie. grouped ? When it is grouped the formula works fine but if I was matching random candidates ie. candidate 1, candidate 6, candidate 10 etc. I seem to get the incorrect average ???

[José Augusto]

Hi @techenic

It works for me.
To test, in sheet1, I sort the table by column A and GROUP1. Now, the elements are not contiguous and the result is the same.
Another test, after that, I change 'Jeffrey Johnston' by 'Pauline Medina' in sheet2 and the result is ok.
See the file

[technik]

That's brill - thanks again !

[José Augusto]

You are welcome.

Thanks for the rep and feedback.