Conditional formatting causing strange calculation error

[ledsales]

I run a very small business and my accounting system is a reasonably complex workbook I created in Excel some years ago. Recently I shifted it to Libre Calc, and have found a few issues, mostly to do with syntax/search text requirements being slightly different between the two programs. I have fixed them all, but have discovered a strange bug to do with conditional formatting.

Each bank account has a pair of worksheets (income and expenditure) and these are summed up in various places. One of those places is a running total on each worksheet, just so I don’t have to keep flipping back to the summary worksheet all the time.

Now, those summary fields are formatted as 2 decimal place currency fields, and have conditional formatting such that when the balance goes negative, the text goes red. That’s it, nothing complex, and it works in excel perfectly.

However, in Calc, when the total in the fields is positive, the formatting is as expected, eg as in A

But, as soon as the field goes negative, only the red conditional formatting works, the original field formatting breaks (the dollar sign and 2 decimal point requirements go awol) and you get a weird result like B:

The formatting on that cell is set as in C:

Is this a known bug? I can simply toggle the correct and incorrect behaviour by simply forcing the cell to switch between positive and negative numbers.

Also, if you force the cell to exactly zero, the formatting works, but if you force it to -1, you get D:

All very odd, and looks like a calculation bug to me.

[PKG]

@ledsales ,
How should the supposed error be recognized on the basis of a picture?
Please attach an anonymized calc file.

[mikekaganski]

Note that in Calc, conditional formatting applies a cell style, which is a complete set of formatting options in itself. Meaning: applying a conditional formatting, the applied style will replace everything, not only color - it will include e.g. number of decimals.

[keme1]

Initial thoughts

• In spreadsheets for accounting, it is wise to apply rounding at every “transaction level” operation. Otherwise, rounding errors will accumulate, yielding seemingly wrong results.

• You mention that it “works in Excel”. If a spreadsheet file is edited in - and saved from - both LO Calc and MS Excel, it is highly likely that the differences between the two apps will accumulate “translation error residue” as it moves back and forth between the two.
  This happens regardless of which file format you are using for saving. Either way, one of the apps will be working in a “foreign format”. I can’t see how that would cause the behavior you report here, but it is worth noting.

Resolve ambiguity

While what you displayed can technically be described as conditional formatting, it does not show use of the tool named “conditional formatting”. If you are also using that, @mikekaganski’s comment explains the Calc “misbehaving”. If not, there is something else at play.

Help us to help you

Attach a sample file with confidential info mangled/removed (but check that it still displays the unwanted behavior), as @PKG also requested.

[ledsales]

Thanks for the responses guys, I should clarify a few things. The numbers that are calculated in that cell, come from a few other cells, all of which only ever reference two digit decimals, and only through addition and subtraction, so there is no sensible way that the number there should be anything other than a two digit decimal. There is no division or multiplication going on, so no opportunity for 10 decimal places to randomly spring up.

Below is the conditional formatting for the cell, as you can see, it is simple, it’s not clear how it can affect the number of decimal places of the actual cell formatting, the two things are independent in excel, so would expect them to be here as well?

I haven’t created a sanitised version of the file, will try to get to that tomorrow.

[robleyd]

@ledsales

all of which only ever reference two digit decimals, and only through addition and subtraction, so there is no sensible way that the number there should be anything other than a two digit decimal. There is no division or multiplication going on, so no opportunity for 10 decimal places to randomly spring up.

Perhaps this is the case for your or I adding such numbers; however we are talking computer calculations here which don’t work quite like you may think. See Accuracy for more information. I would second @keme1 s advice to round your results.

[JohnSUN]

And I would like to ask for details of the cf1 style - perhaps the reason for the error is hidden there in the Numbers section?

[ledsales]

Ok, it was an issue with the style, I didn’t realise that it wouldn’t understand the style from excel, I haven’t had to edit them before. Once I set it correctly, all was fine. Not sure why it was displaying the long strings of numbers after the decimal point, maybe excel is smart enough to automatically round all numbers to the cell format settings, not sure, I’ve just never come across this issue until I used Calc.

