Function to return cell references (addresses) of N highest valuesExcel Formula: What is the equivalent of...

The Digit Triangles

Alignment of six matrices

Unable to disable Microsoft Store in domain environment

Why is the principal energy of an electron lower for excited electrons in a higher energy state?

Anime with legendary swords made from talismans and a man who could change them with a shattered body

Do I have to know the General Relativity theory to understand the concept of inertial frame?

Why do Radio Buttons not fill the entire outer circle?

Is there a RAID 0 Equivalent for RAM?

Should I assume I have passed probation?

Overlapping circles covering polygon

Air travel with refrigerated insulin

Should I warn new/prospective PhD Student that supervisor is terrible?

Can I cause damage to electrical appliances by unplugging them when they are turned on?

What (the heck) is a Super Worm Equinox Moon?

If the only attacker is removed from combat, is a creature still counted as having attacked this turn?

How were servants to the Kaiser of Imperial Germany treated and where may I find more information on them

Why the "ls" command is showing the permissions of files in a FAT32 partition?

When and why was runway 07/25 at Kai Tak removed?

Is there a distance limit for minecart tracks?

Is there anyway, I can have two passwords for my wi-fi

Ways of geometrical multiplication

What should be the ideal length of sentences in a blog post for ease of reading?

Why would five hundred and five be same as one?

How to test the sharpness of a knife?



Function to return cell references (addresses) of N highest values


Excel Formula: What is the equivalent of =MATCH(), but for multiple columns?Conditional formatting Excel 2007/2010: Highlight the first cell in the row that contains duplicate values?Formula to search for a value in a range of cellsUpdate conditional formatting cell references when conditional formatting is applied to new cellCell reference formula within formula to pick out a group of similar rowsAlternative to cell highlighting using conditional formatting in Excel without causing freeze/crashHighlight empty columnExcel conditional formatting linked cells blank formulaExcel Conditional Formatting based on Previous CellHow to apply the same conditional formatting to other cells?Conditional formatting for a blank cell not working when using a formual for multiple tabs in Excel 2010













0















I am making a spreadsheet to track election results and return the elected representatives. We are using a system of proportional representation, applying the modified Sainte-Laguë method. The election results are being fed into a spreadsheet, and then divided by a given number (1.4, 3, 5, etc., up to 15 for a total of eight quotients, as mandated by the applicable law) to get the quotient from which to distribute the seats. Using conditional formatting, the spreadsheet now highlights the eight highest numbers, showing who get what position. This all works as intended.



We need to a) return a list of the eight successful candidates, and b) have that list return the cell reference (e.g., D7), rather than the number. I have played around with using the LARGE function, and am successfully returning the eight top quotients in C24:C31 using a formula that is, essentially, =LARGE($C$2:$J$21, ROW()-23). I am not, however, able to get it to return the grid coordinates for these quotients.



The screenshot below shows a mock-up of what I want to achieve. The cells highlighted in green are highlighted as a result of conditional formatting;
they are the eight largest values in C2:J21. The cells highlighted in yellow are what I want to achieve. To be clear; the data in cell reference C2:J5 are generated by a formula, likewise the values in C24:C31 are the same eight values that are highlighted in green, above; they are formula generated. The hidden rows 6 through 21 are essentially duplicates of rows 1 through 5. I have uploaded the spreadsheet to Google Drive



Screenshot of the spreadsheet in question










share|improve this question




















  • 1





    By "grid coordinate", do you mean cell reference?

    – spikey_richie
    17 hours ago











  • please add some sample data with desired results, without that it's nearly impossible to suggest a working solution.

    – Máté Juhász
    17 hours ago






  • 1





    Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.

    – DavidPostill
    16 hours ago











  • Does that edit help? The table is correct, though the data is mocked up, as the election is a ways of as yet. I have uploaded the spreadsheet to Google Drive: drive.google.com/file/d/1b1uVYwVasfr-1OYpw7_b4LTdNaF2105Y/…

    – razumny
    16 hours ago
















