Discussion Re: Excel large, n=2 and n=3 return the same value. in Excel
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588709#M71988
<P><LI-USER uid="9195"></LI-USER> </P>
<P>Geig, LARGE doesn't ignore duplicates. You may do something like</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 458px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/212396i5601B7FF12610F31/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P> </P>Fri, 14 Aug 2020 10:05:45 GMTSergei Baklan2020-08-14T10:05:45ZExcel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588688#M71987
<P>Hi,</P><P> </P><P>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.</P><P> </P><P>Please see the attched workbok for an example.</P><P> </P><P>Best Regards</P><P>- Geir</P>Fri, 14 Aug 2020 09:44:16 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588688#M71987Geir Hogstad2020-08-14T09:44:16ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588709#M71988
<P><LI-USER uid="9195"></LI-USER> </P>
<P>Geig, LARGE doesn't ignore duplicates. You may do something like</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 458px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/212396i5601B7FF12610F31/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P> </P>Fri, 14 Aug 2020 10:05:45 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588709#M71988Sergei Baklan2020-08-14T10:05:45ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588745#M71991
<P><LI-USER uid="9195"></LI-USER> </P><P>Try this:</P><LI-CODE lang="applescript">=LARGE(SORT(UNIQUE(t_ordre[Omsetning]),,-1),1)</LI-CODE>Fri, 14 Aug 2020 10:15:23 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588745#M71991Detlef Lewin2020-08-14T10:15:23ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588760#M71993
<P><LI-USER uid="1639"></LI-USER> yes, that's better solution, I just mapped pre-DA approach</P>Fri, 14 Aug 2020 10:21:19 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588760#M71993Sergei Baklan2020-08-14T10:21:19ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588853#M71999
<P><LI-USER uid="9195"></LI-USER> </P><P>Sorting would also return a suitable list.</P><LI-CODE lang="excel">= INDEX(
SORTBY(t_ordre, t_ordre[Omsetning],-1),
Nummer,
{4,1}
)</LI-CODE><P>where 'Nummer' refers to</P><LI-CODE lang="excel">= SEQUENCE(12)</LI-CODE><P> </P>Fri, 14 Aug 2020 11:18:42 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588853#M71999Peter Bartholomew2020-08-14T11:18:42ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588858#M72001
<P><LI-USER uid="1639"></LI-USER> <LI-USER uid="521"></LI-USER> Thank you both of you. I guessed Unique would solved it. But now I know how Large works. </P><P> </P><P>Have a nice weekend. </P><P>- Geir</P>Fri, 14 Aug 2020 11:20:53 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588858#M72001Geir Hogstad2020-08-14T11:20:53ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588862#M72002
<P><LI-USER uid="521"></LI-USER> </P><P>My preference is to give up non-DA development altogether.</P><P>The last resort is to charge the client an extra grand and provide a computer and 365 license to go with the solution workbook!</P>Fri, 14 Aug 2020 11:27:08 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1588862#M72002Peter Bartholomew2020-08-14T11:27:08ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589332#M72028
<P><LI-USER uid="214174"></LI-USER> </P>
<P>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. </P>Fri, 14 Aug 2020 15:57:18 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589332#M72028Sergei Baklan2020-08-14T15:57:18ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589429#M72032
<P><LI-USER uid="521"></LI-USER> </P><P>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.</P><P> </P><P>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'.</P><P> </P><P>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.</P>Fri, 14 Aug 2020 16:40:18 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589429#M72032Peter Bartholomew2020-08-14T16:40:18ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589554#M72040
<P><LI-USER uid="214174"></LI-USER> </P>
<P>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.</P>
<P>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.</P>
<P>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.</P>Fri, 14 Aug 2020 17:58:45 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589554#M72040Sergei Baklan2020-08-14T17:58:45ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589606#M72043
<P><LI-USER uid="521"></LI-USER> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P><LI-USER uid="9195"></LI-USER> If this exchange has moved too far from your initial question, do not hesitate to bring the discussion back on topic.</P>Fri, 14 Aug 2020 18:41:33 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1589606#M72043Peter Bartholomew2020-08-14T18:41:33ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1590008#M72061
<P><LI-USER uid="214174"></LI-USER> , thanks for the sharing. Will read over weekend, bit tired now for such things.</P>
<P> </P>
<P>Have a nice weekend.</P>Fri, 14 Aug 2020 21:34:03 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1590008#M72061Sergei Baklan2020-08-14T21:34:03ZRe: Excel large, n=2 and n=3 return the same value.
https://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1600033#M72507
<P><LI-USER uid="214174"></LI-USER> , that's a great paper. Can't say I fully agree with everything, but hope I catch your point.</P>Wed, 19 Aug 2020 22:42:42 GMThttps://techcommunity.microsoft.com/t5/excel/excel-large-n-2-and-n-3-return-the-same-value/m-p/1600033#M72507Sergei Baklan2020-08-19T22:42:42Z