is there a maximum number of calculations excel will do? it seems like my sheet is skipping/missing calculations. I'm using the sum if function in a column and after excel calculates, many of the cells with sumif are still zero, even though they shouldn't be. the criteria and range match, thus that's not the issue. it seems as if it calculates a few cells at the top (though not all) then completely stops calculating after several rows.

I've tried automatic and manual calculation and didn't get anywhere.

9/29/2009 2:42:25 PM

No there is no per-workbook calculation limit that I'm aware of. I have customers that have books that take hours to calculate because they have hundreds of thousands of calculations per book. I know that doesn't really help you, but let me ask, what happens when you select one of these cells that's zero and hit F2, then enter? Does the number come in as zero still or does it then calculate?

9/29/2009 2:54:38 PM

yeh, this one takes a few minutes...

as for F2+ENT, it remains zero



[Edited on September 29, 2009 at 3:05 PM. Reason : /]

9/29/2009 2:58:50 PM

Well, if it's still zero after manually calculating that cell, then Excel did calculate it and it is supposed to be a zero. Check the parameters to your SUMIF function (click on the little fx next to the function bar).

9/29/2009 3:00:48 PM

after playing around, if I retype the value in the criteria cell, it will calculate. which seems to suggest that the criteria cell and range cell are different. But I've check to see if they are different... unless they're different by an extremely small amount (<1E-20) then they're not different I'm comparing date/times if that matters


the parameters are correct.



fuck I guess the criteria and range must be different because if I set the criteria cell = range cell it works. wonderful. any way to strip away everything less than 1 minute from the time stamp? I can set the date/time to number and run the digits out all day and see nothing but zeros...

ok, so they're different. found one off by 5E-10 seconds.

... need to find a way to strip off all the values beyond minutes really...

[Edited on September 29, 2009 at 3:16 PM. Reason : .]

9/29/2009 3:05:42 PM

post only to say that excel sucks a phat one sometimes

9/29/2009 3:39:32 PM

lol, yeh it does.

I ended up just pulling out each portion of the date/time stamp I needed (using month(), day(), etc, etc) and compared the components of a reference time to that and it works just fine.

9/29/2009 3:47:07 PM