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 in JQuery by Edureka
• 13,600 points
9 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 by gaurav
• 12,420 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,720 points
3,985 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 in JQuery by Edureka
• 13,600 points
12 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,720 points
637 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,720 points
5,385 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 in Others by Edureka
• 13,600 points
37 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 in Others by Edureka
• 13,600 points
26 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 in Others by Edureka
• 13,600 points
26 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 12,420 points
22 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 in JQuery by gaurav
• 12,420 points
6 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 in JQuery by gaurav
• 12,420 points
6 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP