mooly 12:56 10 Apr 2009
Locked

Just wondered if there was something silly for this. A "calculation" gives wrong results for certain numbers.
MS Works 8 spreadsheet... Very briefly, set up a sheet to add the sum of a column (credits) and display the result. That's perfect. The next column (debits) simply subtract from this total. That gives perfect answers to, altering the final total correctly.
Where it goes wrong is a third cell that I can manually input a balance. This compares the running total with the manually entered one and if the two match it displays "Balanced" in another cell, if they don't match it shows "not balanced"
Certain numbers don't work for example, if the total is 431.77 and the manual input is 431.77 it's OK. Add 0.47 to the credits, which gives 432.24 and change the manual cell also and the result is incorrectly shown as "not balanced". Add a further 0.1 to the credits and re alter the manual cell and it then balances. Add 1000 to the credits (so it's 1431.77 etc) and then repeat the above and it's all correct.
When you are comparing the value of two cells how can some numbers work and not others ?

Pineman100 13:07 10 Apr 2009

Sorry if this is flippin' obvious, but are you 100% certain that you are totalling every cell in both the debit and credit columns? For example, if you're adding extra rows to columns that already have a totalling formula, does the extra row still fall within the range specified in the formula?

mooly 13:11 10 Apr 2009

Sure yes. No problem with incorrect numbers displayed. It's the comparing one cell with another and giving a yes/no result. Some values don't work.

Woolwell 13:46 10 Apr 2009

I suspect that there is something wrong with your formula.
Suppose credits are in column a then the sum of column a gives the total of credits.
Debits in Column B the total of column B gives the total of debits.
Use a line in Column C and in that subtract total column B from total column A to give your calculated balance. Then in column D enter your manual input. The formula for showing balanced/not balanced should be something like =IF(C5=D5,"Balanced","Not balanced") where the figures 5 equate to your calculated balance and the manual balance.
If you are already doing something like this apologies but computers don't make mathematical errors but formula inputs do.

Simsy 18:54 10 Apr 2009

but in Excel there is an option to calculate using values as displayed...

For example, if the ACTUAL figure in a cell is 3.142, but the cell is formatted to display only 1 decimal place, only 3.1 will be shown, but the value 3.142 will be used for calculation purposes... unless the option I refer to earlier is in place...

Now assuming you have such an option in Works spreadsheet, and it is NOT in place, so the calculation is based on the ACTUAL value in a cell, (and I suspect this is the default), if any of the cells that you are adding contain a formula, it is possible that the formula give a result that goes to several decimal points... If the cell in question is formated to only dispaly, say 2 decimal places, you might be seeing, as in the example I gave earlier, 3.14, but the spreadsheet is doing the calculation using a value of 3.142 Which would gave an "unexpected" result and fit with the symptoms of your problem.

Does that make sense?

Regards,

Simsy

Simsy 19:01 10 Apr 2009

..it is better explained here;
a short way down in the paragraph titled "Actual And Displayed Values"

Regards,

Simsy

Simsy 19:09 10 Apr 2009

With my apologies if this isn't the cause of the problem you have!

Regards,

Simsy

mooly 19:15 10 Apr 2009

The hard parts explaining it. I had wondered about "rounding" but it's only working in currency to two places.
I can definitely get numbers that seem to "confuse" the cell that does the comparison of "If A = B" then display such and such. I can clear a given number in the credit column say and put in a slightly different figure in the same cell and re input the manually updated cell to equal it and it gives an incorrect true or false result.
The cell that does the comparison is
IF(E20+E29=E19,"Balanced.","Not Balanced")

What I am trying to say, is for example 5.50 in the appropriate cells gives balanced, add 0.47 to each and the result is "Un Balanced". Start again with 5.50 and add say 0.20 to each and it's "Balanced". It's just certain numbers.

mooly 19:17 10 Apr 2009

Not had chance to study them yet... thanks.

mooly 19:36 10 Apr 2009

I don't think it's that. Numerically there is never an error. It's just in comparing the result of the dispaly values in the two cells.
After adding a few debits and credits and getting say 6.50 in both it's OK. Different values that might give say 6.33 and it's wrong. Add 10p (0.10) to each and they could be correct again.

Woolwell 20:40 10 Apr 2009

If E20 is the sum of the credits and E29 is the sum of the debits then I would think that you need to have E20 - E29. Puzzled by the fact that E19 is lower than E20.
What happens is you calculate credit - debit in a fresh cell then do If(fresh cell = manaul balance cell, balanced, not balanced). I hope I have made myself clear.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Illustrator Charles Williams on how to create magazines and book covers

iMac Pro review

Les meilleures prises CPL (2018)