Cutting down on recalculation time

bruty

Active Member
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?

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

dave3009

Well-known Member
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

Well-known Member
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

Active Member
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

Well-known Member
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

Active Member
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

Active Member
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.

Replies
13
Views
812
Replies
3
Views
935
Replies
13
Views
3K
Replies
3
Views
3K
Replies
0
Views
603

1,191,172
Messages
5,985,086
Members
439,940
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back