Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create fixed formula (2003)

    Hi. I want to create a formula that will not change cell references when a column is inserted. Particularly, I want a formula '=AVERAGE(A1:B1)' entered into C1 that will not change when a new column A is inserted. (Dollar signs do not fix this problem). Is this possible? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create fixed formula (2003)

    You can use

    =AVERAGE(INDIRECT("A1:B1"))

    The INDIRECT function converts the string "A1:B1" to a range reference. Because "A1:B1" is a string, Excel will not adjust it when cells, rows or columns are inserted or deleted.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create fixed formula (2003)

    Thank you. I forgot to add that I want to then copy the formula down the column - which makes the problem a little trickier. Soz, Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create fixed formula (2003)

    Try this:
    <code>
    =AVERAGE(INDIRECT("A"&ROW()&":B"&ROW()))</code>

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create fixed formula (2003)

    Thanks, brill. Andy.

Posting Permissions

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