Leo Heuser wrote...
Harlan Grove said:
That so, how could you believe the formula construct
D2:
=MAX(Rng)
D3 and down:
=LARGE(Rng,SUMPRODUCT(COUNTIF(Rng,$D$2
2))+1)
would be as efficient as
D2:
=MAX(Rng)
D3 and down [array formulas]:
=MAX(IF(Rng<D2,Rng))
....
I'm not talking efficiency, but sometimes it's not possible
for the user to sort the list (or place it in descending order
as you put it). That's where my formula does the job.
Assume your formulas and mine were entered in D2
8 on different
worksheets.
Your formula, like mine above, only does the job in D3
8 because the
larger numbers from rng are already in the cells above. Both produce
the distinct values from rng in descending order.
If there were gaps in the cells containing these formulas, your formula
adapts so the remaining formulas would still give the distinct values
in descending order. Mine produces either zeros or the largest negative
value in rng. If that sort of robustness were needed,
D3 [array formula]:
=MAX(IF((COUNT(D$2
2)=0)+(rng<MIN(D$2
2)),rng))
On the other hand, if we need to consider D2
8 containing anything
else other than our formulas, if D4 contained the formula =D2, my
revised formula can handle it, but yours could return incorrect results
because your SUMPRODUCT(COUNTIF(..)) term would be overstated. (It
could also return fortuitously correct results if each distinct smaller
value had more duplicate instances than the overstatement in the
SUMPRODUCT(COUNTIF(..)) term.)
So robustness is in the eye of the beholder, and (to repeat) both sets
of formulas only work because they produce the distinct values in
descending order.
BTW how would you use your formula on a multiple row and column
range?
Reread my formula. It handles single area 2D ranges no differently than
1D ranges. Like yours, which relies on COUNTIF, it returns errors if
rng were multiple area or 3D.
If rng were a single area range not spanning an entire column (and I
suppose your formula could work if rng did span entire columns, but
it'd be REALLY SLOW), the term
IF(rng<SomeScalarValueHere,rng)
returns a 2D array of the values in rng. You do realize this, don't
you?
As for my original monster formula,
=LARGE(x,SMALL(IF(ROW(INDIRECT("1:"&COUNT(x)))=1,1,IF(LARGE(x,
ROW(INDIRECT("1:"&COUNT(x)))-1)>LARGE(x,ROW(INDIRECT("1:"&COUNT(x)))),
ROW(INDIRECT("1:"&COUNT(x))))), n ))
it could be used stand-alone without any dependencies on other
formulas, and it could be used on multiple area ranges or 3D references.
