SUMPRODUCT

[Ken]

Hi Group!
I'm back again, trying to solve a problem that Don Guillett so
correctly pointed out that needs to be addressed, and I thank you for
that Don!
I have a database that dynamically updates from log entries, which
in turn updates my charts, and they are working extremely well. The
problem is that I'm using SUMPRODUCT in every cell in my daily
database, and that is causing the log entries to become slower to
enter because the database recalculates on every entry, and my formula
includes far too many cells in that calculation. I have entered a
dynamic named range "MyRange" that correctly includes only the rows
that are populated, but I haven't been able to include that named
range into the calculations for SUMPRODUCT. I have screenshots here to
illustrate how everything is laid out:
http://www.elodgingatbristol.com/SUMPRODUCT.htm
Can that named range be used in the formula to limit how many
calculations that Excel has to do? Any advice is greatly
appreciated...I've beat my head against the wall for a couple of weeks
trying different tips that I've seen in the groups, but can't get it
to work. Thanks very much in advance....
Ken

 

[Ken]

Sorry guys, I posted this message as a reply in error...my apologies!!

 

[JBoulton]

Ken,

SUMPRODUCT will work with dynamic range names. It looks like your MyRange
is the whole data table. That's the problem. You'll have to create seperate
dynamic range names for each field you want to use in the SUMPRODUCT calc.
=SUMPRODUCT(--(range1=x),(range2),(range3)) or whatever your calc looks
like. Range1, Range2 and Range 3 would each be one column wide.

HTH
Jim

 

[JMB]

The individual columns of MyRange could be referenced with Index.
=Index(MyRange,0,1)
would return the first column. But that's for the sake of academics, it may
be better to create a separate dynamic named range for each column needed.

Also, Offset is a volatile function. I think it would help to avoid having
a lot of formulae dependent on a volatile function. Maybe see if this offers
any improvement:
=JobLogEntry!$A$2:INDEX(JobLogEntry!$T:$T,MATCH(1E+306,JobLogEntry!$A:$A,1))
to define MyRange and something similar for each column needed for
sumproduct calculations.

 

[Ken]

The individual columns of MyRange could be referenced with Index.
=Index(MyRange,0,1)
would return the first column. But that's for the sake of academics, it may
be better to create a separate dynamic named range for each column needed.

Also, Offset is a volatile function. I think it would help to avoid having
a lot of formulae dependent on a volatile function. Maybe see if this offers
any improvement:
=JobLogEntry!$A$2:INDEX(JobLogEntry!$T:$T,MATCH(1E+306,JobLogEntry!$A:$A,1)­)
to define MyRange and something similar for each column needed for
sumproduct calculations.






- Show quoted text -

JMB and Jim....Thanks to both of you for your reply! I am setting up a
seperate named range for each of the 4 columns that I need....2 of
them are text, COL D and COL Q, the other 2 are dates, COL I and COL
K....the formula you suggested works well with a new MyRange, and for
COL I, which I named DateRcvd, and for COL K, which I named
DateSent....but the named ranges for the other 2, ValveType, and
Completion, do not work because they are text only columns....if I
type in random numbers as an experiment, they work, but I need for
them to remain as text columns...what am I doing wrong????...Thanks
again for all your help!
Ken

 

[JMB]

The problem is the Match function is trying to match a large number to a text
only field. To get match to return the last text entry, you could use:
=MATCH(Rept("Z",100),JobLogEntry!$D:$D,1)

or to define a named with mixed numbers and text:
=JobLogEntry!$D$2:INDEX(JobLogEntry!$D:$D,MATCH(2,1/(JobLogEntry!$D$2:$A$65536<>""),1))

Note: versions prior to XL 2007 cannot accomodate entire columns in an array
formula, so you can use D165535 or D265536 but not D. Not a problem in
this case since your defined names start in row 2.

But since this is a table, if the last cell for column D does not extend
past the last cell for column A, you should be able to define column D
=JobLogEntry!$D$2:INDEX(JobLogEntry!$D:$D,MATCH(1E+306,JobLogEntry!$A:$A,1))

 

[Ken]

The problem is the Match function is trying to match a large number to a text
only field. To get match to return the last text entry, you could use:
=MATCH(Rept("Z",100),JobLogEntry!$D:$D,1)

or to define a named with mixed numbers and text:
=JobLogEntry!$D$2:INDEX(JobLogEntry!$D:$D,MATCH(2,1/(JobLogEntry!$D$2:$A$65­536<>""),1))

Note: versions prior to XL 2007 cannot accomodate entire columns in an array
formula, so you can use D165535 or D265536 but not D. Not a problem in
this case since your defined names start in row 2.

But since this is a table, if the last cell for column D does not extend
past the last cell for column A, you should be able to define column D
=JobLogEntry!$D$2:INDEX(JobLogEntry!$D:$D,MATCH(1E+306,JobLogEntry!$A:$A,1)­)






- Show quoted text -

JMB....I had typed in the wrong cell reference in the last part of the
formula, which wouldn't let it work! I didn't notice it until your
posted reply...Thank you for all your generous help! My calculations
are taking about 8 seconds or so to complete, but it's faster than it
was...at least now I have my ranges with the correct syntax, and have
discovered a few tips along the way....I go to "View code" for the
Database sheet and turn off the calculations for that sheet...I'm now
going to search for some VBA to allow me to turn off the calculations
for just that sheet with a click of a button, if it's possible! I'm
full of ideas, but short on implementation! Thanks to you and all of
the group...you're all life savers....
Ken

 

[JMB]

check vba help for enablecalculation property. although I think the
calculation for a particular worksheet is re-enabled when the workbook is
closed and re-opened.