Conditional Format

[Nikki]

****This question is not answered by other posts. If it is I need help
seeing how. I do not have any data in any of the cells. Can someone please
help or tell me it is hopeless. Thanks-

I am trying to make a gantt chart in excel that will put a percent finished
for status. To do this I need to have excel count the number of cells that
have a certain background color. This works fine when I do not use
conditional formatting, but to make the chart user friendly I need the
conditional formatting. Is there anyway to accomplish this? ( I have two
bars one for how long the project should take and one for the current date.
I want to comapre the two bars to get a percent finished.)

 

[Biff]

Hi!

What is the condition that applies the conditional
formatting? What formula are you using?

Biff

 

[Nikki]

=AND(G$1>=$B15,G$1<=$C15)-This is the formula I am using this in all the
cells to make a bar(in conditional formatting). This compares the dates
across the top to the start and end dates and colors them in. I need to
count how many cells are filled with a color compared to the predicted amount
of time it should take. So I would need a count If type of equation.

 

[Biff]

Hi!

Well, I need more info or would need to see the file. That
being said, this is totally "doable". Here's an idea:

Use an IF function to insert a space " " into the cells
and use a separate conditional formatting formula:

=IF(AND(G$1>=$B15,G$1<=$B15)," ","")

Then you can use a conditional formatting formula that
tests the cells for the space:

=LEN(CELL_WHATEVER)=1

Now you can use a formula that counts the number of cells
that contain a space:

=SUMPRODUCT(--(LEN(B4:K4)=1))

Notice in the IF function the " ", and the "". These are
NOT the same!

Now you can count the number of cells that have the
conditional formatting and calculate your percentage.

To get your percentage, something like this:

=SUMPRODUCT(--(LEN(B4:K4)=1))/COUNT_OTHER_CELLS*100

Biff

 

[Biff]

Slight typo:

=IF(AND(G$1>=$B15,G$1<=$B15)," ","")

should be:

=IF(AND(G$1>=$B15,G$1<=$C15)," ","")

Biff

 

[Nikki]

Biff-That is a wonderful idea of a way to get something in the cell that
excel can see!!!! Thanks a million I have been going nuts over how to do
this!!!

 

[Biff]

Glad to help. Thanks for the feedback!

Biff

-----Original Message-----
Biff-That is a wonderful idea of a way to get something in the cell that
excel can see!!!! Thanks a million I have been going nuts over how to do
this!!!


.