• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

SImplified Formula to speed up calculations

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
vegas86
Asked:
vegas86
1 Solution
 
byundtCommented:
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
 
vegas86Author Commented:
Thank you so much Byundt! i just applied the formula and its working, if I could give you more points I would!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now