SImplified Formula to speed up calculations

Posted on 2011-04-20
Last Modified: 2012-05-11

Can someone please have a look at my spreadsheet and let me know if there is a way to simplify my formulas on the ‘layout’ tab.
I wouldn’t usually mind but as my sheet grows, so does the calculation times.

I have attached a very basic example of what I have, except with the main sheet I am currently up to row 9565 on the ‘layout’ tab and it is becoming increasingly frustrating to try to work with.

Any suggestions will be greatly appreciated!!

Question by:vegas86
    LVL 80

    Accepted Solution

    INDIRECT is slowing down your spreadsheet recalc time because every formula containing it must be recomputed every time anything changes on any open worksheet.

    If you want fast calculations, put all of your data on a single worksheet. You will need to add a column for the fiscal year. You can then produce your report with a simple SUMIFS formula like:
    =SUMIFS(Data!E:E,Data!A:A,layout!$B4 & "-" & layout!$C4,Data!F:F,RIGHT(E$2,5))
    This formula assumes that the fiscal year is in column F of worksheet Data. Columns A through E of worksheet data are the same as worksheet 05-06 and subsequent.

    Author Closing Comment

    Thank you so much Byundt! i just applied the formula and its working, if I could give you more points I would!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now