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
Bookmarks