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. 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. 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?

Nov 7, 2022 in Others 212 views

## 1 answer to this question.

Placing \$ before the cell references makes it static. Try changing your formula to Rank(B11, B\$2:B\$20,1). Coping this formula will only change those references which are not proceeded with
• 23,220 points

## Excel sum formula without table - Equivalent to the Math Sum Symbol

You can use SUMPRODUCT for this. Assuming X is in cell B1, ...READ MORE

## How to lock data in excel sheet using POI, leaving cells without any data / the rest of the sheet unlocked

If the cells in a row have ...READ MORE

## Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

## Copy the Excel RANK formula without changing the end reference

You can select any cells, press Ctrl + ...READ MORE

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

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

## Convert Rows to Columns with values in Excel using custom format

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