File running slow!

[hoyos]

I have a excell file which has alot of "SUMPRODUCT" formulas. I know using
this formula slows down the calculation by quit abit.....so I have been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

 

[Niek Otten]

Tell us what you use the SUMPRODUCT formulas for, so we can think about
possible other solutions....

 

[מיכ×ל (מיקי) ×בידן]

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky

 

[מיכ×ל (מיקי) ×בידן]

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation and
not after entering/changing cell values.
Micky

 

[hoyos]

Thanks for replying,
Here are two samples of formula I am using:

=IF(AF16="",NA(),SUMPRODUCT(--(YEAR(Orders!$B$5:$B$65001)=YEAR(AF16)),--(MONTH(Orders!$B$5:$B$65001)=MONTH(AF16)),--(DAY(Orders!$B$5:$B$65001)=DAY(AF16)),Orders!$J$5:$J$65001))
an
=SUMPRODUCT((Orders!$B$3:$B$64988>=DATE($B$2,1,1))*(Orders!$B$3:$B$64988<=DATE($B$2,1,31))*(Orders!$C$3:$C$64988=$B5))

At the moment I have 14 worksheets of data. Would it help to have as much on
one sheet as possible or does that not make a difference?

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation and
not after entering/changing cell values.
Micky

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky

 

[Roger Govier]

Hi

Firstly the first formula can be simplified to
=IF(AF16="",NA(),SUMPRODUCT(--(Orders!$B$5:$B$65001=AF16),Orders!$J$5:$J$65001)

Secondly, you are giving Sumproduct almost a whole column to be calculating
each time, when most of the cells will be empty.

Create some Dynamic ranges to use within your formulae.
For example if column B is date and column J is value
Insert>Name>Define>
Name Date
Refers to $B$5:INDEX($B:$B,COUNTA($B$5:$B$65536))

Insert>Name>Define>
Name Value
Refers to $J$5:INDEX($J:$J,COUNTA($B$5:$B$65536))

(Note, the Counta is based on column B in both cases, to ensure that the
ranges are of the same length)

Then, make your formula
=IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value)

The calculations will only be performed upon the used range of data.

For more information on creating Dynamic ranges, I have a page on Debra
Dalgleish's site and a downloadable file showing how to create your Dynamic
ranges with a macro at
http://www.contextures.com/xlNames03.html
--
Regards
Roger Govier

Thanks for replying,
Here are two samples of formula I am using:

=IF(AF16="",NA(),SUMPRODUCT(--(YEAR(Orders!$B$5:$B$65001)=YEAR(AF16)),--(MONTH(Orders!$B$5:$B$65001)=MONTH(AF16)),--(DAY(Orders!$B$5:$B$65001)=DAY(AF16)),Orders!$J$5:$J$65001))
and
=SUMPRODUCT((Orders!$B$3:$B$64988>=DATE($B$2,1,1))*(Orders!$B$3:$B$64988<=DATE($B$2,1,31))*(Orders!$C$3:$C$64988=$B5))

At the moment I have 14 worksheets of data. Would it help to have as much
on
one sheet as possible or does that not make a difference?

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation
and
not after entering/changing cell values.
Micky

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky


:

I have a excell file which has alot of "SUMPRODUCT" formulas. I know
using
this formula slows down the calculation by quit abit.....so I have
been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

__________ Information from ESET Smart Security, version of virus
signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[hoyos]

Thank you Roger I shall try that.
I will come back with a feedback.

Hi

Firstly the first formula can be simplified to
=IF(AF16="",NA(),SUMPRODUCT(--(Orders!$B$5:$B$65001=AF16),Orders!$J$5:$J$65001)

Secondly, you are giving Sumproduct almost a whole column to be calculating
each time, when most of the cells will be empty.

Create some Dynamic ranges to use within your formulae.
For example if column B is date and column J is value
Insert>Name>Define>
Name Date
Refers to $B$5:INDEX($B:$B,COUNTA($B$5:$B$65536))

Insert>Name>Define>
Name Value
Refers to $J$5:INDEX($J:$J,COUNTA($B$5:$B$65536))

(Note, the Counta is based on column B in both cases, to ensure that the
ranges are of the same length)

Then, make your formula
=IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value)

The calculations will only be performed upon the used range of data.

For more information on creating Dynamic ranges, I have a page on Debra
Dalgleish's site and a downloadable file showing how to create your Dynamic
ranges with a macro at
http://www.contextures.com/xlNames03.html
--
Regards
Roger Govier

Thanks for replying,
Here are two samples of formula I am using:

=IF(AF16="",NA(),SUMPRODUCT(--(YEAR(Orders!$B$5:$B$65001)=YEAR(AF16)),--(MONTH(Orders!$B$5:$B$65001)=MONTH(AF16)),--(DAY(Orders!$B$5:$B$65001)=DAY(AF16)),Orders!$J$5:$J$65001))
and
=SUMPRODUCT((Orders!$B$3:$B$64988>=DATE($B$2,1,1))*(Orders!$B$3:$B$64988<=DATE($B$2,1,31))*(Orders!$C$3:$C$64988=$B5))

At the moment I have 14 worksheets of data. Would it help to have as much
on
one sheet as possible or does that not make a difference?

PS Until you'll get a solution that will resolve the slowness - switch to
"Manual Calculation" and hit [F9] when the time comes for a calculation
and
not after entering/changing cell values.
Micky


:

SumProduct is basically an Array-Formula.
You may try using a different function but it will be difficult to make
suggestions without seeing the WB.
Micky


:

I have a excell file which has alot of "SUMPRODUCT" formulas. I know
using
this formula slows down the calculation by quit abit.....so I have
been told!
Is ther anything I can do....ie use a different formula, to speed the
calculations?

__________ Information from ESET Smart Security, version of virus
signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com