LARGE function does not segregate between identical max values
 

If a list containts two maximum numbers (say 5, 5) does it hold that
LARGE(RANGE,1) = LARGE(RANGE, 2)= 5?

I think it does and it's messing up my functions!!!

No way to overcome this I suppose...

Dennis wrote...
If a list containts two maximum numbers (say 5, 5) does it hold that
LARGE(RANGE,1) = LARGE(RANGE, 2)= 5?

I think it does and it's messing up my functions!!!

No way to overcome this I suppose...

That's the way the LARGE function (and the SMALL function) works.
However, if you want the n_th largest distinct number in range x, you
could use the following large and inefficient array 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 ))

Much more efficient is listing them in descending order. If the first,
the MAX, were in cell E1, try the following formulas.

E1:
=MAX(x)

E2 [array formula]:
=MAX(IF(x<E1,x))

Fill E2 down as far as needed.

THANKS! :)

BUT ...if there are *no* blanks in the range, try this *array* formula:

=LARGE(IF(ROW(range)=MATCH(range,range,0),range),n )

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Dennis" wrote in message
ups.com...
THANKS! :)

Dennis

To get a list without having to sort first, try this setup:


In e.g. D2
=Max(Rng)

In D3:
=LARGE(Rng,SUM(COUNTIF(Rng,$D$2:D2))+1)

D3 to be entered with <Shift<Ctrl<Enter, also if edited later.

Copy D3 down as far as necessary.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dennis" skrev i en meddelelse
ups.com...
THANKS! :)

Or
=LARGE(Rng,SUMPRODUCT(COUNTIF(Rng,$D$2:D2))+1)
not array entered, but entered "normally" with <Enter

LeoH

Leo Heuser wrote...
Or
=LARGE(Rng,SUMPRODUCT(COUNTIF(Rng,$D$2:D2))+1)
not array entered, but entered "normally" with <Enter

How do you believe LARGE works? It needs to sort the numeric values fed
to it, so using it you can't avoid sorting.

That so, how could you believe the formula construct

D2:
=MAX(Rng)

D3 and down:
=LARGE(Rng,SUMPRODUCT(COUNTIF(Rng,$D$2:D2))+1)

would be as efficient as

D2:
=MAX(Rng)

D3 and down [array formulas]:
=MAX(IF(Rng<D2,Rng))

?

RagDyer wrote...
BUT ...if there are *no* blanks in the range, try this *array* formula:

=LARGE(IF(ROW(range)=MATCH(range,range,0),range), n)
....

Unstated assumption: range is a single column, multiple row range
beginning in row 1. Otherwise the ROW and MATCH calls won't correspond.
If range were multiple row and column, your approach won't work at all.

Unstated assumption: range is *not* a single column, multiple row range,
starting in row1.<g

Harlan, are those hoof beats outside your door horses or zebras?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Harlan Grove" wrote in message
oups.com...
RagDyer wrote...
BUT ...if there are *no* blanks in the range, try this *array* formula:

=LARGE(IF(ROW(range)=MATCH(range,range,0),range), n)
...

Unstated assumption: range is a single column, multiple row range
beginning in row 1. Otherwise the ROW and MATCH calls won't correspond.
If range were multiple row and column, your approach won't work at all.

"Harlan Grove" skrev i en meddelelse
ups.com...

That so, how could you believe the formula construct

D2:
=MAX(Rng)

D3 and down:
=LARGE(Rng,SUMPRODUCT(COUNTIF(Rng,$D$2:D2))+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.

BTW how would you use your formula on a multiple row and column
range?

Leo Heuser wrote...
"Harlan Grove" skrev i en meddelelse
That so, how could you believe the formula construct

D2:
=3DMAX(Rng)

D3 and down:
=3DLARGE(Rng,SUMPRODUCT(COUNTIF(Rng,$D$2:D2))+ 1)

would be as efficient as

D2:
=3DMAX(Rng)

D3 and down [array formulas]:
=3DMAX(IF(Rng<D2,Rng))
..=2E.
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:D8 on different
worksheets.

Your formula, like mine above, only does the job in D3:D8 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]:
=3DMAX(IF((COUNT(D$2:D2)=3D0)+(rng<MIN(D$2:D2)),rn g))

On the other hand, if we need to consider D2:D8 containing anything
else other than our formulas, if D4 contained the formula =3DD2, 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,

=3DLARGE(x,SMALL(IF(ROW(INDIRECT=AD("1:"&COUNT(x)) )=3D1,1,IF(LARGE(=ADx,
ROW(INDIRECT("1:"&COUNT(x)))-1=AD)LARGE(x,ROW(INDIRECT("1:"&CO=ADUNT(x)))),

ROW(INDIRECT("1:"&COUNT(x)))))=AD, 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.

"Harlan Grove" skrev i en meddelelse
oups.com...
Leo Heuser wrote...
"Harlan Grove" skrev i en meddelelse
That so, how could you believe the formula construct

D2:
=MAX(Rng)

D3 and down:
=LARGE(Rng,SUMPRODUCT(COUNTIF(Rng,$D$2:D2))+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.

I understood your

"Much more efficient is listing them in descending order. If the first,
the MAX, were in cell E1, try the following formulas"

as if the user was to put his *datacolumn* in descending order.
I now see, that you are talking about the *result* list, so you are
right. My mistake, and my formula is inferior.