Solved

# SImplified Formula to speed up calculations

Posted on 2011-04-20
194 Views
Hello,

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!!

example.xlsx
0
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.
0

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!
0

## Featured Post

### Suggested Solutions

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.

#### Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!