SOLVED

Excel large, n=2 and n=3 return the same value.

%3CLINGO-SUB%20id%3D%22lingo-sub-1588688%22%20slang%3D%22en-US%22%3EExcel%20large%2C%20n%3D2%20and%20n%3D3%20return%20the%20same%20value.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588688%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20trying%20to%20make%20a%20top%203%20liste.%20Using%20Large%20function.%20I%20dont%20understand%20why%20the%20second%20largest%2C%20and%20the%20third%20largest%20return%20the%20same%20value%20from%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attched%20workbok%20for%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1588688%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588709%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20large%2C%20n%3D2%20and%20n%3D3%20return%20the%20same%20value.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588709%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGeig%2C%20LARGE%20doesn't%20ignore%20duplicates.%20You%20may%20do%20something%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20458px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212396i5601B7FF12610F31%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588745%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20large%2C%20n%3D2%20and%20n%3D3%20return%20the%20same%20value.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DLARGE(SORT(UNIQUE(t_ordre%5BOmsetning%5D)%2C%2C-1)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588853%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20large%2C%20n%3D2%20and%20n%3D3%20return%20the%20same%20value.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorting%20would%20also%20return%20a%20suitable%20list.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20INDEX(%20%0A%20%20%20%20SORTBY(t_ordre%2C%20t_ordre%5BOmsetning%5D%2C-1)%2C%20%0A%20%20%20%20Nummer%2C%20%0A%20%20%20%20%7B4%2C1%7D%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20'Nummer'%20refers%20to%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20SEQUENCE(12)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588760%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20large%2C%20n%3D2%20and%20n%3D3%20return%20the%20same%20value.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Byes%2C%20that's%20better%20solution%2C%20I%20just%20mapped%20pre-DA%20approach%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi,

 

I was trying to make a top 3 liste. Using Large function. I dont understand why the second largest, and the third largest return the same value from the list.

 

Please see the attched workbok for an example.

 

Best Regards

- Geir

12 Replies

@Geir Hogstad 

Geig, LARGE doesn't ignore duplicates. You may do something like

image.png

 

best response confirmed by Geir Hogstad (Regular Contributor)
Solution

@Geir Hogstad 

Try this:

=LARGE(SORT(UNIQUE(t_ordre[Omsetning]),,-1),1)

@Detlef Lewin yes, that's better solution, I just mapped pre-DA approach

@Geir Hogstad 

Sorting would also return a suitable list.

= INDEX( 
    SORTBY(t_ordre, t_ordre[Omsetning],-1), 
    Nummer, 
    {4,1}
  )

where 'Nummer' refers to

= SEQUENCE(12)

 

@Detlef Lewin @Sergei Baklan Thank you both of you. I guessed Unique would solved it. But now I know how Large works. 

 

Have a nice weekend. 

- Geir

@Sergei Baklan 

My preference is to give up non-DA development altogether.

The last resort is to charge the client an extra grand and provide a computer and 365 license to go with the solution workbook!

@Peter Bartholomew 

Look, with millions of users who don't want or can't afford to buy new computers and licenses we always have to find some compromise. Excel is the behemoth in software world with it's own rules of behaviour. 

@Sergei Baklan 

I am not suggesting that others should follow the same path.   I came to dislike the idea of direct cell referencing in principle since, to me, the location of a value or formula on a sheet has no bearing as to its significance.  I had gravitated to using named ranges and CSE array formulas most of the time despite the fact that Excel was heavily optimised for a far more interactive way of working.

 

Now with DA I am free to think and work in a manner that has always been more natural for me; after all, my day job was entirely built on 'matrix methods'.  Equally, I accept that many of the financial modellers I meet prefer to copy individual values repeatedly so that they are close to the each formula that references them; parts of the Standard are 'Do not use Names' and 'Do not use Array Formulas'.

 

They are perfectly free to go their way, whilst I (thankfully) depart in an entirely different direction.  There are many 'gurus' that can support traditional working but few, like you, that can do both.

@Peter Bartholomew 

You are right. But sometimes it's quite hard to shift from my own environment with LET(), names, etc to shared environment. I always have to think is this or that available to other people, and, even if available, have they any glue about that.

Another point, having some patterns collected for years, not always think that it could be done another way using new possibilities. Such communications help a lot.

Finally, we are all human being and do lot of mistakes. One more, one less, but we do. Again, forum helps to learn on my own on other people mistakes.

@Sergei Baklan 

I confess, there have been times when I wonder whether I am helping or causing more confusion when I post a solution that I know will appear totally alien to almost all Excel users.  In addition, I do not think I have reached a balanced view concerning the limits of how deep a LET formula should be taken.

 

I think I had a similar dilemma when I first realised that Named formulas could be nested one within the other several deep.  It was both liberating (array formulas without the CSE) but, at the same time, it didn't seem entirely right to strike out on such a lone path (even if it did enable me to translate numerical currency values into natural language (I tried English and German).  It worked fine, but it was even questionable whether MS had any obligation to support such use.

 

I did get around to publishing some initial thoughts on dynamic arrays and presented the paper last year.  Since then, Charles Williams, whom I assume you know, included ACCUMULATE in FastExcel.

 

@Geir Hogstad If this exchange has moved too far from your initial question, do not hesitate to bring the discussion back on topic.

@Peter Bartholomew , thanks for the sharing. Will read over weekend, bit tired now for such things.

 

Have a nice weekend.

@Peter Bartholomew , that's a great paper. Can't say I fully agree with everything, but hope I catch your point.