Ask Your Question
-3

Percentage Bug Found

asked 2018-07-03 19:47:01 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

So I have found a major bug in LibreOffice, the percentage value function simply doesn't work. Screen shot attached:

Process: - Cell I4 is formatted with Percentage... i.e. Right click Format, select percentage and value... - Cell G and H are added together minus I4 which should take off the percentage but in this case takes a whole number off 11.... which is incorrect as £11.00 minus 11 percent should be £1.21 which should = £9.79 NOT £10.89

See screen shot

Please fix this most BASIC bug...C:\fakepath\Percentage Bug.JPG

edit retag flag offensive close merge delete

Comments

3

Please take the time to thoroughly word your question and give the actual formulae you used in the different cells.
Screenshots ar next to useless in such cases.
Quoting: "Cell G and H are added together minus I4"
If there not was the formula bar in the screenshot I wouldn have a clue.

Lupp gravatar imageLupp ( 2018-07-03 19:54:38 +0100 )edit

Also please do not post as community wiki. See guidelines for asking.

Jim K gravatar imageJim K ( 2018-07-18 15:53:51 +0100 )edit

4 Answers

Sort by » oldest newest most voted
6

answered 2018-07-03 20:04:54 +0100

Lupp gravatar image

updated 2018-07-18 11:36:33 +0100

The content of cell I4 is a number displayed in a percentage format. The display format doesn't afflict the numeric value of that 11% which is 0.11. Thus your formula =G4 + H4 -I4 (The SUM() there is just hokum.) calculates. 8 + 3 - 0.11 and that surely results in 10.89.

If you want to subtract 11% of a previously obtained result you need to first calculate the "percentage value" like in =(G4 + H4) * I4. You can also subract the rate of 11% from the total given as a percentage of 100% and then continue with =(G4 + H4) * (100% - I4)

Edit1:
The announced attachment.

edit flag offensive delete link more

Comments

Thank you for taking the time to answer my query, however I have to disagree. The software doesn't follow logical algorithmic set, what is the point of working out the value when the function in the programming can stipulate the value of a number as a percentage. I..E "format" = Percentage. This change in cell value should stamp the cell as the function. Having to recalculate first & then percentage after makes no sense in the literal form the cell declaring. Hope that helps.

Bug Finder gravatar imageBug Finder ( 2018-07-09 14:27:11 +0100 )edit

You may read my answer to another question here. The question there is different, but the topic and my answer may shed some light on expectable implications.
Your ""format" = Percentage" cannot be implemented. What you needed was, written in a sloppy way, %-format makes '+ CURRENT()*percentage'. Trying to implement such counter-mathematical concepts would mess up all.

Lupp gravatar imageLupp ( 2018-07-09 16:16:56 +0100 )edit

There lies my point, having to do a sloppy mathematical equations to merit a result, that in it self should be the justification for LibreOffice to re-visit the code and enhance the function, instead of having to manually try to write it. That's what these products should be doing, making it efficient rather than harder. Do you know how I can get in touch with the developer to change the code? Otherwise I will have to use my good old calculator which has a % button on it.

Bug Finder gravatar imageBug Finder ( 2018-07-09 22:55:07 +0100 )edit

Sorry. You are wrong. Trying to be "smart" regarding the expectations of one grouip of uers causes problems without end for others. There are standards concerning expressions, in specific operators and their precedence which must be regarded. Otherwise to solve an anyway simple task may be a bit simpler while tasks a bit less simple get unsolvable. What's the problem with =G4 + H4 - CURRENT()*I4? I would prefer to do it otherwise, but its exactly what your old (specialized) calculator does.

Lupp gravatar imageLupp ( 2018-07-10 00:18:37 +0100 )edit

In the specific case there's no chance, but generally feature requests are to file to bugs.documentfoundation.org in the same way as actual bugs with "Importance = enhancement".

Lupp gravatar imageLupp ( 2018-07-10 00:24:42 +0100 )edit

Once again your formula doesn't work in LibreOffice = Err:522, there is a real problem with the code in the spreadsheet, I have pulled what's left of my hair out today, trying to resolve and the simple fact is that LibreOffice cannot perform or calculate percentages, as insane as that sounds it can't. I found nothing but pain from LibreOffice. Now in Excel on my work laptop, works an absolute treat. I will log an issue with bug.documentfoundation.org, and fire it out on social media.

Bug Finder gravatar imageBug Finder ( 2018-07-14 16:59:35 +0100 )edit

