Are any of the values you are summing likely to have produced a NULL (or non-numeric) result from the formula?
From memory I think this can give you the problem you describe - but it could be something else.
Try some filters to see if you can get subsets of the data which do add up correctly - just one way to check what is going on.
Yes some do have null values... is that the problem? How do I work around it? /b[/quote]OK, you need to ensure that the values in the field you are calculating will not be null - i.e.e the formula will always give a numeric result. Null can be the result of a divide by zero error for example.
I think it will be useful to revisit the formula and ensure that all the fields it uses are themselves numeric and thet none of them can give a zero result - it all gets a bit formula specific at that point.
You may nee to put something into the formula that checks for NULL (eg using the ISNULL function to test the result) and then ensure that any nulls are converted to zero or something suitable like that.
My suggestion on filters was really intended for slicing the data (if possible) into smaller chinks to see if the formula worked for some groups of records and not others.
Sometimes drilling down in a summary can show that some keys calculate OK at the summary level and some do not.
How have you got your options set for the display of nulls? Are they obvious in the table - "null" or blank for example?
They were set to display as 0 and I am now changing to null. I'll go back and look at my formulas. Thanks. /b[/quote]Ok, that should make things visible for the purpose of what you are now doing so it will be easier to see what is going on.
The resolution, assuming the data feeding the formula is as it needs to be - ie the null calculation is in fact valid - is a little more difficult in some ways.
For example, you could put a check into the feeding fields for the formula such that if the result is NULL make the field numeric zero or, perhaps, 1, depending upon what it is to be used for in the next calculation. That would make the calculation run BUT may give misleading results. That in turn might be a problem for audit and compliance requirements.
I don't know the purpose of your calculation but take care to consider issues like that when working out what sort of fix is appropriate.
Edit for typo.
[size="1"][ June 16, 2005, 10:38 AM: Message edited by: Grant Perkins ][/size]
I know i have formulae that result in nulls even though the formula does not have divison in it.
My simple formal was A - B which resulted in null if either A or B are null. I had forgetten that I had the Monarch option "display nulls as zero", so I could not find the error for a while.
Once I changed to
if(isnull(A),0,A) - if (isnull(B), 0, B), the problem was solved.
We've seen this a couple of times now, and thought I'd post to see how anyone else has solved this problem.
Assume that we have a summary[/B] where some values are NULL. We want to display this data as a zero, so we set this in Options>View. If we then export this data to Excel, then the zeroes that represent the null values are treated as text and not as numeric cells in Excel. You can see this usually by their being aligned left with a small green triangle in the corner.
I've seen ASAP Utilities (http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=70&utilities=Numbers[/url]) which looks like it could do the job, but it would be nice if Monarch had an option to view & export NULL as a numeric zero rather than as "0".