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,...
- 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.
- Let me correct myself. The formula in B2 should be:
=REPT(“|”,MIN(A2,99)&
IF(A2>99,
“X”&REPT(“|”,A2-100),
“”)
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