Once again you don't make clear which one of the four formulae (3 in the answer, one in the comment) didn't work for you. And you didn't quote the formula returning the error.
Did you read the explanation of Err:522?
I will attach an example to my answer by editing.
If you don't word your bug report more thoroughly it will be completely useless.
(Don't make a fool of you in the "social media" you have in mind.)

Lupp gravatar imageLupp ( 2018-07-18 11:30:30 +0100 )edit

To summarize a bit -- the whole problem revolves around the definition of cell I4, which you have "formatted" as a percentage. Format has everything to do with appearance and may have nothing to do with the actual mathematical meaning of a number. So I4 would be more unambiguously defined by =0.11*(G4+H4), as already suggested by @Lupp.

"Never leave to semantics what mathematics can precisely define." As already suggested by @Mike-Kaganski.

ve3oat gravatar imageve3oat ( 2018-07-19 23:20:29 +0100 )edit
2

Quoting: "Never leave to {whatever else} what mathematics can precisely define."
Alas! Long ago spreadsheet software started to breach this wholesome principle. The current V6.1.0.1RC would return 1111.00% in J4 for =G4+H4+I4 under the given circumstances due to automatic formatting. The same even for =G4+I4+H4!.
Down with all this antirational nonsense! (Can I longer feel sure this will be fixed as a bug? It might be renamed a feature.

Lupp gravatar imageLupp ( 2018-07-20 11:00:33 +0100 )edit

The misuse of formats for a clumsy semantical approach to "pragmatic" behaviour or whatever has even reached the specifications in OpenFormula long ago or the basics of the implementation in AOO/LibO depending on the viewpoint, There you find 'Types' that are not seriously implemented at all. As a surrogate numeric formats are misused with very bad results.

Lupp gravatar imageLupp ( 2018-07-20 11:12:47 +0100 )edit
3

answered 2018-07-18 12:02:45 +0100

updated 2018-07-18 12:26:03 +0100

I'll try to rephrase what has been already exceptionally correctly been explained to you by @Lupp and @ve3oat. Maybe different wording could make it easier to grasp.

You seem to not understand what "percent" is. You have some kind of thinking habit, that tells you that a phrase "take eleven pounds minus eleven percent" is correct.

Actually, it is not. It is a shortcut that eats up some crucial parts of expression, and those who understand the essence of the math do understand what is being omitted; so using that incomplete (and thus incorrect) phrase is justified when everyone has a clue about the full phrase, which is the following:

  • take eleven pounds minus eleven percent of it (i.e., of eleven pounds).

People who understand what a percent is, will never forget that percent is another way to say 1/100. And they can interchangeably say (again, incompletely, taking the shortcut): "take eleven pounds minus eleven hundredths". And they would never ever think that the phrase is equal to "11-11/100". They realize that the real math is "11-11*11/100"

But it takes not a long time until people grow who are so much used to thinking with those shortcuts, that they start to forget (or even don't realize) that percent is just a multiplier, and it needs another multiplier to form a meaningful product. They start to believe that by applying a % to some number, that automatically allows to omit the base number from maths. They don't even realize that in the formula "8+3-11%", even if computer would try to guess what should it use for taking percentage of, it couldn't decide if 11% should be from (8+3) or simply from 3. Sigh... thinking is hard.

There's no error in the result Calc gives you. There's no bug here. You tell Calc to calculate =8+3-11/100. It gives you the correct result.

As to "cell as function". Who told you that that would solve the problem? Heh. Simple reasoning like that makes sad all those who often add or subtract percents before applying the resulting fraction to the base number. If a calculator has a shortcut useful for accountant (when they use it properly) doesn't mean it has universal value. Spreadsheets must only do what has been explicitly told to them. A "3 + 11/100" could also mean "300% + 11%". Get used to it. It's not a calculator.

edit flag offensive delete link more
1

answered 2018-07-04 20:38:37 +0100

ve3oat gravatar image

Further to the excellent answer by Lupp, you just need to think about it a bit.

"Percentage"

But, percentage of what?

You need to tell the percentage function which value to use to calculate the percentage. Once you have done that, it will work exactly as you expect it to.

edit flag offensive delete link more

Comments

Thank you, but Lupp was wrong I have actually found a bug in LibreOffice.

Bug Finder gravatar imageBug Finder ( 2018-07-14 20:07:00 +0100 )edit

You did? What is the bug number of the bug you reported?

erAck gravatar imageerAck ( 2018-07-18 17:03:00 +0100 )edit
0

answered 2018-07-18 17:23:00 +0100

jaragon gravatar image

Thank you, but Lupp was wrong I have actually found a bug in LibreOffice.

Excel gives the same results. Why don't you try to convince Microsoft first?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2018-07-03 19:47:01 +0100

Seen: 174 times

Last updated: Jul 18 '18