Hi, i have a complex rank formula which i use below, however i need to use this same formula for different ranges and rather than keep changing the formula i need to be able to copy and paste it and it will automatically update the row range. I've put those in bold, so essentially i want a range 24 rows long, but then ability to copy that formula and use it elsewhere which is also 24 rows long, any ideas?
=RANK(EF421,$EF$421:$EF$440)+SUMPRODUCT(--($EF$421:$EF$440=EF421),--($EE$421:$EE$440>EE421))+SUMPRODUCT(--($EF$421:$EF$440=EF421),--($EE$421:$EE$440=EE421),--($EA$421:$EA$440>EA421))
=RANK(EF421,$EF$421:$EF$440)+SUMPRODUCT(--($EF$421:$EF$440=EF421),--($EE$421:$EE$440>EE421))+SUMPRODUCT(--($EF$421:$EF$440=EF421),--($EE$421:$EE$440=EE421),--($EA$421:$EA$440>EA421))