Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    I have two worksheets, in the same workbook. I enter data sporadically. In the first worksheet, after I enter data, I have to go to each of the statistical cells, select it, and then drag the outline down to the last data cell. In the second worksheet, I enter data sporadically; when Iíve entered the data, the statistical cells update themselves. I can see the cell pointer moving from cell to cell.

    Iíve looked at everything in the Options Tools menu for both worksheets, and I donít see a difference. What is different in the second worksheet?
    Iíve attached the worksheet that automatically updates.
    Attached Files Attached Files
    Bob Wall

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bob,

    I'm not sure but your problem may be your range definitions. If you don't insert a new row within the defined range it won't be included in calculations.

    I've attached a modified worksheet using the Offset function in the Statistical formulas. If you always insert a row at the blank row between the data and stats then enter your data it should always adjust properly and recalculate automatically.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts
    My worksheet does the automatic recalculation calculation without using the Offset function. My concern is why it is different from the other worksheet in the same workbook that doesnít automatically recalculate. Iíll try using your offset function on another small worksheet to see how it works.

    I went back to my other worksheet, the one that didnít do the auto recalc, and redid the stats formulae. Now it does the auto recalc.

    After experimenting with the auto recalc, I find that moving the stats to another column negates the auto recalc. When I bring the stats back to the original column, it doesnít reinstate the auto recalc. Moving the stats too far down, about thirty rows or so, in the same column cancels the auto recalc and it doesnít return when I move the stats back up in the column. If I enter a value at the bottom of the data, the auto recalc works, and if I delete the new value, the formula in the stat stays at the new cell address. When Iíve entered two new values, the auto recalc still works.
    Bob Wall

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bob,

    When you move your stats the references in the formula will adjust by the amount of distance you move them {relative referencing}, i.e. if the Average function is =Average(A7:A16) and you move it over one column it will automatically adjust to B7:B16. Likewise if you move it down the row numbers will adjust that is why if you move it far enough it no longer has any values in the relative number of rows to calculate with, i.e. in the example above you have a 10 row range so if you move the formula down one row you will only be calculating with 9 rows, move it down 10 rows and you're now using all blank cells!

    Now if you use Absolute referencing =Average($A$7:$A$16) you can move the function anywhere on the worksheet and it will still work.

    You might want to lookup "cell referencing" in the help file or google it for a more complete explanation.

    I hope this helps you understand what is happening when your move formula around.

    I also noticed in your formula on the sample sheet you included the blank row after your data in your formula, which is a good idea to make sure your ranges adjust when you insert rows. Did you do this on the sheet that had problems?

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bob,

    Attached is an improved version of the previous sheet I posted. With this version you don't have to bother to insert new rows just add new data on the bottom of the list the formula will automatically adjust. I keep track of my BP also so I kept working on this to make it better.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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