• 462K Members
• 7,419 Online
• 559K Conversations
SOLVED

New Contributor

# 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

9 Replies

# Re: Bar length in cell based on value in another cell

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.

# Re: Bar length in cell based on value in another cell

That could be

=REPT("X",MIN(A2,100)) & IF(A2>100,"|"&REPT("X",A2-100),"")

# Re: Bar length in cell based on value in another cell

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

# Re: Bar length in cell based on value in another cell

Hi Sergei, That's what I want. Any idea how to improve on the "X" and so I get continuous bar?

Thanks

Andrew

# Re: Bar length in cell based on value in another cell

It would easily stretch to 250 rows

# Re: Bar length in cell based on value in another cell

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

# Re: Bar length in cell based on value in another cell

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

# Re: Bar length in cell based on value in another cell

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

# Re: Bar length in cell based on value in another cell

Thanks all!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies