Max ignore the last column

[KIM Chanthan]

Hi,

Can anyone help to solve my issue? I want to find the largest value by ignoring the value in last column.
Please find attached file for more detail.

Thanks,

 

[bosco_yip]

Try...………….

In B5, enter formula :

=MAX(OFFSET($A2,,,,MATCH(9^9,2:2)-1))

 

[Peter Bartholomew]

 

[pecoflyer]

If no gaps eventually =MAX(A2:INDEX(A2:D2;COUNTA(2:2)-1))

 

[shrivallabha]

If no gaps eventually =MAX(A2:INDEX(A2:D2;COUNTA(2:2)-1))

You can take care of gaps by using construct like below.
=MAX(A2:INDEX(A2:F2,MATCH(2,1/ISNUMBER(A2:F2))-1))

 

[KIM Chanthan]

Try...………….

In B5, enter formula :

=MAX(OFFSET($A2,,,,MATCH(9^9,2:2)-1))

View attachment 68248

It works perfectly thanks :D

 

[KIM Chanthan]

You can take care of gaps by using construct like below.
=MAX(A2:INDEX(A2:F2,MATCH(2,1/ISNUMBER(A2:F2))-1))

It works perfectly thanks :D

 

[Peter Bartholomew]

With two overlapping named ranges