CSE formulas

[Guest]

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$65536=$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.

 

[Guest]

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.

---

 

[Guest]

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

 

[Guest]

You're welcome, Abas !
Thanks for the feedback ..

 

[Bob Phillips]

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)