Dave Hawley, recommended database functions for these formulas....
Posted by Dustin on April 03, 2001 12:01 PM
Dave,
As a followup to the array formula and performance issue discussion earlier, I wondered if you might be able to recommend which database functions in particular I should use to improve the performance of these formulas.
Just let me know if you need more info to make an educated decision on this. I'll be glad to send you whatever you need.
As an aside, I have already made the TODAY() to CurDay change and it has not made a significant improvement.
To recap our earlier discussion, I have 7 tabs full of formulas similar to the three I am including here:
=IF(CurDay<C$4,"",IF(ISERROR(SUM(([IBMData.xls]Sub!$E$2:$E$5000>B$5)*([IBMData.xls]Sub!$E$2:$E$5000<=C$5)*([IBMData.xls]Sub!$K$2:$K$5000=$A6))/(SUM(([IBMData.xls]Req!$C$2:$C$5000>B$5)*([IBMData.xls]Req!$C$2:$C$5000<=C$5)*([IBMData.xls]Req!$F$2:$F$5000=$A6)))),"",SUM(([IBMData.xls]Sub!$E$2:$E$5000>B$5)*([IBMData.xls]Sub!$E$2:$E$5000<=C$5)*([IBMData.xls]Sub!$K$2:$K$5000=$A6))/(SUM(([IBMData.xls]Req!$C$2:$C$5000>B$5)*([IBMData.xls]Req!$C$2:$C$5000<=C$5)*([IBMData.xls]Req!$F$2:$F$5000=$A6)))))
-------------------------------------------------------
=IF(CurDay<G$5,"",SUM(IF([IBMData.xls]Assg!$BD$2:$BD$5000="C",1,0)*(IF([IBMData.xls]Assg!$H$2:$H$5000="Current",1,IF([IBMData.xls]Assg!$H$2:$H$5000>F$5,1,0))*(IF([IBMData.xls]Assg!$AZ$2:$AZ$5000<=F$5,1,0)))))
------------------------------------------------------
=IF(CurDay<G$5,"",SUM(IF([IBMData.xls]Req!$P$2:$P$5000="C",1,0)*(IF([IBMData.xls]Req!$C$2:$C$5000>F$5,1,0)*(IF([IBMData.xls]Req!$C$2:$C$5000<=G$5,1,0)))))
Thanks in advance Dave!
Dustin
Posted by Mark W. on April 03, 2001 12:10 PM
I'd like to see that too!
Posted by Dave Hawley on April 03, 2001 12:17 PM
Hi Dustin
Without doubt these would slow down performance significantly. Arrays are ok, but only for a few instances. You should never use an array nested as deep as that first one unless absolutely needed.
a quick look at suggests that you are pulling in data from another Workbook, if this is the case for all your arrays, then the Database formulas wont work as they require the External workbook to be open. But you could certainly replace any arrays that are not external with DCOUNT, DCOUNTA and DSUM.
For your external data I would suggest using a Pivot Table.
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on April 03, 2001 12:17 PM
Re: I'd like to see that too!
Mark: Dustin should first name all these ranges meaningfully and substitute them in the formulas, then get rid off the IFs that populate those array formulas.
Posted by Dustin on April 03, 2001 12:24 PM
Thanks for the response Dave.
Firstly, I am not completely opposed to the idea of educating the end-user that the external sheets must be open to calculate the correct value.
Secondly, I have absolutely no experience with Pivot Tables and would not know where to begin as far as structuring my data into the correct format.
Thirdly, I would love any assistance on how to change the syntax of the formulas using the dfunctions.
Thanks again.
Dustin
Posted by Dustin on April 03, 2001 12:26 PM
Aladin and Mark, please excuse my ignorance, but what would you like to see?
Posted by Dave Hawley on April 03, 2001 12:35 PM
Dustin
regarding Pivot Table help and examples take a look through this search result:
http://www.google.com/search?sourceid=navclient&q=excel+pivot+table+help
They can seem a bit daunting at first, but stick with them and you wont look back.
Regarding the database formulas, is not that they wont update withpout the external workbook being open, it's that they will return #VALUE! and error value. But if you are still wanting to use them I strongly suggest reading the help in Excel, it is explained quite well with many examples. You would probably need to break that mega array into 2 or 3 database formulas, but even that would be better.
Dave
OzGrid Business Applications
Posted by Dustin on April 03, 2001 12:37 PM
Thanks again Dave. I will read up on Pivot Tables.
Posted by Mark W. on April 03, 2001 12:57 PM
Re: ... but what would you like to see?
> ...recommend which database functions in particular
> I should use to improve the performance of these
> formulas
A Dfunction solution to your problem.
