can anyone demystify match+countif+offset to return the row number which contains the highest value from the range

[joramastiff]

It has been banging my head against the wall for the past few days, and still have no explanation.
the formula is to return the row number which contains the highest value
=MATCH(TRUE,COUNTIF(OFFSET(rng,ROW(rng)-CELL("row",rng),0,1),MAX(rng))>0,0)

The formula works perfectly, but i can't understand the logic behind it...
Even the offset part is puzzling...it works only if height specified to 1, if it's set to 0( which is all rows), the result will be wrong.
offset returns the values from the first column...
To me, the way countif range is constructed doesn't make any sense....
also, if you plug it into the spreadsheet and inspect it with F8, it provokes even more questions ...

but it gives the correct result. Can anyone please demystify that for me.

 

[bosco_yip]

Hi,

To return the highest (max) value row number

I think this normal formula (non-array) appear more simpler, and return the same result as per your formula :

=SUMPRODUCT(ROW(rng)*(rng=MAX(rng)))

Regards
Bosco

 

[joramastiff]

Hi,

To return the highest (max) value row number

I think this normal formula (non-array) appear more simpler, and return the same result as per your formula :

=SUMPRODUCT(ROW(rng)*(rng=MAX(rng)))

Regards
Bosco

Thank you, but it was more about understanding the logic behind the formula. Which I have already found.

 

[Peter Bartholomew]

The inner part of the formula
= ROW(rng)-CELL("row",rng)
is just a way of creating a zero-based index 0-4. I normally use a named formula 'k' to avoid being distracted by a messy formula that misleads one into thinking it has something to do with rows. Thesedays
= SEQUENCE( 5, 1, 0 )
will do the same job. The OFFSET formula
= OFFSET( rng, k, 0, 1 )
then is used to generate a collection of single-row ranges, extracted from the rng. The formula will generate a #VALUE! error if written to the grid or used in practically any function. It seems that COUNTIF (which accepts references as the criterion range but not arrays) does know what to do with this 'array of ranges' object, so
= COUNTIF( OFFSET(rng,k,0,1), MAX(rng) )
returns an array with 1 for the row containing the maximum value and 0 otherwise.
The '>0' condition turns the 1 into TRUE but, as far as I can see, is pointless; it is just as simple to search for the 1.
Since MATCH works with ranges or arrays
= MATCH( 1, COUNTIF( OFFSET(rng, k, 0, 1), MAX(rng) ), 0 )
will return the index of the array element containing the 1 and, hence, the number of the row containing MAX(Rng).