Excel 2007 with a worksheet 180,000 lines deep … only 5 columns.

I prepared a data profile that included classifying the data and then using SUM to add up the values.

I prepared another analysis as preparation to draw a histogram and then used SUM to add up the values.

Then  I created a very simple IF statement to check that total 1 =  total 2 … they didn’t agree. For some reason, although ALL values have only two decimal places, when added together, total 1 could be expanded to …247.900883 while total 2 could be expanded to 247.90003.

As a workaround I amended the IF statement:

=IF(INT(A1)=INT(B1),”Reconciled”,”Error”) … that worked

and so did this:

=IF(ROUND(A1,3)=ROUND(B1,3),”Reconciled”,”Error”) but =IF(ROUND(A1,4)=ROUND(B1,4),”Reconciled”,”Error”) did NOT work, for what is probably an obvious reason!

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: