Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel 2010 Average Formula Changes

    We have a worksheet with 2 sets of 4 columns of numbers. We have a formula to average each set of 4 columns.

    The first average is =AVERAGE(E9:H9) in column M and the second average is in column N =AVERAGE(I9:L9).

    When we start typing numbers in columns e through h the average formula displays the correct average, but when we continue typing a number into column i, the average formula in column M changes to =AVERAGE(E9:I9). If you continue typing into j9, k9, and l9, the formula changes to =AVERAGE(E9:L9). The average formula in column N does not change.

    Our current work around is to insert a column to separate the two ranges for the formulas.

    Does anyone know why this is happening to the AVERAGE formulas? The SUM function will do the same thing.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In Excel Options, in the Advanced section, you need to uncheck the 'Extend data range formats and formulas' option.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks, Rory. I had hoped it was something easy.

Posting Permissions

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