My progress bar (created using conditional formatting) looks weird.
Some cells shows 100% as half the cell width and som as the complete width. Can anyone help?
(see pictures below)
Excel01.PNG
Excel02.PNG
My progress bar (created using conditional formatting) looks weird.
Some cells shows 100% as half the cell width and som as the complete width. Can anyone help?
(see pictures below)
Excel01.PNG
Excel02.PNG
As you only posted pictures we can not tell but are all the cells based on the same data range?
Databar CF will evaluate values in relation to eachother so if all cells are 100% it will fill half when you set percent.
Instead of choosing percent choose automatic
OR choose number and fill 0 as minimum and 1 (=100%) as maximum. that way your bars will show correct fill
I cant remember exact explanation why percent does not work but it has to do with how excel evaluates the applied range when you choose percent it will compare each score with eachother and that causes excel to see 100% as only half filled when there are only 100% values, you can check by putting 0% (or any other then percentage) in 1 of the cells in the CF applied range. then 100% bars will show ok.
so normally when even 1 cell with a percentage is other then 100% it works but when all values are 100% (which is normally the goal if you are doing percentage completion) it gives the strange bars, but since 110% completed task is not possible (no brownie points for extra effort ) I use the number limit as shown above.
You cloud also "trick" excel by hiding a 0% value in one of the cells of the applied range than it will also evaluate properly with all 100% scores
Edit: to clarify based on Andy Pope's comment. I ASSUMED that the first 2 cells that show correct databar are in a different CF-rule.
I just recognised the problem from one of my sheets where I had a simular issue and above adjustments solved the problem for me.
Last edited by Roel Jongman; 03-18-2019 at 05:23 AM.
Dear all
I found a solution but I'm not sure I understood how except that it has something to do with data range.
In the top cell I changed the rule to only count for that cell (it still looked weird after that). Then I deleted all rules for all the other cells except for the top one. Then I changed the data-range on the top one, to include all the cells and then it worked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks