Bad performance using formulas

[antexity]

I am finding my spreadsheet very slow from adding a small amount of records to a large amount. There may be better ways to write a formula or an entirely different concept. See attached, I know the trend tabs are a huge performance hit, but unsure how to lay out the data in another way instead of laying it out the way I did it to get the graph that I am after. There are a few arrays everywhere and all are set to 25000 for maintenance free.

Any help is appreciated.
Thank you so much.
Bill

[AlKey]

Hi antexity and welcome to the forum.

I think when it comes to large data sets, one should turn their attention to using other tools that are more efficient in handling large volume of data. Using MS Access database to store all your tables and queries and then export them into excel for data analysis is a much better way of doing things.

Just a thought.

Alex

[antexity]

Thanks Alex, If we will start getting even more complex, then that will be my next step. Access came across my mind a couple of times throughout this development. Just hoping someone with good dev skills can rewrite the formula's or organize the data in a way to be more efficient.

Thanks for your reply.

[AlKey]

You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[FDibbins]

Another option to look at is to create helper colunms that will let you use regular formulas instead of those array formulas you have, that can become resource-hogs as well

[TMS]

Using 25,000 may mean maintenance free, but it also means you are multiplying 25,000 cells against 25,000 cells for however many columns you include in your array formula. That's why the workbook is slow.

You would be better off creating Dynamic Named Ranges or, even better, converting your data to a Structured Table and Excel will manage the ranges for you.

How much data is there currently (in terms of rows and columns)? And how quickly does it grow? 25,000 rows will NOT be enough eventually, but is that next week, next month, next year, ... five years?

[antexity]

Using 25,000 may mean maintenance free, but it also means you are multiplying 25,000 cells against 25,000 cells for however many columns you include in your array formula. That's why the workbook is slow.

You would be better off creating Dynamic Named Ranges or, even better, converting your data to a Structured Table and Excel will manage the ranges for you.

How much data is there currently (in terms of rows and columns)? And how quickly does it grow? 25,000 rows will NOT be enough eventually, but is that next week, next month, next year, ... five years?

Thanks for the reply, we want to keep an average 1 year and 2 months of data and we will purge the rest when it ages past that month. We are starting with March 2015 with an average of 60 records per day (21960 for a year approx). Right now we have about 2000 records.

[antexity]

Another option to look at is to create helper colunms that will let you use regular formulas instead of those array formulas you have, that can become resource-hogs as well

Thanks for the advice. I have used helper columns for dates but unsure where else I can use helper columns. Ill keep this in mind.

Thank you

[FDibbins]

You said you attached a WB, but I dont see it...if you attach it again, we may be able to simplify some of your formulas.

Another option (that I have used in the past), is if the historic data is static/unchanging, then forumulas that use only that data could be converted to values?

[TMS]

@Ford: there WAS a workbook. It is very, very slow. Been playing a little but there are a lot of Array Formulae with several columns so it is very long winded calculating.

[TMS]

This is the updated workbook with the Error Report Data converted to a Structured Table. Superfluous lines in the Error Report Data sheet have been removed. Because this is a Structured Table, new lines added to the bottom of the list will be automatically incorporated into the Table and any formulae, formatting, conditional formatting and data validation will be propagated.

The formulae have all been modified to use either formal Structured Table references, or (Dynamic) Named Ranges based on the Structured Table references.

It's still a little slow on my (now elderly) laptop but it is manageable. The %age calculated goes up 10s rather than 1s with large pauses in between.

I don't think that the number of records is an issue at present and hence a database probably would not be the answer. The problem is the number of complex array formulae and SUMPRODUCT formulae throughout the workbook. This was exacerbated by using a row count of 25,000 (give or take) everywhere. The workbook size isn't huge, but reduced 75 kb from the original.

As an aside, if you choose to move to an Access Database for your raw data, you will have the cost of purchasing or subscribing to the product plus a huge learning curve (in my opinion). And, if you are still pulling the data into Excel to analyse it, you may still incur calculation overheads.

Anyway, interesting exercise. See if it's an improvement for you. If not, well, I tried


Regards, TMS

[FDibbins]

@Ford: there WAS a workbook. .

I figured there was 1, but that it had been removed

[TMS]

I have it if you want it

[FDibbins]

I started looking at it (your uploaded copy), intending to see if helpers might - well - help, but that got so involved, I gave up (plus, getting a head-cold, so feeling "foggy" lol)