# Excel slow: Referencing Empty Cells?

1. ## 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

2. ## 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

3. ## Re: Excel slow: Referencing Empty Cells?

thank you!!!!!

4. ## Re: Excel slow: Referencing Empty Cells?

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

5. ## 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.

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