Copy the Excel RANK formula without changing the end reference

0 votes

Suppose I have a simple spreadsheet with 3 rows of data that I want to rank in separate columns. The example I will use is simple, but my actual dataset is 12k + rows. In this simple example, I want to use the RANK formula from Excel to do this. To rank the values in column Police, I'll use the formula =RANK(B2, B2:B11, 1), with B2:B11 being the range.

enter image description here

As I mentioned, my actual dataset has thousands of rows and many more columns to compare. Even in this example, I want a simple way to copy the formula to all of the other _RANK column cells. If I simply copy the cell to the other cells, +1 gets added to the cell value. This is what I want to happen, EXCEPT for the ending cell of the range.

enter image description here

As you can see above, this is incorrect. The formula gets set to =RANK(B11,B11:B20,1) for cell E11, when what I want is =RANK(B11,B11:B11,1). How can I easily copy this formula across multiple cells so that it is has the correct formula?

Jun 8, 2022 in JQuery by Edureka
• 13,690 points
1,094 views

1 answer to this question.

0 votes

You can select any cells, press Ctrl + C to copy them, select the top cell in the current worksheet where you want to paste the formulas, and press Ctrl + V. Since Excel does not interpret the formulas without the equal sign as formulas, they will be copied exactly, without changing references.

answered Jun 9, 2022 by gaurav
• 23,260 points

Related Questions In JQuery

0 votes
1 answer

Error:jquery each loop return false not end the function

Hii @kartik, Returning from one function doesn't call as it's ...READ MORE

answered Jun 2, 2020 in JQuery by Niroj
• 82,880 points
7,714 views
0 votes
1 answer

Smooth scroll without the use of jQuery

document. querySelector('your-element'). scrollIntoView({behavior: 'smooth'}); If you want to scroll top ...READ MORE

answered Jun 1, 2022 in JQuery by Edureka
• 13,690 points
479 views
0 votes
1 answer

How can I implement my own $(document).ready functionality without using jQuery?

Hello @kartik,  There are three options: If script is the last ...READ MORE

answered Apr 28, 2020 in JQuery by Niroj
• 82,880 points
1,606 views
0 votes
1 answer

What is the equivalent of jQuery .hide() to set visibility: hidden

Hello Kartik, There isn't one built in but ...READ MORE

answered Apr 28, 2020 in JQuery by Niroj
• 82,880 points
14,811 views
0 votes
1 answer

Copy the Excel RANK formula without changing the end reference

Placing $ before the cell references makes ...READ MORE

answered Nov 8, 2022 in Others by gaurav
• 23,260 points
533 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,549 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,880 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
930 views
0 votes
1 answer

Calculate the number of days between a cell and today in excel?

Excel Formula to Calculate Number of Days ...READ MORE

answered Jun 9, 2022 in JQuery by gaurav
• 23,260 points
516 views
0 votes
1 answer

MS Excel TRUE/FALSE Formula Not Returning Expected Result

There are two main reasons you might ...READ MORE

answered Jun 10, 2022 in JQuery by gaurav
• 23,260 points
396 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP