Too many calculations

[Kazman]

My spreadsheet has grown so large that it is doing too many calculations (many of which are just true/false helpers). These are slowing it WAY down.

Is there a way for it not proceed with calculations when the next row is blank?

 

[MrKowz]

You can turn off automatic calculation by going to Tools>Options>Calculation and changing it to manual.

However, there may be a way to optimize your spreadsheet with more effecient formulas. Can you provide any examples of formulas you are using, your input, and desired return?

 

[Kazman]

Well I have a nasty chain to deterimine quarterly results based on different criteria... I'm sure there is an easier way to do this. Here is where the information is based off of

Main!
B:B contains dates
C:C instructor
D:D types of hours (see metrics below)
E:E course code
F:F hours

I'm showing metrics by instructor. On the Metrics! sheet, an instructor is chosen in a drop down and then it fills in the following blocks by countiif calculations from Main!F:F (these are all drop drown items in Main!D:D)

formal classes
substitute teaching
informal classes
host support
additional duty

Now to further complicate things, I must break each down by calendar quarter.... to do this I have a helper sheet called MainSupport!

On this sheet i have a true/false column (using IF formulas) for the following <deep breath>:
hours types (5 diff columns)
instructor match (does Main!C:C match the instrucor chosen in the pulldown)
then I have =IF(Main!B:B="",FALSE,ROUNDUP(MONTH(Main!B:B)/3,0)) to determine the quarter
then i have =IF(D:D=TRUE,IF(F:F=TRUE,IF(N:N=1,TRUE,FALSE)))... this asks "if the instrutor matches, check if it was a formal course, then check if it happened in quarter 1"
**I have one of these for each quarter AND each 4 quarters is repeated 5 times (once each for each type of hours)**


I have

 

[MrKowz]

It looks like you might be able to consolidate a LOT of this using some SUMPRODUCT formulas (they can do multi-condition counting and summing, even complex conditions such as determining the quarter internal to the formula).

Would you be able to provide some dummy data, along with your desired outcome and logic for that data? I'll see what I can come up with.