Simple multiplication doesn't seem to be working

English
#1

I’m beating my head against the wall with this one. I’ve got a spreadsheet that does a simple if comparison then multiplies the result of that by a constant.

The multiplication result isn’t right.

A perfect example of the problem is on row 22 of sheet 6. The IF is working properly and returning a value of 1.17 units. That is then multiplied by 25 - which should give a result of 29.25 but the spreadsheet returns 29.17. There are similar errors (they aren’t rounding errors, they’re just plain wrong answers) on some of the other sheets.

I’ve looked and looked but I can’t figure out why. I’d be very grateful if someone could take a glance and see what I’m missing.Driver Incentive 2020-07-02.ods

#2

Could you please also provide a screenshot of Sheet6.G22 with increased number of shown decimal digits? :wink:

#3

@mikekaganski Done

#4

Thanks @JohnSUN!

#5

By the way, I must thank @anummy for providing exact place (cell address) where to look in the question. It’s just too often when people not only don’t do that (thinking, possibly, that others would click everywhere trying to find the problem), but even send screenshots, not the actual documents…

#6

@mikekaganski Totally agree with you. I upvote this post for such a high-quality description. Thanks @anummy!

#7

The multiplication result isn’t right.

For what it is worth, the calculations are correct in your spreadsheet. The rounding error is in the rendering. When you use a display format with 2 decimals, the result will be rounded. Calc will still use the exact value.

So what do you need?

If you regard the rounded unit value a “transaction level”, you should use the rounding suggested in the answer from JohnSUN. This makes the actual value correspond to the displayed value.

If you need to determine exact cost/impact or whatever it is you do, you may need to use the exact value and not round until “transaction point” (expense entered, payment submitted, etc.). It is not uncommon for intermediate monetary values to include fractions of the penny. This is often required for correct assessments where you work with large numbers of low cost items.

#8

You were right when you suspected a rounding error. And you were wrong when you decided that this simply cannot be. These are indeed rounding errors. See for yourself

rounding.png
rounding.png597×553 24.4 KB

So, solution is

=ROUND(G22;2)*25
#9

Allow me to make an additional comment: There seem to be many users, who assume, that formatting to 2-digitals also does affect the stored values (I’d call that the “Implicit-Rounding-by-Formatting-Assumption”). These users don’t clearly differentiate between data stored (and used for any further calculations) and the values presented to them (by Formatting). To state clearly: The “Implicit-Rounding-by-Formatting-Assumption” is a false assumption. And this question along with its answer demonstrates that perfectly.

#10

Thanks for the help folks. It was my assumption on the displayed value as you all pointed out.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.