In-cell progress bars?

[checkypantz]

Is there a way to have an Excel (2003) cell display a percentage
result from a calculation as a shaded percentage of a cell instead of
returning a result? It seems like that sort of thing should be in
Format Cells... > Shading, but there's nothing there. This is possible
to set up in an HTML cell by including a 1-pixel-wide graphic then
changing the width attribute of the img tag. Is there anything similar
in Excel?

Thanks,
-j

 

[Pete_UK]

One way you could do this is by means of this formula:

=REPT("|",100*A1)

if you have a percentage in A1 - it will repeat the pipe character
once for each percentage point. Adjust the column width to cope with
up to 100 of these, or adjust the scaling factor from 100 to 50 (or 25
etc).

Hope this helps.

Pete

 

[T. Valko]

That's available as a conditional formatting option in Excel 2007 but no
earlier versions.

 

[checkypantz]

What an awesome kludge. Thanks!

-j

One way you could do this is by means of this formula:

=REPT("|",100*A1)

if you have a percentage in A1 - it will repeat the pipe character
once for each percentage point. Adjust the column width to cope with
up to 100 of these, or adjust the scaling factor from 100 to 50 (or 25
etc).

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

What an awesome kludge. Thanks!

-j




- Show quoted text -

 

[Guest]

Good answer Pete, and it looks especially good if you format the cell with
Webdings and use a "g" instead of a "|" in the formula. The cells can also
use colored font for additional effect.

Vaya con Dios,
Chuck, CABGx3

 

[T. Valko]

Nice one, Chuck!

When I've done something like this in the past, I've done it sort of like a
Gantt chart and used conditional formatting to fill the cell colors. This of
course uses more than a single cell. If you needed a scale from 0 to 100 in
increments of 1 then you would need 101 cells. You can adjust the column
width depending on the scale needed so that 101 columns need only be a
couple of inches wide.

One drawback with the REPT(......) method is that it will only work on
integers. If the value were something like 20.7, REPT ignores the .7.

 

[Guest]

Thanks Biff............appreciate your comment.........yeah this does make a
kind of neat appearance for the REPT formula. I use it when reviewing
"cyclic" data also.......it makes it real easy to spot something out of
kilter...............
As for not picking up the decimals, no big deal I reckon as this is kinda
analog in nature and the exact number not too important........like blood
pressure, plus or minus a couple of points don't matter, its "about" where
it's at that counts.....in the 120's, or in the 160's. I also like to use
Speedometer Displays.....I hesitate to call them "charts" because the way I
make them they have nothing to do with charting, but look like a real
instrument...........back to the grindstone here...

Vaya con Dios,
Chuck, CABGx3