Need someone to look at my code and expain

[Pete]

I need an MVP to look at my code and explain why my workbook is not
working.

Basicly this workbook has worked flawlessly in the past. But one of my
users entered the required information and now it does not work.

Let me explain. The user enters data for accounting. The worksheet
compares data entered in rows and columns, then the data is compared to
the sum of another column. If the row and column data match the sum of
the other column then the report is to state "Sheet is in balance" if not
the report is to state "Sheet is out of balance by ###.##".

The problem is the sheet is in balance but excel is reporing the sheet is
out of balance. And when it does it reports that it is out of balance by
..00.

What i need is someone to look at this and tell me why excel is doing
this.

I will email this to anyone that wants to look at this.

Pete

 

[Michael J. Malinsky]

It could simply be an accuracy thing from somewhere out beyond two decimal
places. Maybe try using the ROUND function, something like:

=IF(ROUND(A1,2)=ROUND(B1,2),"Balances", "Does Not Balance")

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

 

[Medemper]

My guess would be someone entered a number with more than 2 decimal places somewhere, and your rounding format ###.## is showing .00 instead of say .0034.
I need an MVP to look at my code and explain why my workbook is not
working.

Basicly this workbook has worked flawlessly in the past. But one of my
users entered the required information and now it does not work.

Let me explain. The user enters data for accounting. The worksheet
compares data entered in rows and columns, then the data is compared to
the sum of another column. If the row and column data match the sum of
the other column then the report is to state "Sheet is in balance" if not
the report is to state "Sheet is out of balance by ###.##".

The problem is the sheet is in balance but excel is reporing the sheet is
out of balance. And when it does it reports that it is out of balance by
.00.

What i need is someone to look at this and tell me why excel is doing
this.

I will email this to anyone that wants to look at this.

Pete

 

[Dick Kusleika]

Pete

The problem is likely that there are precision errors in your workbook. Not
errors on your part, but rather imprecision in the way all computers
calculate decimal (base 10) numbers. If you have a formula like

=IF(SUM(E1:E10)=SUM(A1111),"Sheet In Balance", etc..)

then you should change it to

=IF(ROUND(SUM(E1:E10)-SUM(A1111,0)=0),"Sheet In Balance", etc..)

Actually, it may not be the reason that I described above. Otherwise you
probably would have had this problem long ago. If the user is entering
numbers that are summed (i.e. no multiplication or division), then it could
be that there is a stray extra decimal somewhere. For example, if in a cell
that has an Accounting format, you enter

1.451

when you meant to enter

1.45

it will display the same number and you may not be aware of the error.
Precisely why you have the formula that checks it, right? Scan through the
entered number and look at the formula bar as you do. You may see a three
decimal place entry when you expect two.

 

[Pete]

Dick, thank you, your round solution fixed the proplem. I did check every
cell for stray extra decimals but there were none. I will in the future
use the round function to fix this problem.

Thanks Again