I'm hoping someone can help me because I've been at this for a week and having no luck!

I have a big excel file containing 90 participants data. From row 2 to 493 is participant one for example and each participant has the same amount of rows. I need to use an absolute reference for each participant to calulate certain formulas for each participant but I then need to change this absolute reference for the next participant. Is there a way to do this?

So for instance, say a formula contains (=IF(AND(BO14>((AVERAGE($BO$14:$BO$493))-(2.5*$BY$14)),BO14<((AVERAGE($BO$14:$BO$493))+(2.5*$BY$14))),BO14,"")

I need to keep the (average($BO$14:$BO$493) and the (2.5*$BY$14) part absolute for the 492 rows for each participant

BUT this needs to change for the next participant so that it is
(average($BO$506:$BO$985), etc.

Is there any way to do this? Make something absolute for one participant and then change it for the next by a certain number.

I've got 90 participants data and multiple formulas for each participant! Thank you for any suggestions!