Changing range

To all Excel specialist (as a profession or a hobby) good day,

I measure pupil dilation and eye movement (which include for example fixation - a gaze on the same spot and saccades - a quick eye movement to the next spot. A fixation occurs between 2 saccades) and I began with data that include too many rows (multiple rows for each fixation - 13,000~ in general...a very shortened version is attached) which I wanted to base on for a pivot table.

I managed to narrow it down to a single serial number for each fixation (column P) but it returns only the pupil size for the row of the fixation's serial number instead of the average pupil size for the whole rows referring to the same fixation.

I tried to use a formula to get these averages but it only picks the average for X number of cells instead of the average for an entire fixation, which can take a varying number of rows.
Ideally, I'd like Excel to "understand" that when P contains a number, I want the S (for the left pupil, or T for the right) column to contain the average for Q (for the left, or R for the right) until the next number in P. By coincidence for the first fixation it was Q2:Q14 but as I said, for the next fixations the number of cells may vary.

Could you please help me?

Thanks a lot,
Rakefet

You can use this formula in S2:

=IF(P2="","",AVERAGE(Q2:INDEX(Q:Q,MATCH(P2+1,P:P,0))))

and a very similar formula in T2:

=IF(P2="","",AVERAGE(R2:INDEX(R:R,MATCH(P2+1,P:P,0))))

Copy these down as far as you need them. For the last AVERAGE to work properly, you will need to put the next serial number in the first blank cell at the bottom of column P (i.e. put 6 in P344 in this example).

Hope this helps.

Pete

EDIT: You may need to use semicolons ( ; ) instead of commas ( , ) in the formulae, depending on your regional settings.