I am doing a quality analysis and I only want to pull the first two data points from a table from a certain person who has multiple enteries. What would be the best process to achieve this.

Here is a simplified example of what I am trying to figure out: Jake 50 Jake 62 Jake 63 Mary 52 Mary 96 Mary 95 Mary 36

My objective is to find out these:

Jake 1= Jake 2=

and

Mary 1= Mary 2=

Once I find out these points I have to take the average of the first two scores.

1 Answers

0
JvdV On

Assuming your data looks like this:

enter image description here

The formula in E2 used would look like:

{=AVERAGE(INDEX($B$1:$B$8,SMALL(($A$2:$A$8=D2)*ROW($A$2:$A$8),COUNTIF($A$2:$A$8,"<>"&D2)+1)),INDEX($B$1:$B$8,SMALL(($A$2:$A$8=D2)*ROW($A$2:$A$8),COUNTIF($A$2:$A$8,"<>"&D2)+2)))}

Notice it's an array formula and should be entered through Ctrl ShiftEnter