Ask Your Question

Autofill area based on percentage [closed]

asked 2014-06-21 15:10:52 +0100

luc.tsar gravatar image

Hi all,

I'm looking for the way to color an area based on single cell percentage value: let's say we have a 10*10 cell square area (100 cells total) in which each cell represent the 1% of the total.

Now, I would like to color this area according a single cell value which would stand as percentage: example 0.36 => 36% of the total area filled.

Any method to achieve this?

Many thanks for your kind help. LS

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-25 22:20:14.966448

3 Answers

Sort by » oldest newest most voted

answered 2014-06-21 15:50:01 +0100

ROSt52 gravatar image

updated 2014-06-21 17:56:24 +0100

I think it is possible. Please look at the attached file. I used a simple formula, which you can recognize when you click into the cells.


You sill see the calculated values in all cells. If you extend the conditional formatting also to the font, you will see only colored cells.

The zero value is a bit tricky because the real value is approx. -1.4E-16 thus no an exact 0. Thus I used the -0.01 value in the conditional formatting to change the color.

If you still have a question, please ask.

EDIT 1 after comments:

I opened the conditional formatting window Condition 1 makes the back ground blue and white

image description

I added condition 2, which makes the color of the font in the cells with blue background blue

image description

I added condition 3, which makes the font of the white cells white

image description

Note: In my case the font in condition 2 did not turn blue. However, I could not find the cause and the solution for it. Please test and see if the result is a blue font or a black font.

You can access "conditional formatting" in the Format > Conditional Formatting > Condition. The conditional formatting is the key element.

It might be good to read about "conditional formatting" in the free manual which you can download from:

All clear???

edit flag offensive delete link more


It works! But I don't know what you've done. E.g. "If you extend the conditional formatting..." : Where do you find this? I can see that in cell format there's a 0.00% value that must be related, but can't see where you actually set the cyan background, etc. Could you add this to your answer? Thanks!

David gravatar imageDavid ( 2014-06-21 16:48:50 +0100 )edit

@ROSt52 & @karolus - thanks!

David gravatar imageDavid ( 2014-06-21 17:54:08 +0100 )edit

answered 2014-06-21 22:07:56 +0100

Lupp gravatar image

updated 2014-06-21 23:37:45 +0100

This was thought to be a comment - but wanting to attach a file I had to change it to an answer.

I had a look into the solution by ROSt52 and also tried two different but similar solutions ask35789ColourFractionArea001.ods. There were - not the first time - upsetting experiences with the present implementation of conditional formatting. Might someone test the attached file and tell me if the irregularities are also to see elsewhere?

edit flag offensive delete link more


There's a way to hide numbers and show color only?

Tried many times with styles but no way at the moment.


luc.tsar gravatar imageluc.tsar ( 2014-06-21 23:07:20 +0100 )edit

Think you mean the solution (not correctly working because of CF flaws) with the border around? There are no hidden numbers. The calculations are exclusively done evaluating the formula inside the CF-condition. The cells themselves are empty (blank). A way to hide numbers that ARE present and also text, of course, is using a style with background and font in the same colour. The same effect can be obtained by direct formatting.

Lupp gravatar imageLupp ( 2014-06-21 23:27:24 +0100 )edit

Nice! Thanks for the tip ;)

luc.tsar gravatar imageluc.tsar ( 2014-06-21 23:49:20 +0100 )edit

@Lupp - Very interesting approaches!. Need a bit more time to study and understand some of the more complexer functions you used. Great solutions.!

ROSt52 gravatar imageROSt52 ( 2014-06-22 10:23:52 +0100 )edit

@ROSt52 - Thank you, You are very polite. I am just a tinkerer. The approach -as far as the formulae are concerned- is the same for both solutions. Just the evaluation is done offstage (by the condition-formula) in the first case and onstage (by the cell-formula) in the other case. The idea comes from old days (about 1965) when I sometimes had to care for memory mapping myself programming for the computers of that time in ALGOL 60.

Lupp gravatar imageLupp ( 2014-06-22 10:44:29 +0100 )edit

@Lupp - Tinkering around on something is in my experience a strong driver of research and development. I do the same and when I get stuck, I do have a look into the LibO guides. ---- ALGOL in the mid 60s... I did it in the early 70s.....

ROSt52 gravatar imageROSt52 ( 2014-06-23 10:31:10 +0100 )edit

@ ROSt52 "---- ALGOL in the mid 60s... I did it in the early 70s....." Nowadays next to nobody still seems knowing anything about ALGOL (60, later 68) and its being progenitor to PASCAL - and by that of Delphi. Oh what a beutifully structured language! And how fast its compiling and linking! I didn't stick to programming much in my later life - but up to this day I do not understand the reasons for preferring C/C++ over Object Pascal (which is available for all relevant platforms in the form of FreePascal). What is it what I missed?

Lupp gravatar imageLupp ( 2014-06-23 11:32:01 +0100 )edit

@Lupp - I also touched PASCAL a bit but in my last research years I used again the language I learned first: FORTRAN. Unfortunately I don't know the modern languages c/c++ except for their names. I actually wonder if FORTAN is in research still alive???

ROSt52 gravatar imageROSt52 ( 2014-06-26 15:16:30 +0100 )edit

@ROSt52 - Alive? Of course! See , I didn't really use FORTRAN for programming but I had to study some custom libraries and to port small parts of them to (flavours of) PASCAL. By the way: LibO Calc V didn't improve conditional formatting so far. (Concepts too ambitious?)

Lupp gravatar imageLupp ( 2014-06-26 16:54:53 +0100 )edit

@Lupp - Wikipedia link - thanks! Very interesting to see that FORTRAN is still VERY MUCH alive. Seeing the punchcard picture created very sentimental feelings. I started with such punchcards for my first FORTRAN 66 program.... - Conditional formatting - I was not aware of a is bigger change to come. So far conditional formatting never brought me to things which were not possible. Need to install 2.4.5 these days. To much of my surprise I have already 2.4.5 in the antergos linux distro.

ROSt52 gravatar imageROSt52 ( 2014-06-27 10:14:00 +0100 )edit

answered 2014-06-21 22:10:38 +0100

luc.tsar gravatar image

Great! that's it!

Many thanks :)

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-06-21 15:10:52 +0100

Seen: 835 times

Last updated: Jun 21 '14