Workbook so slow in every way

[excellentexcel]

Hi gents i have a workbook that is only 345kb in size
it takes ages to open and although it has lookups and retrieves data from the web it just seems like it shouldnt be so slow

i also keep getting a message at the bottom like this:-

calculating (1 processor ) thena %

why is this none of my other books does this


thanks

[DonkeyOte]

how many arrays / volatile functions do you have within it ?

If it's not confidential - zip the file and upload here so people can review.

[excellentexcel]

no problem donkeyote
its a football result sheet
i have attached

[DonkeyOte]

In the attached, same file as yours, I have highlighted all formulae...

Green - are formulae that will have little bearing in performance
Red - are formulae that will have an impact on performance

Nearly all Red's appear on Summary (a few on hp1)

In particular note those SUMPRODUCTS inclusive of SUBTOTAL w/OFFSET... OFFSET is Volatile (and filtering row visibility is Volatile action) ... so essentially these functions are Volatile arrays and thus are likely to have significant (& detrimental) impact on performance.

To test... for each cell in red add a ' before the =
(ie make the formulae text)

Once done, SaveAs the file... close XL.
Now re-open your revised file... does the performance issue you spoke of remain on this version ?

(This is merely to ascertain as to whether or not these functions are the source of your problem... at which point need to think of alternative approaches / options)

[excellentexcel]

hi donkeyote
i did what you said
i marked all the red cells with ' first to make them text cells as you suggested
this made it a hell of a lot quicker
i have attached
do i need to keep these cells this way or do you suggest anything?
kind regards
and thanks

[excellentexcel]

hi donkeyote

the input sheet requires you to pick two teams one in e1 the other in g1
this will then look up each teams reference number and how they are reffered to as in sheets head to head and home team/away team
column c is how they show on head to head
where as column d shows as hometeam/away team
if you were to select aldershot in e1
it will then pull the number to use as web query and put aldershot united in e4 and aldershot in e5

sheet head to head
the formula in column g returns the score from column c as a score rather than a date
the formula in column h determines the winner or draw if necessary
the formula in column h tells you if there were over 2.5 goals or under 2.5 goals

the same formulas apply to sheets home team and away team

sheet summary
tells you all the data that has been entered by the web query but in a summarised version

i have to say it seems to be getting slower and slower

it sometimes take an absolute age for me to get my internet explorer back again

im on office 2007 and ie8

hope this helps