0















I am making a spreadsheet to track election results and return the elected representatives. We are using a system of proportional representation, applying the modified Sainte-Laguë method. The election results are being fed into a spreadsheet, and then divided by a given number (1.4, 3, 5, etc., up to 15 for a total of eight quotients, as mandated by the applicable law) to get the quotient from which to distribute the seats. Using conditional formatting, the spreadsheet now highlights the eight highest numbers, showing who get what position. This all works as intended.



We need to a) return a list of the eight successful candidates, and b) have that list return the cell reference (e.g., D7), rather than the number. I have played around with using the LARGE function, and am successfully returning the eight top quotients in C24:C31 using a formula that is, essentially, =LARGE($C$2:$J$21, ROW()-23). I am not, however, able to get it to return the grid coordinates for these quotients.



The screenshot below shows a mock-up of what I want to achieve. The cells highlighted in green are highlighted as a result of conditional formatting;
they are the eight largest values in C2:J21. The cells highlighted in yellow are what I want to achieve. To be clear; the data in cell reference C2:J5 are generated by a formula, likewise the values in C24:C31 are the same eight values that are highlighted in green, above; they are formula generated. The hidden rows 6 through 21 are essentially duplicates of rows 1 through 5. I have uploaded the spreadsheet to Google Drive



Screenshot of the spreadsheet in question










share|improve this question




















  • 1





    By "grid coordinate", do you mean cell reference?

    – spikey_richie
    17 hours ago











  • please add some sample data with desired results, without that it's nearly impossible to suggest a working solution.

    – Máté Juhász
    17 hours ago






  • 1





    Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.

    – DavidPostill
    16 hours ago











  • Does that edit help? The table is correct, though the data is mocked up, as the election is a ways of as yet. I have uploaded the spreadsheet to Google Drive: drive.google.com/file/d/1b1uVYwVasfr-1OYpw7_b4LTdNaF2105Y/…

    – razumny
    16 hours ago














0












0








0








I am making a spreadsheet to track election results and return the elected representatives. We are using a system of proportional representation, applying the modified Sainte-Laguë method. The election results are being fed into a spreadsheet, and then divided by a given number (1.4, 3, 5, etc., up to 15 for a total of eight quotients, as mandated by the applicable law) to get the quotient from which to distribute the seats. Using conditional formatting, the spreadsheet now highlights the eight highest numbers, showing who get what position. This all works as intended.



We need to a) return a list of the eight successful candidates, and b) have that list return the cell reference (e.g., D7), rather than the number. I have played around with using the LARGE function, and am successfully returning the eight top quotients in C24:C31 using a formula that is, essentially, =LARGE($C$2:$J$21, ROW()-23). I am not, however, able to get it to return the grid coordinates for these quotients.



The screenshot below shows a mock-up of what I want to achieve. The cells highlighted in green are highlighted as a result of conditional formatting;
they are the eight largest values in C2:J21. The cells highlighted in yellow are what I want to achieve. To be clear; the data in cell reference C2:J5 are generated by a formula, likewise the values in C24:C31 are the same eight values that are highlighted in green, above; they are formula generated. The hidden rows 6 through 21 are essentially duplicates of rows 1 through 5. I have uploaded the spreadsheet to Google Drive



Screenshot of the spreadsheet in question










share|improve this question
















I am making a spreadsheet to track election results and return the elected representatives. We are using a system of proportional representation, applying the modified Sainte-Laguë method. The election results are being fed into a spreadsheet, and then divided by a given number (1.4, 3, 5, etc., up to 15 for a total of eight quotients, as mandated by the applicable law) to get the quotient from which to distribute the seats. Using conditional formatting, the spreadsheet now highlights the eight highest numbers, showing who get what position. This all works as intended.



