Strange and inconsistent result from a simple formula

[Jack Sadie]

In order to assist in explaining this problem, I have been trying to add a
jpg extract of the spreadsheet, but this seems to result in the message not
arriving on the newsgroup - presumably because of the danger of virus in an
attachment. I am trying again without the attachment, but sorry - it will
need your close attention to understand it.

I have a cash analysis book in the form of an Excel spreadsheet.

The Cell F64 at the bottom of page 1 is the sum of a column of figures
(Actual value
£12484.70)
Each cell in column F is the sum of any items from columns G to R in the
same row

Likewise each cell G64 to R64 inclusive is the sum of that relevant
column.

In order to check the validity of each row I have a dedicated check column
"E",
every cell of which carries the same relative formula.

Thus the simple formula in E64 reads :-

=IF(F64-SUM(G64:R64)=0,"","Problem")

With certain exceptions, the value of each cell in column E therefore reads
blank as it should do.

The exceptions include E64 which reads "Problem"

BUT...I have introduced a formula in cell S64 : "=SUM(G64:R64)"
and this gives value of £12484.70 which equals the value of Cell F64 and
therefore
means that there is no problem !!

I am therefore totally perplexed why Cell E64 indicates there is a problem ?

This difficulty does not occur at row 127, the similar row at the bottom of
the next page (page 2), but does again at row 155 which is the row of totals
at the end of the year
in the middle of the 3rd page.

Hope I have explained adequately.
What is my error? Can anyone help please ?

 

[Ronald Dodge]

Check through your formulas as it may require quite close scrutiny. Also,
be sure you don't have circular references as this could very well be
causing such symptoms. Without us knowing what the formulas are that you
are attempting to use in columns G through R, it would be rather tough to
help find the issue. As you trace this backwards, it can be rather time
consuming and tedious depending on the scale and complexity of your formulas
and data.

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Peo Sjoblom]

Try to wrap the cells with ROUND

=IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem")

 

[Jack Sadie]

Thanks ever so much, Peo.

Well I copied that off and pasted it in place of my formula, and I am
pleased to say it has been effective; at least it now shows a blank cell
where previously it read "Problem". Likewise I have copied that to the other
problem cells with similar success. And as a check if I change one of the
cells in a random position to deliberately force an error, it clearly gives
the required result and delivers the word "Problem" !!!!
So again my very sincere thanks.

BUT, please can you tell me why that is so ? What was wrong with the
formula I was using?
I am not familiar with the function "Round", but I thought it had to do with
rounding to a given number of digits after the decimal place, or is that off
the mark?

Also should I therefore be using that formula for every cell in the column
or just those cells which are dealing with column totals at the bottom of
each page?

--
Regards, Jack Sadie
(e-mail address removed)

Try to wrap the cells with ROUND

=IF(ROUND(F64,2)-ROUND(SUM(G64:R64),2)=0,"","Problem")

 

[Peo Sjoblom]

Most likely you had extra decimals due to the way computers treat numbers
If you use the sum formula where you had a discrepancy without the ROUND,
then copy it as values and format as general and widen the column you might
find
some extra decimals


http://www.mcgimpsey.com/excel/pennyoff.html

http://www.cpearson.com/excel/rounding.htm


--
Regards,

Peo Sjoblom

Thanks ever so much, Peo.

Well I copied that off and pasted it in place of my formula, and I am
pleased to say it has been effective; at least it now shows a blank cell
where previously it read "Problem". Likewise I have copied that to the
other problem cells with similar success. And as a check if I change one
of the cells in a random position to deliberately force an error, it
clearly gives the required result and delivers the word "Problem" !!!!
So again my very sincere thanks.

BUT, please can you tell me why that is so ? What was wrong with the
formula I was using?
I am not familiar with the function "Round", but I thought it had to do
with rounding to a given number of digits after the decimal place, or is
that off the mark?

Also should I therefore be using that formula for every cell in the column
or just those cells which are dealing with column totals at the bottom of
each page?

 

[Jack Sadie]

OK thanks. I'll have a look into that.
Cheers.

 

[Guest]

Assuming that you are only adding and subtracting numbers with no more than 2
decimal places, your original formulas would have been fine if you had used
numbers like 1248470 (hundredths of pounds) instead of 12484.70 (pounds).
Excel's arithmetic is correct, but most decimal fractions can only be
approximated in binary (as discussed in Peo's 2nd link), and your final
formula is detecting the residual effects of these initial approximations to
your inputs. Integers (up to 15 digits) can be exactly represented, but the
only 2-place decimal fractions that can be exactly represented are .00, .25,
..50, and .75. Peo's suggestion of rounding intermediate results then works
because it is reducing the impact of these approximations.

If you throw multiplication/division into the mix (such as interest
calculations, etc) then you also need to be aware that Excel retains its full
precision despite how you may have formatted the cells (as discussed in Peo's
first link).

Jerry

 

[Gord Dibben]

Jack

If you have many of these to add the ROUND function to, here's a bit of code to
help.

Sub RoundAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & ",2)"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Jack Sadie]

As it happens there aren't many, but thanks so much for your trouble. I'll
store it for the future.

 

[Jack Sadie]

Really grateful for that generous explanation. I think that also answers my
question regarding the other cells - seems like a good idea to use the
rounding in each cell of the check column.