[Solved] £3.04 x 365 Does Not Equal £1,110.46

[Alex in Grimsby]

Hi everyone, Whilst I have been using both spreadsheets in general and Open Office in particular for many years, I cheerfully admit that my underlying mathematical competence is low level, (I failed 'O' level Maths . . . twice!!). Therefore it is entirely possible that this current issue that I am facing is the result of my inadequate understanding. The Subject line above and the attached screenshot file are together pretty self-explanatory. So feel free to have a smile at my expense, but please be gentle in assisting me.

[Villeroy]

I will never understand why people upload screenshots of documents instead of the document itself.
And I do not understand why people calculate the sum of a single value. I mean, S10*365 gives one result and the sum of one resulting value does not make any difference.
The value in S10 must be 3,04236 but a screenshot does not reveal the true value nor how it gets into S10.

[MrProgrammer]

Therefore it is entirely possible that this current issue that I am facing is the result of my inadequate understanding.

Just because cell S10 displays as £3.04 doesn't mean its value is 3.04. I will guess that your formatting for the cell rounds the value to two decimal places for display. You didn't attach your spreadsheet, so I can't tell what the value is, nor what the formatting is. The damned screenshot does not contain that information. Always attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself) when you post in the Calc forum. The cell's value is probably closer to 3.0423561644, and that value multiplied by 365 and rounded to two decimal places is 1110.46.
[Tutorial] Ten concepts that every Calc user should know

Numeric calculations are done with the cell's value, not with the number's display format, unless you enable option OpenOffice.org Calc → Calculate → Precision As Shown.

=SUM(S10*365)

To multiply two numbers, just use =cellA*cellB instead of =SUM(cellA*cellB).

I have entered £53.08. In cell G27 I have the formula =SUM(E27*52) which is returning the answer £2,760.00. The correct answer should he £2,760.16.

We answered essentially the same question for you eleven months ago. You can find your previous posts using View your posts at the top of the page. Reviewing that discussion would have been much easier for you than creating a second post about the same question. I gave you the link to the tutorial above then. You need to study that until you understand it. Otherwise you will continue to have trouble with the simplest problems. I told you that you should attach a spreadsheet document when you had the January question and you did not then, nor did you today. I am done helping you now. We can't help when you refuse to follow advice you've been given.

And I do not understand why people calculate the sum of a single value.

I'm baffled by this as well, though it's common enough that I write about this foolishness in Ten Concepts. I do have a theory to explain it: People are "afraid" of a naked multiplication =cellA*cellB and feel they have to protect it with a function. I am surprised they choose to use SUM though. If one insists on using a function, =PRODUCT(cellA*cellB) or better yet =PRODUCT(cellA;cellB) seem like more obvious methods. But maybe people are lazy and SUM is shorter to type than PRODUCT. Function =N(cellA*cellB) would be shorter still. "De gustibus non est disputandum."

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[John_Ha]

3.04 x 365 will never be exactly equal to 1109.60 because decimal numbers like 3.04 and 1109.60 cannot be represented exactly by the binary numbers used in computers. The answer will be something like 1109.6000000000000000012 or 1109.599999999999999999999999.

Now, if you ask "Is the answer exactly equal to 1109.60?" you get the answer NO because it is very, very, very slightly different - like measuring the 3,000 mile distance between London and New York to an accuracy of less than 1 millionth of an inch. The difference is so tiny it makes no difference.

So, read the Tutorial on Calc to understand how numbers are represented. Set Precision as shown in Tools > Options > Calc > Calculate ..., which means that if a cell shows 1109.60 the value stored will be 1109.60 and not 1109.6000000000000000012 and all will be well. Or don't ask "Are they equal?", ask "Is the difference between them less than some appropriate small number like 0.0001?" and again all will be well.

[karolus]

But maybe people are lazy and SUM is shorter to type than

No, they are dumb and lazy, they never type SUM into Formulabar, they klick on the SUM-button, because that has worked since first time they entered a formula.

[Alex in Grimsby]

