Forum Discussion
Solved
Bar length in cell based on value in another cell
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 |
- Let me correct myself. The formula in B2 should be:
=REPT(“|”,MIN(A2,99)&
IF(A2>99,
“X”&REPT(“|”,A2-100),
“”)
That could be
=REPT("X",MIN(A2,100)) & IF(A2>100,"|"&REPT("X",A2-100),"")Hi Sergei, That's what I want. Any idea how to improve on the "X" and so I get continuous bar?
Thanks
Andrew
- Swap the “X” with “|” in Sergei’s formula. For another, instead of -100, modify it to 101 so that the 100th character will display as “X”, all succeeding characters will display as “|”, and the total number of characters will be exactly equal as those of A2.
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.
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
- It would easily stretch to 250 rows
