Formula not being persistent when copied

I have the following formula in column EY:

=AVERAGE(PERCENTRANK($EV$2:$EV$5000,EV3),PERCENTRANK($EW$2:$EW$5000,EW3),PERCENTRANK($EX$2:$EX$5000,EX3))


When I paste columns EV, EW, EX, and EY into another spreadsheet, in say column FF, the references stay the same (EV, EW, EX) when they should adapt and be referencing FC, FD, FE.

All the other formulas I use adapt to wherever I paste them, why doesn't this one? How do I fix it?

Thanks

That behavior is by design.
It's the absolute vs relative references.

Remove the $'s next to each column reference.
Then when you copy paste them, they will incriment as you expect them to.

Putting the $'s there, locks those references so they don't incriment when pasted/filled/dragged

Originally Posted by Moo the Dog

To add to Jonmo1's post,

If you only want the column references to adapt, just remove the $ next to the column references, NOT the row references.

For example, $EV$2:$EV$5000 --> EV$2:EV$5000

Doing so will keep the row references the same. This is CRUCIAL if you will be filling the formula down the columns!

- Moo

Works great thanks!