+ Reply to Thread
Results 1 to 4 of 4

Formula not being persistent when copied

  1. #1
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    298

    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

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula not being persistent when copied

    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

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,844

    Re: Formula not being persistent when copied

    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

  4. #4
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    298

    Re: Formula not being persistent when copied

    Quote Originally Posted by Moo the Dog View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Persistent decimal rounding
    By Trevasaurus in forum Excel General
    Replies: 4
    Last Post: 03-20-2013, 04:27 PM
  2. Making vlookup persistent
    By 91TwighlightGT in forum Excel General
    Replies: 15
    Last Post: 11-07-2011, 04:54 PM
  3. Persistent Formula / Graph
    By haysmj in forum Excel General
    Replies: 6
    Last Post: 11-18-2008, 10:38 AM
  4. [SOLVED] Array Data Not Persistent
    By Edd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2006, 02:47 PM
  5. [SOLVED] Persistent Data in a Combo Box
    By The Hawk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-30-2005, 06:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1