dsum, array or vlookup
Posted by Dan on October 04, 2001 10:33 AM
I am pulling sales data from one tab to another. The sales are by month and have a region and store no. associated with it. Should I use Dsum, arrays or vlookups. Right now I have an array that is very long and it takes in inordinate amt. of time to "compute".
Posted by Aladin Akyurek on October 04, 2001 10:39 AM
Would you post the array formula that you use?
Aladin
Posted by Dan on October 04, 2001 10:57 AM
=-SUM((Raw_Data!$J$2:$J$12600='"P&L" level detail'!$I11)*(IF('"P&L" level detail'!$B$6="all",1,Raw_Data!$G$2:$G$12600='"P&L" level detail'!$B$6))*(IF('"P&L" level detail'!$D$6="all",1,Raw_Data!$H$2:$H$12600='"P&L" level detail'!$D$6))*(IF('"P&L" level detail'!$A$6="all",1,Raw_Data!$F$2:$F$12600='"P&L" level detail'!$A$6))*(IF('"P&L" level detail'!$C$6="all",1,Raw_Data!$I$2:$I$12600='"P&L" level detail'!$C$6))*(Raw_Data!$G$2:$G$12600<>"Overhead")*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600='"P&L" level detail'!$I11)*(IF('"P&L" level detail'!$B$7="all",1,Raw_Data!$G$2:$G$12600='"P&L" level detail'!$B$7))*(IF('"P&L" level detail'!$D$7="all",1,Raw_Data!$H$2:$H$12600='"P&L" level detail'!$D$7))*(IF('"P&L" level detail'!$A$7="all",1,Raw_Data!$F$2:$F$12600='"P&L" level detail'!$A$7))*(IF('"P&L" level detail'!$C$7="all",1,Raw_Data!$I$2:$I$12600='"P&L" level detail'!$C$7))*(Raw_Data!$G$2:$G$12600<>"Overhead")*(Raw_Data!AY$2:AY$12600/1000))
Posted by Dan on October 04, 2001 11:05 AM
Can you simplify this so it won't take so long?
=-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$B$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$B$7,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$A$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$D$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))
Posted by Aladin Akyurek on October 04, 2001 11:22 AM
I suspect that the IFs are unnecessary in this array formula. Try the following SUMPRODUCT equivalent. However, don't expect too much performance gain.
=-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$B$6)*(Raw_Data!AY$2:AY$12600/1000))-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$B$7)*(Raw_Data!AY$2:AY$12600/1000))-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$A$6))*(Raw_Data!AY$2:AY$12600/1000))-SUMPRODUCT((Raw_Data!$J$2:$J$12600=$I17)*(Raw_Data!$G$2:$G$12600=$D$6)*(Raw_Data!AY$2:AY$12600/1000))
Would you report back how it does?
Aladin
Posted by Barrie Davidson on October 04, 2001 12:37 PM
Dan, have you considered using a pivot table to summarize your data?
BarrieBarrie Davidson
