joramastiff
New Member
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.
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.
Attachments

8.2 KB Views: 10