Hi I have an interlinked workbook that's giving me headaches and I need to discover a couple things:
1. Are SUMIFS Volatile? From my understanding the answer should be primarily no? Is there any definitive way to determine if/when this is the case
2. How can I determine the formulas that are all calculating on sheet recalc? Is there a way to identify where what's happening gloablly so I can maybe isolate my problems?
SUMIF(S) is not volatile, but the cells they reference may be.
Charles Williams has a tool at http://www.decisionmodels.com/FastExcelV3Profiler.htm that may help. There is also a UDF on his site (don't have a link), CalcCount, that will tell you when a formula recalculates.
Entia non sunt multiplicanda sine necessitate
Although he does also state:
"One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions. This occurs when the size of the first range argument is not the same as the second (sum_range) argument.
For example =SUMIF(A1:A4,">0",B1) is volatile whereas =SUMIF(A1:A4,">0",B1:B4) is not volatile. Both of these formulae will reference cells B1:B4."
on that same site, though whether that is relevant to your set-up I do not know.
Regards
Good point. I was not accounting for bad practice.
BTW, cmore, I have not used Charles's tool, though I frequently use CalcCount (I keep it in Personal).
Agreed that it is just that.Originally Posted by shg
Regards
Thanks Guys, this is exactly what I was looking for. So the SUMIFS I am using one range to many, I wouldn't typically use this, but I'm using it in order to shrink an array to a desired size.
So for instance I look at cells SUMIFS(A14:M14, A12:M12, "*"&Array(with 9 elements)&"*")............. This doesn't typically cause problems except where I need to do a cross sheet comparison that involves 2 of these types of arrays.
That said, based on you guys' input I'm pretty sure this is where the volatility arises. Will try and use Calcount now as well. Thank You.
Are there any alternatives to the kind of SUMIFS I described?
I can probably MMULT * SUMPRODUCT it some how, not sure if that would be better?
All the xxIF(S) functions are golden.
Gotcha, but if my construction is the 'Bad Practice' and creating unwanted volatility, you think SUMIFS is still best option?
Please look carefully at XOR's example. You're not doing that, as far as I can tell, and if you are, just fix it.
Yep, you're right the SUMIFS alone would not then be the cause of the volatility, back to the drawing board
Hi shg, having a bad time of find CalcCount, any suggestions?
Here's the version I have:
Any errors are mine alone.
Please Login or Register to view this content.
Thanks Trying it out a bit now. So I have to use it on cells that I think are the issue right? Or do you you loop through cells or anything else when using it?
So based on this tool, I can see that certain cells are being recalculated multiple times on recalculate, and this is the likely cause of my major slow downs. But I don't have an efficient way of identifying which specific cells (although I have an idea), or why it's calculating multiple times. Any thoughts towards resolving
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks