Feb 21 2019 07:05 AM
Hi, We have a report where a column shows a value, usually between 1 and 200.
We want to have a horizontal bar in the next cell whose length is based on the cell value.
Something like below, but I want it to be a continuous block, rather than "X". Ideally I also want the bar to have a mark to show where the 100 value, the "|" would do.
Any ideas?
Thanks
Andrew
Value | Bar |
100 | XXXXXXXXXX |
200 | XXXXXXXXXXXXXXXXXXXX |
50 | XXXXX |
175 | XXXXXXXXXXXXXXXXX |
85 | XXXXXXXX |
129 | XXXXXXXXXXXX |
Feb 21 2019 07:32 AM
Why not use a 2D bar graph? no point in reinventing the wheel. Just resize it to match your rows, see attached. Note I reversed the vertical axis so it would display in the correct order.
Feb 21 2019 07:36 AM
That could be
=REPT("X",MIN(A2,100)) & IF(A2>100,"|"&REPT("X",A2-100),"")
Feb 21 2019 07:37 AM
Hi Rich, Thanks for the speedy response. I actually have 250 rows so probably too many for a graph.
I have seen this done before but can't recall how. At the moment, I am using the rept() function to show "X" as representative of cell value. It's ok but I really would like a continuous bar.
Cheers
Andrew
Feb 21 2019 07:39 AM
Hi Sergei, That's what I want. Any idea how to improve on the "X" and so I get continuous bar?
Thanks
Andrew
Feb 21 2019 07:45 AM
Feb 21 2019 07:57 AM
You may try to find most close to that symbol, e.g. CHAR(110) with Windings font.
Another way is in B column add =A2, etc, and apply conditional formatting to column with data bar, set option not to show numbers and minimum value as zero.
Both variants are in attached.
And if separate 100 with conditional formatting, when use two helpers column, with MIN(A2,100) in first and the rest in second, apply data bar formatting to both
Feb 21 2019 07:59 AM
Feb 21 2019 08:45 AM
SolutionFeb 21 2019 08:45 AM
Solution