Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |