find top 25 codes

  • Thread starter Thread starter Guest
  • Start date Start date
I find that the results differ when using your old and new formulas fo
the Top N list.

For example, if I enter 6 as the Top N value I get the followin
results:

Old formula [MAX(IF(.....]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110}

...which seems correct. The last value is included since it's tie
with the Top N value.

New formula [=COUNTIF(......]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110;1,100}

...which doesn't seem correct. I don't understand why that last valu
of 100 is included.

What am I missing?
 
You're right. I should have thought more about it before deciding to
switch. Thanks for looking at it.

The OP should use in F2 the original:

=MAX(IF(INDEX(B4:B11,MATCH(F1,D4:D11,0))=B4:B11,D4:D11))-F1

which must be confirmed with control+shift+enter instead of just
enter.

instead of the flawed:

=COUNTIF(B4:B11,LARGE(B4:B11,F1))-1
I find that the results differ when using your old and new formulas for
the Top N list.

For example, if I enter 6 as the Top N value I get the following
results:

Old formula [MAX(IF(.....]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110}

...which seems correct. The last value is included since it's tied
with the Top N value.

New formula [=COUNTIF(......]

{5,140;6,140;2,125;8,120;3,110;9,110;20,110;1,100}

...which doesn't seem correct. I don't understand why that last value
of 100 is included.

What am I missing?
Click to expand...
 
Please replace the formula in F2, which is flawed, with:

=MAX(IF(INDEX(B4:B11,MATCH(F1,D4:D11,0))=B4:B11,D4:D11))-F1

which you need to confirm with control+shift+enter instead of jus
enter.

Thanks to Domenic for keeping me to my original formula system.
 
Aladin,

Thanks for a very robust solution!

Aladin Akyurek said:
Please replace the formula in F2, which is flawed, with:

=MAX(IF(INDEX(B4:B11,MATCH(F1,D4:D11,0))=B4:B11,D4:D11))-F1

which you need to confirm with control+shift+enter instead of just
enter.

Thanks to Domenic for keeping me to my original formula system.
Click to expand...
 
Back
Top