Find Max Value in row based on adjacent cell containing specific value

[gazmeist]

Hi guys

Love this board - has helped me for many years in finding solutions to my excel/vba problems.

I currently have this tricky one and have tried a few different Max(if(Mod(column..... iterations without much luck

I have my data in columns, with each row representing a different person, so can't rearrange things.

In basic terms the data I have looks something like this

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Round 1[/TD]
[TD]Races[/TD]
[TD]Round 2[/TD]
[TD]Races[/TD]
[TD]Round 3[/TD]
[TD]Races[/TD]
[TD]Round 4[/TD]
[TD]Races[/TD]
[TD]Round 5[/TD]
[TD]Races[/TD]
[TD]Best 8 Race Result[/TD]
[TD]Best 9 Race Result[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]15[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]13[/TD]
[TD]8[/TD]
[TD]23[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now its various calculations that fill in the round results and # of races for each round.

Ive come up with a couple of variations for the formulas for the Best X race result fields but not having luck

Note: CL3:KG3 is the range from from round 1 to the final round columns
Also Note: Entered as Array Formulas (Ctrl-shift-enter)

Best 8 Race:
=MAX(IF(
IF(MOD(COLUMN($CL3:$KG3)-COLUMN($CL3)+0,3)=2,$CL3:$KG3,0)=8,
IF(MOD(COLUMN($CL3:$KG3)-COLUMN($CL3)+0,3)=0,$CL3:$KG3,0),0))

I originally had it as

=MAX(IF(
IF(MOD(COLUMN($CL3:$KG3)-COLUMN($CL3)+0,3)=2,$CL3:$KG3,0),8,
IF(MOD(COLUMN($CL3:$KG3)-COLUMN($CL3)+0,3)=0,$CL3:$KG3,0)))

But this just produced the max result no matter in the row irrespective of races etc.

Any help would be greatly appreciated

 

[oldbrewer]

round 3 - 9 races 10 points do you need to convert this to 8 races = 8.88

then do you want highest score for 8 races

can you put up with an out of sight helper table?

 

[gazmeist]

OK to clarify further

The formula needs to search the whole row, and only for rounds that had 8 races, find the max result for those rounds.

So for my example above

[TABLE="class: cms_table_grid, width: 800"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Round 1[/TD]
[TD]Races[/TD]
[TD]Round 2[/TD]
[TD]Races[/TD]
[TD]Round 3[/TD]
[TD]Races[/TD]
[TD]Round 4[/TD]
[TD]Races[/TD]
[TD]Round 5[/TD]
[TD]Races[/TD]
[TD]Best 8 Race Result[/TD]
[TD]Best 9 Race Result[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]15[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]13[/TD]
[TD]8[/TD]
[TD]23[/TD]
[TD]8[/TD]
[TD]23[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]


Hope that helps make more sense

 

[oldbrewer]

[TABLE="width: 1048"]
<colgroup><col span="11"><col><col><col></colgroup><tbody>[TR]
[TD]Person[/TD]
[TD]Round 1[/TD]
[TD]Races[/TD]
[TD]Round 2[/TD]
[TD]Races[/TD]
[TD]Round 3[/TD]
[TD]Races[/TD]
[TD]Round 4[/TD]
[TD]Races[/TD]
[TD]Round 5[/TD]
[TD]Races[/TD]
[TD]Best 8 Race Result[/TD]
[TD]Best 9 Race Result[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]this very simple macro gives the results you want[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]n8 = 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] n9 = 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For j = 2 To 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] For k = 3 To 11 Step 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"] If Cells(j, k) = 8 Then If Cells(j, k - 1) > n8 Then n8 = Cells(j, k - 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"] If Cells(j, k) = 9 Then If Cells(j, k - 1) > n9 Then n9 = Cells(j, k - 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(j, 12) = n8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Cells(j, 13) = n9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] n8 = 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] n9 = 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gazmeist]

Thanks Oldbrewer. Hadn't considered VBA options yet as I figured there should be a fomulatic way to do it. Will go the CBA route but still curious to see if it can be done by formula if anyones up to the challenge.

Cheers