We need to a) return a list of the eight successful candidates, and b) have that list return the cell reference (e.g., D7), rather than the number. I have played around with using the LARGE function, and am successfully returning the eight top quotients in C24:C31 using a formula that is, essentially, =LARGE($C$2:$J$21, ROW()-23). I am not, however, able to get it to return the grid coordinates for these quotients.



The screenshot below shows a mock-up of what I want to achieve. The cells highlighted in green are highlighted as a result of conditional formatting;
they are the eight largest values in C2:J21. The cells highlighted in yellow are what I want to achieve. To be clear; the data in cell reference C2:J5 are generated by a formula, likewise the values in C24:C31 are the same eight values that are highlighted in green, above; they are formula generated. The hidden rows 6 through 21 are essentially duplicates of rows 1 through 5. I have uploaded the spreadsheet to Google Drive



Screenshot of the spreadsheet in question







microsoft-excel worksheet-function






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 4 mins ago









Scott

16k113990




16k113990










asked 18 hours ago









razumnyrazumny

4024922




4024922








  • 1





    By "grid coordinate", do you mean cell reference?

    – spikey_richie
    17 hours ago











  • please add some sample data with desired results, without that it's nearly impossible to suggest a working solution.

    – Máté Juhász
    17 hours ago






  • 1





    Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.

    – DavidPostill
    16 hours ago











  • Does that edit help? The table is correct, though the data is mocked up, as the election is a ways of as yet. I have uploaded the spreadsheet to Google Drive: drive.google.com/file/d/1b1uVYwVasfr-1OYpw7_b4LTdNaF2105Y/…

    – razumny
    16 hours ago














  • 1





    By "grid coordinate", do you mean cell reference?

    – spikey_richie
    17 hours ago











  • please add some sample data with desired results, without that it's nearly impossible to suggest a working solution.

    – Máté Juhász
    17 hours ago






  • 1





    Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.

    – DavidPostill
    16 hours ago











  • Does that edit help? The table is correct, though the data is mocked up, as the election is a ways of as yet. I have uploaded the spreadsheet to Google Drive: drive.google.com/file/d/1b1uVYwVasfr-1OYpw7_b4LTdNaF2105Y/…

    – razumny
    16 hours ago








1




1





By "grid coordinate", do you mean cell reference?

– spikey_richie
17 hours ago





By "grid coordinate", do you mean cell reference?

– spikey_richie
17 hours ago













please add some sample data with desired results, without that it's nearly impossible to suggest a working solution.

– Máté Juhász
17 hours ago





please add some sample data with desired results, without that it's nearly impossible to suggest a working solution.

– Máté Juhász
17 hours ago




1




1





Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.

– DavidPostill
16 hours ago





Some diagrams or screen shots of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you.

– DavidPostill
16 hours ago













Does that edit help? The table is correct, though the data is mocked up, as the election is a ways of as yet. I have uploaded the spreadsheet to Google Drive: drive.google.com/file/d/1b1uVYwVasfr-1OYpw7_b4LTdNaF2105Y/…

– razumny
16 hours ago





Does that edit help? The table is correct, though the data is mocked up, as the election is a ways of as yet. I have uploaded the spreadsheet to Google Drive: drive.google.com/file/d/1b1uVYwVasfr-1OYpw7_b4LTdNaF2105Y/…

– razumny
16 hours ago










1 Answer
1






active

oldest

votes


















0














This is tricky. 
We can use


=MIN(IF($C$2:$J$21=value, ROW($C$2:$J$21)))

to find the number of the first row in which value appears. 
Likewise,
=MIN(IF($C$2:$J$21=value, COLUMN($C$2:$J$21)))

finds the number of the first column in which value appears. 
The above are array formulas.

If the top eight values in C2:J21 are unique,
we can use the above to find a value in that grid. 
Then


INDEX($A$1:$J$21, row_number, column_number)

will index that cell,
and we can use
CELL("address", INDEX(the above))

to get the row & column address of that cell.

So, enter


