CSE formulas
 

Dear friends, I`m currently using CSE formulas to extract and analyse data
with the multiple criterias. e.g:
=SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$6 5536=$C$6)*('12SP'!$E$2:$E$65536<=$D$2)*('12SP'!$D $2:$D$65536))..But
unfortunately I have a problem with sheets so that everytime I face the
calculating cells %0..%100 phrase when I make any change. Perhaps it`s
because of heavy formulas..Anybody can help me in this issue?Thanks
beforehand.

CSE formulas
 

One way .. I'd set the book's calc mode to Manual* (Click Tools Options
Calculation tab, options are there). Then we could press F9 to recalc, but
only as and when required, eg after all updates / changes are done.
*I'd usually leave "Recalc before save" unchecked (as a personal preference)

Another thing I might do is to use the smallest range sizes possible ..
=SUM(('12SP'!$A$2:$A$65536=$A$1)*...
Do you really have/expect data all the way to 65K? Perhaps 1K suffices <g?
The smaller the range sizes, the faster it'll compute.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abas Ibrahimov" wrote:
Dear friends, I`m currently using CSE formulas to extract and analyse data
with the multiple criterias. e.g:
=SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$6 5536=$C$6)*('12SP'!$E$2:$E$65536<=$D$2)*('12SP'!$D $2:$D$65536))..But
unfortunately I have a problem with sheets so that everytime I face the
calculating cells %0..%100 phrase when I make any change. Perhaps it`s
because of heavy formulas..Anybody can help me in this issue?Thanks
beforehand.

CSE formulas
 

Wow..thanks for help Max, I will try to calculate them manually..and reduce
the range size as much as possible. Thanks again, very helpful

"Max" wrote:

One way .. I'd set the book's calc mode to Manual* (Click Tools Options
Calculation tab, options are there). Then we could press F9 to recalc, but
only as and when required, eg after all updates / changes are done.
*I'd usually leave "Recalc before save" unchecked (as a personal preference)

Another thing I might do is to use the smallest range sizes possible ..
=SUM(('12SP'!$A$2:$A$65536=$A$1)*...
Do you really have/expect data all the way to 65K? Perhaps 1K suffices <g?
The smaller the range sizes, the faster it'll compute.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abas Ibrahimov" wrote:
Dear friends, I`m currently using CSE formulas to extract and analyse data
with the multiple criterias. e.g:
=SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$6 5536=$C$6)*('12SP'!$E$2:$E$65536<=$D$2)*('12SP'!$D $2:$D$65536))..But
unfortunately I have a problem with sheets so that everytime I face the
calculating cells %0..%100 phrase when I make any change. Perhaps it`s
because of heavy formulas..Anybody can help me in this issue?Thanks
beforehand.

CSE formulas
 

You're welcome, Abas !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abas Ibrahimov" wrote:
Wow..thanks for help Max, I will try to calculate them manually..and reduce
the range size as much as possible. Thanks again, very helpful

CSE formulas
 

It can even be quicker to use dynamic ranges so as to only calculate the
minimum necessary, such as

=SUM((OFFSET('12SP'!$A$2,0,0,COUNTA('12SP'!$A:$A)-1,1)=$A$1)*
(OFFSET('12SP'!$G$2,0,0,COUNTA('12SP'!$A:$A)-1,1)=$C$6)*
(OFFSET('12SP'!$E$2,1,0,COUNTA('12SP'!$A:$A)-1,1)<=$D$2)*
(OFFSET('12SP'!$D$2,1,0,COUNTA('12SP'!$A:$A)-1,1)))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Abas Ibrahimov" wrote in message
...
Wow..thanks for help Max, I will try to calculate them manually..and
reduce
the range size as much as possible. Thanks again, very helpful

"Max" wrote:

One way .. I'd set the book's calc mode to Manual* (Click Tools
Options
Calculation tab, options are there). Then we could press F9 to recalc,
but
only as and when required, eg after all updates / changes are done.
*I'd usually leave "Recalc before save" unchecked (as a personal
preference)

Another thing I might do is to use the smallest range sizes possible ..
=SUM(('12SP'!$A$2:$A$65536=$A$1)*...
Do you really have/expect data all the way to 65K? Perhaps 1K suffices
<g?
The smaller the range sizes, the faster it'll compute.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Abas Ibrahimov" wrote:
Dear friends, I`m currently using CSE formulas to extract and analyse
data
with the multiple criterias. e.g:

=SUM(('12SP'!$A$2:$A$65536=$A$1)*('12SP'!$G$2:$G$6 5536=$C$6)*('12SP'!$E$2:$E
$65536<=$D$2)*('12SP'!$D$2:$D$65536))..But
unfortunately I have a problem with sheets so that everytime I face
the
calculating cells %0..%100 phrase when I make any change. Perhaps it`s
because of heavy formulas..Anybody can help me in this issue?Thanks
beforehand.