Though a belated response to your post I hope the above may still be of some help - if not to you then to anyone else who may have a similar requirement and stumble across this...
Whenever using Array / SUMPRODUCT formulae it's advisable to avoid using double evaluation - eg (ISERROR(test),"x",test).
Moreover, given use of XL2010 avoid using Arrays with large ranges as these are iterative calcs and very expensive (pre XL2007 you could not use entire column references for example)
Given all of the above I would propose you
a) add some dynamic, non-volatile named range constructs to minimise calc range
b) utilise IFERROR function to avoid need for double evaluation
Regards point a)
Create Named Ranges as follows
b) with the above in place you can then modify your Arrays per below
the above are still not overly efficient formulae, however, they will be significantly faster than those used in the previously attached file - and also account for your requirements of course.
To put above in context - using your sample file the original formulae for Ordered would take say 10 seconds on a dual core 4GB machine whereas the revised will take at most a 2/10 of that time.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks