+ Reply to Thread
Results 1 to 5 of 5

Excel slow: Referencing Empty Cells?

  1. #1
    Registered User
    Join Date
    07-13-2009
    Location
    Reno
    MS-Off Ver
    Excel 2007
    Posts
    3

    Excel slow: Referencing Empty Cells?

    I have set up a spreadsheet in order to track and calculate the number of students we teach at our organization. It is running prohibitively slow when entering new data.

    On one sheet each individual session is entered (the school, program, date, number of hours etc.) This is the Session worksheet.

    On the next sheet(the Program worksheet) the maximum number of students for each unique program (this is usually a combination of school and the program) is calculated to ensure we are not counting students we teach weekly as new students.
    This is calculated using a formula: :{=MAX(IF(Session!G:G=Program!A2,Session!D:D))}.
    "Session!G:G" = the unique program name on the Session worksheet.
    "Program!A2" = the unique program name on the Program worksheet.
    "Session!D:D" = the number of students in that individual session.

    Basically it searches the session worksheet for any entries that are part of that unique program and draws the maximum number of students from those ones.

    The last sheet has the total number of students and the total for each general program (we have about 9), which is calculated using a sumif function from the numbers on the Program sheet?

    I want to ensure the spreadsheet is expandable as we teach more classes, so I don't want to reference a static range of cells with each formula. Is it being slow because it is searching those empty cells when calculating each formula?

    or

    Is it just too many conditional formulas (there are 65 unique programs) and it takes a long time to calculate as new data is added (this seems more likely)?

    If it is the first one, how do I fix it and still allow more data to be added?

    If it is the 2nd, any suggestions?

    Thanks
    Last edited by therealdoctor; 07-13-2009 at 06:43 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel slow: Referencing Empty Cells?

    I want to ensure the spreadsheet is expandable as we teach more classes, so I don't want to reference a static range of cells with each formula.
    Use dynamic ranges. See, for example, http://www.contextures.com/xlNames01.html
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-13-2009
    Location
    Reno
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Excel slow: Referencing Empty Cells?

    thank you!!!!!

  4. #4
    Registered User
    Join Date
    07-13-2009
    Location
    Reno
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Excel slow: Referencing Empty Cells?

    Just got all the ranges named and working, and it is flying. Thank you.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel slow: Referencing Empty Cells?

    Good job. Dynamic ranges are a very powerful feature of Excel.

    The next thing to learn, if you haven't already, is to define them with the non-volatile INDEX function instead of the volatile OFFSET.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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