Excel takes over an hour to calculate !!!

I am trying to 'graph' various traffic data in excel.
Unfortunately I cannot post all of my workbook as it is over 10mb. I even had to disconnect the sheet with pivot tables and 'ultimately' the graph I am trying to achieve. (I have attached a screenshot to try and show pivot tables and chart etc)

My data source is a text file which I import and is usually around 120,000 rows, which can take forever to 'calculate'.

Richard Buttery very kindly offered me a solution to an issue I was having generating 85th percentile data in my chart (Richards work is highlighted in yellow).

However, having done some research it seems that using COUNTIF(S) on a large amount can have performance issues (people seem to be annoyed at 15-20 seconds) so an hour seems a tad excessive and I'm not convinced that this is the root cause of my issues.

Clearly there is something not right with the way I have constructed my 'report'.

dynamic traffic chart.xlsxChart Sheet.JPG

Wonder if anyone can give performance optimising advice?

Many thanks

there is not enought data in the file to advice
do you have smth like =date() in it? if Yes then OFFSET is not the best choice
it's not very good to define dimention of your UDN every time with COUNT(UDN column)

looked here?
http://www.decisionmodels.com/calcsecrets.htm

Originally Posted by Richard Buttrey

Hi,

The first thing I'd do is change the named ranges. At the moment whole columns are being included in the name definition. e.g.

Formula:

Please Login or Register  to view this content.

You should limit that to just the number of rows necessary. e.g.

Formula:

Please Login or Register  to view this content.

or probably better the dynamic version

Formula:

Please Login or Register  to view this content.

Thanks again for your expertise Richard. I've removed a lot of 'unused' name ranges which seems to have helped things quite a lot.

Using FORMULA]=OFFSET(Data!$O$2,,,COUNT(Data!$O1:$120000),1)[/FORMULA]
is discounted as the number of rows varies greatly for each txt file that is imported.

I'm not sure I understand the differences using COUNT and COUNTA in the context you mentioned and how COUNTA makes the selection 'dynamic' whereas COUNT returns whole columns. I was under the impression the difference was COUNT returns numbers and COUNTA returns any character?