Tjyylli

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

microsoft-excel worksheet-function

share|improve this question

edited 4 mins ago

Scott

16k113990

asked 18 hours ago

razumnyrazumny

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
  

  
  
  
  

add a comment | 

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

microsoft-excel worksheet-function

share|improve this question

edited 4 mins ago

Scott

16k113990

asked 18 hours ago

razumnyrazumny

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
  

  
  
  
  

add a comment | 

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

microsoft-excel worksheet-function

share|improve this question

edited 4 mins ago

Scott

16k113990

asked 18 hours ago

razumnyrazumny

4024922

share|improve this question

edited 4 mins ago

Scott

16k113990

asked 18 hours ago

razumnyrazumny

4024922

share|improve this question

edited 4 mins ago

Scott

16k113990

asked 18 hours ago

razumnyrazumny

4024922

edited 4 mins ago

Scott

16k113990

edited 4 mins ago

Scott

16k113990

asked 18 hours ago

razumnyrazumny

4024922

asked 18 hours ago

razumnyrazumny

4024922

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

answered 8 hours ago

ScottScott

16k113990

add a comment | 

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

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

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

Name

Email

Required, but never shown

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

answered 8 hours ago

ScottScott

16k113990

add a comment | 

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

answered 8 hours ago

ScottScott

16k113990

add a comment | 

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

answered 8 hours ago

ScottScott

16k113990

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

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

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

CELL("address", INDEX(the above))

=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)))))

share|improve this answer

answered 8 hours ago

ScottScott

16k113990

answered 8 hours ago

ScottScott

16k113990

answered 8 hours ago

ScottScott

16k113990