Thanks Niek. I will have a look at the website.
Yes, Excel on the PC at the company is using 100% CPU usage. PC is 2.66 GHz
with 740MB RAM
Mine is a 1.8 GHz dual core with 1GB RAM tablet & when it is doing the
spreadsheet it is only using about 50% CPU
--
Tony
"Niek Otten" wrote:
Hi Tony,
Difficult to say from a distance.
But do visit Charles Williams' site:
www.decisionmodels.com
BTW it wouldn't surprise me if memory shortage is a problem. Any signs of excessive paging in the Task manager?
Personally I prefer to have helper columns with intermediate results; easy to check for correctness and usually rather fast,
faster than SUMPRODUCT anyway.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"TonyL" wrote in message ...
|I have developed a spreadsheet on my home computer which does a lot of
| SUMPRODUCT calculating (I know this can be a slow calculating function). On
| my computer the spreadsheet takes 2 minutes to do its calculating. I
| transferred the speadsheet to the company I was doing work for & run the same
| spreadsheet but it takes 40 minutes to do it's calculating. This was running
| over a network so I tried to run on the local drive on the fastest PC there
| (it is only a slightly lower spec than my machine) & it still takes 30
| minutes.
|
| The SUMPRODUCT looks up a worksheet table which Excel gets thru a query. I
| have only the minimum number of lines in the table that it needs as I know
| the SUMPRODUCT function takes alot more time on larger sized tables. I have
| tried turning off any services that the PC was running, such as antivirus,
| that could slow the PC down but it didn't make any difference. What things
| should I look for to make this faster?
|
| Below is the type of code I have in the spreadsheet.
|
| =SUMPRODUCT((Sheet2!A$1:A$3110="Family")*(LEFT(She et2!F$1:F$3110,20)="Counselling
| - family")*(Sheet2!B$1:B$3110<"
| ")*(LEFT(Sheet2!D$1:D$3110,1)="A")*(Sheet2!E$1:E$3 110=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$31 10=C$6))
|
| Is there another function that I could use other thatn the SUMPRODUCT
| function? TIA
| --
| Tony