shading a cell or group of cells based on value

[pat67]

I am trying to color cells based on a value. I have tried using data
bars conditional formatting and while that says it's supposed to have
a longer bar for a higher value, it doesn't. no matter what value is
in there it always shows half the cell colored. Is there a way to show
this? It would be a percentage. so if the value is 75% i am looking to
see 75% of the cell colored. Any help would be appreciated. Thanks.

 

[Gord Dibben]

I think you misunderstand the results of data bars.

Not to be used on single cells

A data bar colors the cell based on the value of that cell relative to the
values of other cells.

One cell with a value of 75% means nothing unless other cells are included for
comparison.

Even then, 75% value does not mean 75% of cell will be colored.

Depends upon the values of the included cells.


Gord Dibben MS Excel MVP

 

[pat67]

I think you misunderstand the results of data bars.

Not to be used on single cells

A data bar colors the cell based on the value of that cell relative to the
values of other cells.

One cell with a value of 75% means nothing unless other cells are included for
comparison.

Even then, 75% value does not mean 75% of cell will be colored.

Depends upon the values of the included cells.

Gord Dibben     MS Excel MVP





- Show quoted text -

Obviously I do. Do you know a way to shade based on percentage? I
should say a relatively easy way.

 

[Pete_UK]

Obviously I do. Do you know a way to shade based on percentage? I
should say a relatively easy way.- Hide quoted text -

- Show quoted text -

Assuming your percentage value is in A1, you could have a formula like
this in B1:

=REPT("|",100*A1)

Format that cell to have a colour of your choice, then copy down. You
might need to adjust the width of the column, and you can always use a
different factor (instead of 100) to adjust how wide the response is.

Hope this helps.

Pete

 

[pat67]

Assuming your percentage value is in A1, you could have a formula like
this in B1:

=REPT("|",100*A1)

Format that cell to have a colour of your choice, then copy down. You
might need to adjust the width of the column, and you can always use a
different factor (instead of 100) to adjust how wide the response is.

Hope this helps.

Pete- Hide quoted text -

- Show quoted text -

That sort of works. I have to try and modify it to what I am doing.
Here's my issue. I have a start date, planned hours and actual hours.
I have to show where we should be versus where we are. These people i
work with are insane. There is software to do this with i.e. MS
Project, but no one knows how to use it so I have to try and make work
in Excel. Like i said. insane. Thanks though. I will see if i can work
with that formula

 

[Gord Dibben]

Here's another one.

Column B at width of 20 and Courier New font.

With .75 in A1 enter this in B1

=REPT(CHAR(149),A1*CELL("width",B1))


Gord