Large Sheet Locks Up; Auto Calc & AutoFill

  • Hello, all.

    I have a worksheet with about 55000 rows and 15 columns --10 of which contain formulas in nearly every row. When I open the workbook up with the Automatic calculation enabled, it Calculates on the status bar rather quickly: maybe a few seconds.

    At the bottom of the worksheet I need three rows: total, count, and average. When I try to AutoFill the total (SUM) and count (COUNT) formulas across the columns, everything works fine. But when I try to do the average and AutoFill (using either AVERAGE or SUBTOTAL), the status bar reads "Calculating 0%" and Excel locks up (stops responding). Also, if I try to copy an AVERAGE cell and then paste it, Excel locks up.

    Of course, if I set the Calculation to Manual, everything works fine. But my question is, why did I have no problems until I tried to calculate an average?

    Another question: Will changing the Iterations make any difference?

    Thanks for any help with this issue.

    Bubbis Thedog


  • Re: Large Sheet Locks Up; Auto Calc & AutoFill

    550,000 formulas is putting you at the limit of what Excel can do. Depending on the type of formulas these can be very taxing in themselves. Perhaps a fresh look at which formulas, and why.

    Check out this link on formula dependencies and trees.

    Decision Models

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    (retired Excel 2003 user, 3.28.2008)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!