Dear MrProgrammer, I DID ask that respondees be gentle with me, but you seem to feel the need to have a real tilt at me, how sad. Today you say: " I told you that you should attach a spreadsheet document when you had the January question and you did not then, nor did you today. I think I am done helping you now." I do not know how old you are, but in spite of still retaining most of my marbles, at the age of 77 I have to acknowledge that my memory is not what you in particular would wish it to be. It is not especially unreasonable to expect me to recall in December something you told me in January, but my reality is that nowadays I quite frequently forget something I was told far more recently than twelve months ago. If you are not in my decade yet, then maybe you might wish you had been more kind when you do reach it. In the meantime, don't worry, I forgive you, and I will give serious consideration to leaving a Forum that includes such intolerant members. Best wishes.

[Alex in Grimsby]

Dear Villeroy, You are of course quite right in complaining that screenshots are often uploaded rather than the full file/document. As someone who has helped others in the past, computer-wise, I can completely agree with you that providing full details of an issue is far more helpful and likely to result in a better, often swifter, resolution. So with no implied intention of making an excuse for myself, may I briefly explain why I sent a screenshot? I have a great deal of experience over very many years of using computers. Above all else, that experience has taught me one over-riding lesson - I am always going to find things I don't know, or can't do on a computer! My expertise has quite high levels in some things, and only very basic competence in others. So in this context, whilst I can do screenshots, it never occurred to me to remove all sensitive data from my spreadsheet file whilst retaining its actually functionality. I actually have not the slightest idea how to achieve that - especially when the spreadsheet in this case is one of ten sheets that make up one file.

Just to complete my reply, I did open my original post by acknowledging my limited mathematical understanding. A good illustration of that incompetence relates to your other comment. You said: "And I do not understand why people calculate the sum of a single value. I mean, S10*365 gives one result and the sum of one resulting value does not make any difference." You may find it hard to grasp this, but I do not understand your two sentences at all - especially the second, where you say "S10*365 gives one result and the sum of one resulting value does not make any difference". I have no idea what that means.

[gpgrego]

Try this formula, May be it can help you.

[RusselB]

Another useless use of the SUM function.

Code: Select all

=round(s10;2)*365

will return the same information without requiring Calc to use the processes associated with the SUM function.

[Hagar Delest]

"And I do not understand why people calculate the sum of a single value. I mean, S10*365 gives one result and the sum of one resulting value does not make any difference." You may find it hard to grasp this, but I do not understand your two sentences at all - especially the second, where you say "S10*365 gives one result and the sum of one resulting value does not make any difference". I have no idea what that means.

The SUM() function is to... sum several numbers: SUM(A;B;C) will sum A+B+C. Thus summing a single value makes no sense: SUM(A) = A.

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the *EDIT button if your issue has been fixed.

[keme]

At the practical level, the use of SUM() for a single value is not needed, and to some users it may be confusing or even annoying.
From a mathemathical standpoint, I'd say that the sum of one single element is perfectly valid, and in some cases it will provide a more consistent notation.
The practical and theorethical perspectives overlap to a great deal. In this case the use of SUM() is not contributing to the issue. Ejecting adjectives like "dumb" and "lazy" towards people who admit that their expertise is not with this software, nor with maths in general, is kicking someone who is already down. Not because he is an opponent, but just because he is down.
I'll leave that right there...

The issue comes from improper rounding, as MrProgrammer quickly and precisely noted. There is a difference between stored value (as calculated by formula) and displayed value (how stored value is rendered, according to format).

• Currency format will by default round the displayed value to two decimals.
• The stored value, as used for further calculations, still holds all the decimals of the original calculation.

The solution to this is, as gpgrego and RusselB correctly suggests, to use the ROUND() function in your formula, so that displayed value is exacly the same as what is displayed. This is pertinent for every calculation point which signifies a transaction (actual money transfer) because transactions are (mostly) limited "to the penny".

Do not use ROUND() indiscriminately, as this may mess up intermediate calculations. The worst case of this is the use of "precision as displayed" as will be advised in other places. That is a "catch-all rounding" which is very rarely a good idea. Please note: Your calculations are about averaging and extrapolation (intermediates), not daily payments (transactions). The result you got originally is better than the one with rounded values which you see as "corrected".

Mantra: Round at transaction point.