But, all is good now, so thanks everyone for your input, obviously still got a bit of a learning curve ahead of me.

[JohnSUN]

@ledsales

maybe excel is smart enough to automatically round all numbers

Take my word for it - Calc is not dumber than Excel, it is more polite. Instead of forcibly cutting off numbers that it considers unnecessary, it offers the user to choose whether to cut off anything in the calculation results.

[Wanderer]

@ledsales

all of which only ever reference two digit decimals, and only through addition and subtraction

Then you might even consider “precision as shown” from the Screenshot shown by @JohnSUN
.
But remember always: Calculations are done not in decimal mode. All numbes are calculated in binary, so there are rounding errors by design.

Double-precision floating-point format

[ledsales]

@JohnSUN

Take my word for it - Calc is not dumber than Excel, it is more polite. Instead of forcibly cutting off numbers that it considers unnecessary, it offers the user to choose whether to cut off anything in the calculation results.

To me, logically, if I format a cell to show currency with two decimal places, then it’s a given that I only want two decimal places as per the format. I think that excel is more integrated in that respect, in Calc there seems to be multiple places you have to set formatting and styles, which may be fine for the programming minded folks and those who know about this, but remember, you want this to be a direct replacement for excel for everyone, not just a small percentage. In excel, I set the format once and get exactly what I expect.

But, don’t get me wrong, I think Calc and Libre Office are brilliant, that’s why I’ve moved to it, but the most important goal in any software that wants to appeal to the masses is user experience. If it’s difficult to use and does things unexpectedly, especially when there is a defacto standard, which, like it or not, is excel, then that’s what it needs to emulate. The average person does not want to relearn everything when shifting to a different package that effectively is meant to do exactly what their existing software does.

Another great example is the Affinity suite of programs. They are nice, but they work completely differently to the industry standards photoshop, illustrator and indesign, so the vast majority of publishers just won’t use them, despite them being much cheaper.

It’s all about user experience, it really is.

[Wanderer]

@ledsales

but the most important goal in any software that wants to appeal to the masses is user experience.

In this we can agree, but for everything else I have to disagree completely. I prefer to have choices in software, not only on the vendor/producer, but also on the way they work.

@ledsales

especially when there is a defacto standard, which, like it or not, is excel, then that’s what it needs to emulate. The average person does not want to relearn everything when shifting to a different package that

Ok, so for software wich does anything different than excel, but still is a spreadsheet, we need to issue warnings like “Coffee may be hot” or “If window is not labeled Excel, don’t expect Excel inside.” “The average user of Excel is not supposed to use this software. Please confirm to have read the docs…”
.
Also Microsoft should have issued this kind of warnings, when they invented ribbons and violated the de facto standard of using long menus… (One of my colleagues sweared for 2 years, when Ivgave her the choice of newer MS-Office or LibreOffice instead of Office2003).

@ledsales

Another great example is the Affinity suite of programs. They are nice, but they work completely differently to the industry standards photoshop, …

A good example, because I think this shows another point: A big group of persons never used photoshop, so there is no advantage for them to have a clone of photoshop. Affinity is free to invent another approach to handle this.
.
IMHO a lot of the open source world is driven by the idea to have choice, even if some are guided by licences (git was invented, because somebody didn’t give a free licence any longer). So we have several distributions of linux beside FreeBSD, we have gnome, kde, xfce, cinnamon … To have choices is not always easy, but this is a price I accept for freedom.
.
I actually liked OpenOffice using regular expressions instead of wildcards (unknown to the DOS-world) and Writer to work style-based. So I welcome to have choice, even when I don’t understand/like all decisions made by devs.
.

[PKG]

The screenshot shows a conditional formatting created in Excel and opened with Calc.
In this, the date format of the weekends is destroyed by a number format in the Default cell template.

This could also be the reason for this problem!

If you remove the number format, everything is fine again.

[karolus]

@Wanderer

liked OpenOffice

LibreOffice?