Alternative to calc differences of DSUM functions (with 5 or 6 criteria)

[Dave]

Hi,

I'm trying to convert a 123 spreadsheet to XLS, and
improve it's performance. The 123 file is using @DSUM a
lot and is calculating differences between the multiple
DSUM formulas within a single cell.

I have weekly YTD sales data for a variety of stores, by
department, for specific weeks from this year and last
year. I have 4 or 5 data areas that these formulas refer
to. Most are very small areas. The 1 large data set has
almost 7,000 rows and maybe 20 columns.

Depending upon a given value for a store (like Year =
2004, Week = 40) the function will sum department values
for week 40 and subtract the sum of departments for
another specified week, say 36. This tells me how sales
were in that period. The formula will then divide by
another DSUM. The idea is, how were sales for period 'x'
versus period 'y' as a percentage. Up 10%, down 20%, etc.

Some of these DSUM formulas have over 6 different criteria.

I've looked at a variety of options and I've seen that
Excel doesn't calc DSUM very fast. As it is it takes
something like 5 minutes to calc this file in Lotus.

I need a somewhat flexible/elegant solution so that I can
train several other people on how to edit any 'solution'
for different contingencies.

Thanks!!

 

[Harlan Grove]

...
...

Some of these DSUM formulas have over 6 different criteria.

I've looked at a variety of options and I've seen that
Excel doesn't calc DSUM very fast. As it is it takes
something like 5 minutes to calc this file in Lotus.

I need a somewhat flexible/elegant solution so that I can
train several other people on how to edit any 'solution'
for different contingencies.

Show the 123 formulas. Without them, there's way too much guesswork involved in
trying to figure out how you could improve recalc performance.

 

[Guest]

Here you go:

(@DSUM($DATA_SLS,"YtdslsTy",((FY=$G12#AND#WK=$H12)#OR#
(FY=$I12#AND#WK=$J12))#AND#STR=$A12#AND#MT=A$4)-@DSUM
($DATA_SLS,"YtdslsTy",FY=$E12#AND#WK=$F12#AND#STR=$A12#AND#
MT=A$4))/(@DSUM($DATA_SLS,"YtdslsLy",((FY=$G12#AND#WK=$H12)
#OR#(FY=$I12#AND#WK=$J12))#AND#STR=$A12#AND#MT=A$4)-@DSUM
($DATA_SLS,"YtdslsLy",FY=$E12#AND#WK=$F12#AND#STR=$A12#AND#
MT=A$4))-1

 

[Harlan Grove]

Here you go:

(@DSUM($DATA_SLS,"YtdslsTy",((FY=$G12#AND#WK=$H12)#OR#
(FY=$I12#AND#WK=$J12))#AND#STR=$A12#AND#MT=A$4)-@DSUM
($DATA_SLS,"YtdslsTy",FY=$E12#AND#WK=$F12#AND#STR=$A12#AND#
MT=A$4))/(@DSUM($DATA_SLS,"YtdslsLy",((FY=$G12#AND#WK=$H12)
#OR#(FY=$I12#AND#WK=$J12))#AND#STR=$A12#AND#MT=A$4)-@DSUM
($DATA_SLS,"YtdslsLy",FY=$E12#AND#WK=$F12#AND#STR=$A12#AND#
MT=A$4))-1

...

(
@DSUM(
$DATA_SLS,
"YtdslsTy",
(
(FY=$G12#AND#WK=$H12)
#OR#
(FY=$I12#AND#WK=$J12)
)
#AND#
STR=$A12
#AND#
MT=A$4
)
-@DSUM(
$DATA_SLS,
"YtdslsTy",
FY=$E12
#AND#
WK=$F12
#AND#
STR=$A12
#AND#
MT=A$4
)
)
/(
@DSUM(
$DATA_SLS,
"YtdslsLy",
(
(FY=$G12#AND#WK=$H12)
#OR#
(FY=$I12#AND#WK=$J12)
)
#AND#
STR=$A12
#AND#
MT=A$4
)
-@DSUM(
$DATA_SLS,
"YtdslsLy",
FY=$E12
#AND#
WK=$F12
#AND#
STR=$A12
#AND#
MT=A$4
)
)-1

You have a table (range) names DATA_SLS. That's easy enough to convert into
Excel. This table contains fields named (not necessarily an exhaustive list)

YtdslsTy
YtdslsLy
FY
WK
STR
MT

One possibility is naming each column of this table (with the field names NOT
included in the named ranges). This would allow the translation into the
SUMPRODUCT expression

(
SUMPRODUCT(
((FY=$G12)*(WK=$H12)+(FY=$I12)*(WK=$J12)>0)*(STR=$A12)*(MT=A$4),
YtdslsTy
)
-SUMPRODUCT(
(FY=$E12)*(WK=$F12)*(STR=$A12)*(MT=A$4),
YtdslsTy
)
)
/(
SUMPRODUCT(
((FY=$G12)*(WK=$H12)+(FY=$I12)*(WK=$J12)>0)*(STR=$A12)*(MT=A$4),
YtdslsLy
)
-SUMPRODUCT(
(FY=$E12)*(WK=$F12)*(STR=$A12)*(MT=A$4),
YtdslsTy
)
)-1

If this proves too restrictive, see the archived thread beginning at

http://google.com/[email protected]