=CELL("address", INDEX($A$1:$J$21, MIN(IF($C$2:$J$21=C24,ROW($C$2:$J$21))), MIN(IF($C$2:$J$21=C24,COLUMN($C$2:$J$21)))))
into cell B24,
press Ctrl+Shift+Enter,
and drag/fill down to B31.

Notes:





  • CELL("address", …) returns an absolute address (with dollar signs).
    If you don’t want them, you can use
    =SUBSTITUTE(CELL(blah blah blah), "$", "")


  • This does not handle duplicate values well. 
    For example, if E3 also contained 8 (in addition to D4),
    it will report that they are both in D3 (first row and first column). 
    This may be very hard to fix, but it would be possible to test
    whether such a coincidence had occurred, to allow manual correction. 

  • While this can be done all in one cell,
    it might simplify your life in the long run
    if you use helper columns for the row and column values.


This answer is based partly on a trick from this answer by barry houdini.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1415623%2ffunction-to-return-cell-references-addresses-of-n-highest-values%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    This is tricky. 
    We can use


    =MIN(IF($C$2:$J$21=value, ROW($C$2:$J$21)))

    to find the number of the first row in which value appears. 
    Likewise,
    =MIN(IF($C$2:$J$21=value, COLUMN($C$2:$J$21)))

    finds the number of the first column in which value appears. 
    The above are array formulas.

    If the top eight values in C2:J21 are unique,
    we can use the above to find a value in that grid. 
    Then


    INDEX($A$1:$J$21, row_number, column_number)

    will index that cell,
    and we can use
    CELL("address", INDEX(the above))

    to get the row & column address of that cell.

    So, enter


    =CELL("address", INDEX($A$1:$J$21, MIN(IF($C$2:$J$21=C24,ROW($C$2:$J$21))), MIN(IF($C$2:$J$21=C24,COLUMN($C$2:$J$21)))))
    into cell B24,
    press Ctrl+Shift+Enter,
    and drag/fill down to B31.

    Notes:





    • CELL("address", …) returns an absolute address (with dollar signs).
      If you don’t want them, you can use
      =SUBSTITUTE(CELL(blah blah blah), "$", "")


    • This does not handle duplicate values well. 
      For example, if E3 also contained 8 (in addition to D4),
      it will report that they are both in D3 (first row and first column). 
      This may be very hard to fix, but it would be possible to test
      whether such a coincidence had occurred, to allow manual correction. 

    • While this can be done all in one cell,
      it might simplify your life in the long run
      if you use helper columns for the row and column values.


    This answer is based partly on a trick from this answer by barry houdini.






    share|improve this answer




























      0














      This is tricky. 
      We can use


      =MIN(IF($C$2:$J$21=value, ROW($C$2:$J$21)))

      to find the number of the first row in which value appears. 
      Likewise,
      =MIN(IF($C$2:$J$21=value, COLUMN($C$2:$J$21)))

      finds the number of the first column in which value appears. 
      The above are array formulas.

      If the top eight values in C2:J21 are unique,
      we can use the above to find a value in that grid. 
      Then


      INDEX($A$1:$J$21, row_number, column_number)

      will index that cell,
      and we can use
      CELL("address", INDEX(the above))

      to get the row & column address of that cell.

      So, enter


      =CELL("address", INDEX($A$1:$J$21, MIN(IF($C$2:$J$21=C24,ROW($C$2:$J$21))), MIN(IF($C$2:$J$21=C24,COLUMN($C$2:$J$21)))))
      into cell B24,
      press Ctrl+Shift+Enter,
      and drag/fill down to B31.

      Notes:





      • CELL("address", …) returns an absolute address (with dollar signs).
        If you don’t want them, you can use
        =SUBSTITUTE(CELL(blah blah blah), "$", "")


      • This does not handle duplicate values well. 
        For example, if E3 also contained 8 (in addition to D4),
        it will report that they are both in D3 (first row and first column). 
        This may be very hard to fix, but it would be possible to test
        whether such a coincidence had occurred, to allow manual correction. 

      • While this can be done all in one cell,
        it might simplify your life in the long run
        if you use helper columns for the row and column values.


      This answer is based partly on a trick from this answer by barry houdini.






      share|improve this answer


























        0












        0








        0







        This is tricky. 
        We can use


        =MIN(IF($C$2:$J$21=value, ROW($C$2:$J$21)))

        to find the number of the first row in which value appears. 
        Likewise,
        =MIN(IF($C$2:$J$21=value, COLUMN($C$2:$J$21)))

        finds the number of the first column in which value appears. 
        The above are array formulas.

        If the top eight values in C2:J21 are unique,
        we can use the above to find a value in that grid. 
        Then


        INDEX($A$1:$J$21, row_number, column_number)

        will index that cell,
        and we can use
        CELL("address", INDEX(the above))

        to get the row & column address of that cell.

        So, enter


        =CELL("address", INDEX($A$1:$J$21, MIN(IF($C$2:$J$21=C24,ROW($C$2:$J$21))), MIN(IF($C$2:$J$21=C24,COLUMN($C$2:$J$21)))))
        into cell B24,
        press Ctrl+Shift+Enter,
        and drag/fill down to B31.

        Notes:





        • CELL("address", …) returns an absolute address (with dollar signs).
          If you don’t want them, you can use
          =SUBSTITUTE(CELL(blah blah blah), "$", "")


        • This does not handle duplicate values well. 
          For example, if E3 also contained 8 (in addition to D4),
          it will report that they are both in D3 (first row and first column). 
          This may be very hard to fix, but it would be possible to test
          whether such a coincidence had occurred, to allow manual correction. 

        • While this can be done all in one cell,
          it might simplify your life in the long run
          if you use helper columns for the row and column values.


        This answer is based partly on a trick from this answer by barry houdini.






        share|improve this answer













        This is tricky. 
        We can use


        =MIN(IF($C$2:$J$21=value, ROW($C$2:$J$21)))

        to find the number of the first row in which value appears. 
        Likewise,
        =MIN(IF($C$2:$J$21=value, COLUMN($C$2:$J$21)))

        finds the number of the first column in which value appears. 
        The above are array formulas.

        If the top eight values in C2:J21 are unique,
        we can use the above to find a value in that grid. 
        Then


        INDEX($A$1:$J$21, row_number, column_number)

        will index that cell,
        and we can use
        CELL("address", INDEX(the above))

        to get the row & column address of that cell.

        So, enter


        =CELL("address", INDEX($A$1:$J$21, MIN(IF($C$2:$J$21=C24,ROW($C$2:$J$21))), MIN(IF($C$2:$J$21=C24,COLUMN($C$2:$J$21)))))
        into cell B24,
        press Ctrl+Shift+Enter,
        and drag/fill down to B31.

        Notes:





        • CELL("address", …) returns an absolute address (with dollar signs).
          If you don’t want them, you can use
          =SUBSTITUTE(CELL(blah blah blah), "$", "")


        • This does not handle duplicate values well. 
          For example, if E3 also contained 8 (in addition to D4),
          it will report that they are both in D3 (first row and first column). 
          This may be very hard to fix, but it would be possible to test
          whether such a coincidence had occurred, to allow manual correction. 

        • While this can be done all in one cell,
          it might simplify your life in the long run
          if you use helper columns for the row and column values.


        This answer is based partly on a trick from this answer by barry houdini.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 8 hours ago









        ScottScott

        16k113990




        16k113990






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1415623%2ffunction-to-return-cell-references-addresses-of-n-highest-values%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Why not use the yoke to control yaw, as well as pitch and roll? Announcing the arrival of...

            Couldn't open a raw socket. Error: Permission denied (13) (nmap)Is it possible to run networking commands...

            VNC viewer RFB protocol error: bad desktop size 0x0I Cannot Type the Key 'd' (lowercase) in VNC Viewer...