In LibreOffice calc, I want calculated values to be green and negative values to be red.
Switching value highlighting on or off gives me one or the other but not both. I’ve had no success with various formatting and conditional formatting attempts but I am not very adept.
The Value Highlighting feature shows the TYPE of the cell content:
- Constant String,
- Constant Numeric value,
- Result of a Formula
You need use the CF (Conditional Format) feature with some predefined Cell Styles.
2 Likes
This number format can’t make the calculated (i.e., from formula) values green. Indeed, a conditional formatting, making use of ISFORMULA, would be needed.
A clarification: what I meant was that the number format can’t discriminate between the calculated cells, and cells containing literals - to only make calculated cells green (which was asked here). As far as the question is worded, it shouldn’t matter if the calculated cells contain numbers, or maybe strings - they should be green (which is what the Value Highlighting does). And at the same time, all negatives (including calculated? or not?) should be red.
2 Likes
What about calculated values being negative, and what about directly entered positive or zero values ?
You surely won’t mean “red and green at the smae time”, do you?
To explain this and probably also comments by @mikekjaganski to more detail see attached example.
disask96775complicateCF.ods (42.3 KB)
Using CF you should always have a complete distinction of cases in mind. In many actual occasions you may then omit cases where the default format is what you want or merge some subsequent cases into one.
1 Like
Thank you responders, you are correct in that my desired results lack necessary refinement… In my case, my calculated values are all positive so I’m not concerned with wanting data to be both red and green, both negative and calculated, only a) positive and calculated (green) or b) negative or positive, but not calculated (red or blue/black).
It is possible using styles in your formula:
Create GREEN and RED styles.
=IF(xxxx>=0;xxxx&T(STYLE("GREEN"));xxxx&T(STYLE("RED")))
xxxx = your formula
1 Like
Please do not recommend the use of the STYLE() function. It’s a legacy and IMHO should rather be deprecated. It sets the cell style during formula evaluation, whenever a recalc is triggered for that formula, whereas conditional formatting is evaluated for the current view only, not replacing an applied cell style. STYLE() is also not interoperable with other spreadsheet implementations. The only real use of STYLE() is to temporarily highlight a cell with a timeout along with the call of an asynchronous Add-In function when that delivers its result.
See also Spreadsheet Functions
2 Likes