IF Formula Assistance

[ExcelRaceRatings]

Hello,

Hoping someone can assist with an IF Formula issue i am having as per the below photo.

What i would like to achieve is to adjust the current formula in Column BV (Revised Fig) so that the calculation is only performed on figures in column AO (RTG) based on the criteria in Column BX (Race ID). The way it is currently set up is that it is performing the calculation on all figures in Column AO (RTG) and thats not what i am after.

So basically in BV2:BV8 it should be revising the figures of AO2:AO8 from 14 through to 1 because BX2:BX8 is RACE ID *1.

Any assistance would be greatly appreciated?

 

[NiMip]

Ok, this should work, but only if your Race ID values are all grouped together. If appears that this is the case, based on your screenshot. But if not, you'll want to sort by Race ID to make sure they'll all grouped.

=IF(AO2="","",MIN(MAX(CEILING((AO2-MIN(OFFSET($AO$1,MATCH(BX2,$BX$2:$BX$250000,0),,COUNTIF($BX$2:$BX$250000,BX2))))/((MAX(OFFSET($AO$1,MATCH(BX2,$BX$2:$BX$250000,0),,COUNTIF($BX$2:$BX$250000,BX2)))-MIN(OFFSET($AO$1,MATCH(BX2,$BX$2:$BX$250000,0),,COUNTIF($BX$2:$BX$250000,BX2))))/14),1),1),14))