SPSS: Inconsistent totals due to rounding of numbers

Question

I am using weights when running the data with SPSS custom tables.

Thus it is expected that the column or row values may not add up to row total, column total or Table Total due to rounding of decimals

sample table result:

                                  variable 2
                         category 1       category 2      Total
variable 1   category 1       45             52             97
             category 2       60             56             115
             Total           105            107             211

Is there a way to force SPSS to output the correct row, column, or table totals?

expected table output:

                                  variable 2
                         category 1       category 2      Total
variable 1   category 1       45             52             97
             category 2       60             56             116
             Total           105            108             213

Answer 1

To be clear: the total displayed by CTABLES is mathematically correct. However, if you want to display as the total the sum of the displayed values in the rows, instead, the only way to do this is by using the STATS TABLE CALC extension command to recompute the totals using the rounded values.

Here is how to do that. First, you need to create a Python module named customcalc.py with the following contents

def custom(datacells, ncells, roworcol):  
    '''Calculate sum of formatted values'''  
    total = sum(float(datacells.GetValueAt(roworcol,i)) for i in range(ncells))  
    return(total)

This file should be saved in the python\lib\site-packages directory under your Statistics installation or anywhere else that Python can find it.

Then, after your CTABLES command, run this syntax

STATS TABLE CALC SUBTYPE="customtable" PROCESS=PRECEDING  
/TARGET custommodule="customcalc"  
FORMULA="customcalc.custom(datacells, ncells, roworcol)" DIMENSION=COLUMNS LEVEL = -2  LOCATION="Total"   
LABEL="Rounded Count". 

That custom function adds up the formatted values in each row instead of the full precision values. If you have suppressed the default statistic name, Count, so that "Total" is the innermost label, use LEVEL=-1 instead of LEVEL=-2 ABOVE.

Answer 2

If you are using the CROSSTABS procedure to produce these figures then you should do using the option ASIS.