Hey I have too many calculations in my spreadsheet, that would make sense anyway as it's really slow, is there a way I can make it run faster? Working on my 2016 finances and hope someone can help!
Graeme FINANCE - 2016.xlsx
here is the document and the password is "finance"
Thanks guys.
At least two of the Named Ranges reference whole columns .... 1048576 cells .... nearly all blanks. I noticed a SUMPRODUCT formula that references and multiplies that range. That is a lot of unnecessary calculation and will definitely slow a workbook down.
Reduce the size of all overly large ranges. If you are making room for expansion use an upper boundary row that will more than cover that but not overly large. For example if you anticipate using 10,000 rows in AmountReceived set your range to 12,000 rows or 15,000.
Last edited by FlameRetired; 12-21-2015 at 10:23 PM.
Dave
Agree with Dave on this - reduce your ranges to just what you need...or, say, 2-3 times what you think you will need, you are including over 1 million cells on those ranges...and in the calcs
Also, using SUMPRODCT can slow things down - especially when using such large ranges. Consider switching to maybe a sumifS() or countifS() function?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Ford,
Good point on the SUMPRODUCT thing. I had forgotten about that.
Dave
Dave, that's why I get paid the big bucks
LOL yeah right
For some reason, you appear to have a lot of blank columns up to column AMK. I have deleted the majority of these, which nearly halves the size of the file.
I have also converted most of the Named Ranges to Dynamic Named Ranges, which improves the calculation time because the SUMPRODUCT formulae are no longer referring to whole columns. The alternative would be to convert your data tables to Structured Tables.
See the attached updated example.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I don't think that I destroyed anything but I discovered that one of the worksheets had over 9000 rows with just a counter in column A. This is something that probably isn't needed as it is consecutive numbers that you can add as you make entries. Other than that I used the dynamic named ranges where you used whole column ranges. It would probably be advisable to change all the named ranges to be dynamic as I think TMS did.
For some reason, my version of the workbook seems to be quite small compared to the original. I used Microsoft's Excess Format cleaner to reset the data ranges.
Last edited by newdoverman; 12-22-2015 at 08:52 PM. Reason: I introduced a circular reference...corrected it.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Can't download attachment in #7:
vBulletin Message
Invalid Attachment specified. If you followed a valid link, please notify the administrator
Ben Van Johnson
You may have tried to download while I was swapping out the file for one that didn't have a circular reference that I had accidentally introduced. It should be ok now.
That happened to me too. Odd.... a circular reference that I had accidentally introduced.
I will upload it again in case something went wrong with the upload
Asking for a password?
Same password as the original file - finance
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks