Cutting down on recalculation time

[bruty]

I have the following formulae in a spreadsheet:

=IF(AND(AX3="",AY3=""),"",IF(AND(OR(AX3="Clear",AX3="N/A"),AY3="Clear"),"Yes","No"))

=IF(AND(BA3="",BE3=""),"",IF(BA3="No","No",IF(AND(BA3="Yes",BE3="Clear"),"Yes",IF(AND(BA3="Yes",BE3="Fail"),"No",""))))

=IF(AV3="","",IF(BH3="",TODAY()-AV3,BH3-AV3))

=IF(A3="","",IF(BH3="","No","Yes"))

=IF(BH3="","",IF(BA3="No","",BH3+90))

Unfortunately, these need to go down appoximately 10,000 lines and when the rest of the data is in it can be fairly slow to navigate due to all the recalculating.

Can anyone suggest an easy way of optimising these formulas and cutting down on recalculation?

 

[dave3009]

Hi There

My first suggestion would be to turn off automatic calculations

Tools>>Options>>Calculations>>Manual use F9 to calculate the sheet when required.

HTH

Dave

 

[njimack]

A couple of suggestions.

1. Either use a spare cell or a defined name containing "=TODAY". That way, your 3rd formula won't be volatile, which will reduce calculation time.

2. Your 5th formula could be condensed into =IF(OR(BH3="",BA3="No"),"",BH3+90)

Alternatively, go down the VBA route. This will probably be quicker, but it's impossible to say without having access to your data.

 

[bruty]

1. Either use a spare cell or a defined name containing "=TODAY". That way, your 3rd formula won't be volatile, which will reduce calculation time.

Brilliant. Cheers. I thought this was causing a problem but was having a mental block as to how to get around it.

2. Your 5th formula could be condensed into =IF(OR(BH3="",BA3="No"),"",BH3+90)

Cheers again - will this actually speed it up or is it just a cosmetic change?

Alternatively, go down the VBA route. This will probably be quicker, but it's impossible to say without having access to your data.

I'm trying to keep it as simple as possible and their are several people who are OK with formulas but none other than me that know anything about VBA (and I'd only class myself as a beginner). At least with the formulas, as soon as they key the data the results will show.

 

[njimack]

Quote:

2. Your 5th formula could be condensed into =IF(OR(BH3="",BA3="No"),"",BH3+90)

Cheers again - will this actually speed it up or is it just a cosmetic change?

It'll probably make a small difference purely because you've got one less IF formula. In a single formula the time-saving won't be seen, but multiply that by 10,000 and you should see a small difference. It should also make your file that bit smaller.

 

[bruty]

As an extra question, when I filter the spreadsheet these formulas are in, at the bottom of the screen it just says 'Filter Mode' instead of '330 out of 10000' or whatever the wording is.

Is there anyway to change this back, or is that impossible due to the calculations?

 

[bruty]

Quote:

2. Your 5th formula could be condensed into =IF(OR(BH3="",BA3="No"),"",BH3+90)

Cheers again - will this actually speed it up or is it just a cosmetic change?

It'll probably make a small difference purely because you've got one less IF formula. In a single formula the time-saving won't be seen, but multiply that by 10,000 and you should see a small difference. It should also make your file that bit smaller.

Cheers for all the help.