Autofill area based on percentage [closed]

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 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

Sort by » oldest newest most voted

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.

PercentageArea.ods

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.

++++++++++++++++++++++++

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

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

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

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.

All clear???

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!

( 2014-06-21 16:48:50 +0200 )edit

@ROSt52 & @karolus - thanks!

( 2014-06-21 17:54:08 +0200 )edit

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?

more

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

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

LS

( 2014-06-21 23:07:20 +0200 )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.

( 2014-06-21 23:27:24 +0200 )edit

Nice! Thanks for the tip ;)

( 2014-06-21 23:49:20 +0200 )edit

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

( 2014-06-22 10:23:52 +0200 )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.

( 2014-06-22 10:44:29 +0200 )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.....

( 2014-06-23 10:31:10 +0200 )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?

( 2014-06-23 11:32:01 +0200 )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???

( 2014-06-26 15:16:30 +0200 )edit

@ROSt52 - Alive? Of course! See http://en.wikipedia.org/wiki/Fortran , 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 4.2.5.2 didn't improve conditional formatting so far. (Concepts too ambitious?)

( 2014-06-26 16:54:53 +0200 )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.

( 2014-06-27 10:14:00 +0200 )edit

Great! that's it!

Many thanks :)

more