Calculating is very slow

[TonyL]

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(Sheet2!F$1:F$3110,20)="Counselling
- family")*(Sheet2!B$1:B$3110<"
")*(LEFT(Sheet2!D$1$3110,1)="A")*(Sheet2!E$1:E$3110>=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$3110=C$6))

Is there another function that I could use other thatn the SUMPRODUCT
function? TIA

 

[Niek Otten]

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


|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(Sheet2!F$1:F$3110,20)="Counselling
| - family")*(Sheet2!B$1:B$3110<"
| ")*(LEFT(Sheet2!D$1$3110,1)="A")*(Sheet2!E$1:E$3110>=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$3110=C$6))
|
| Is there another function that I could use other thatn the SUMPRODUCT
| function? TIA
| --
| Tony

 

[TonyL]

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

 

[Niek Otten]

Hi Tony,

I'm not very good at this, but I think you get 50% because only one of the processors is used. I did notice that modern processors
(like yours) are a lot faster than older ones. The GHz does not count too much then.


| 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
| >
| >
| > |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(Sheet2!F$1:F$3110,20)="Counselling
| > | - family")*(Sheet2!B$1:B$3110<"
| > | ")*(LEFT(Sheet2!D$1$3110,1)="A")*(Sheet2!E$1:E$3110>=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$3110=C$6))
| > |
| > | Is there another function that I could use other thatn the SUMPRODUCT
| > | function? TIA
| > | --
| > | Tony
| >
| >
| >