Calculating percentage based on cell color?

[cmlloveless]

I have another task that I have been asked to add to my wonderful training spread sheet. As personnel expire on their annual/biannual/semiannual training the cells now turn red.

Is it possible to calculate a percentage off of this? I need to know a percentage of how many cells are expired or red in the spread sheet. That way I don't have to keep manually calculating a "readiness" percentage everytime they ask for it.

So lets say 10 cells are red (which meand they have expired) and the remaining 130 cells are either green or yellow (which means they are not expired), then we are roughly at 93% ready or complient on training.

 

[cmlloveless]

<TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=80 border=0><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl71 id=td_post_2791763 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #8db4e3" width=80 height=21>Job</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #8db4e3" height=22>Comp</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #fde9d9" height=20>annual / x1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Courier New'; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>7/1/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Courier New'; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>6/1/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>x</TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #92d050" height=19>2/1/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>10/1/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>10/1/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: yellow; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Courier New'; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>8/1/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Courier New'; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>7/1/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Courier New'; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>7/13/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Courier New'; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20>7/1/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 8pt; BACKGROUND: red; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Courier New'; HEIGHT: 15pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>x</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" height=20>11/1/2010</TD></TR>







<TR style="HEIGHT: 15.75pt" height=21><TD class=xl72 id=td_post_2791763 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" align=right height=21>40.00%




</TD></TR>

</TBODY></TABLE>
This one is my favorite column because I have all three, dates, x's and blanks. I need a percent that is a combination of non blank cells (x's) and non expired dates. So this percent should read 67% (basically all the green cells divided by the total cells)

 

[Ruddles]

They all have conditional formating to turn them red when they are over 365 days. The 11.11% was from using the formula I just posted. I want it to say 88.88% though.

The simplest way would be to use 1 minus the formula you're using.

 

[Ruddles]

I also have some that look like this without a date. How to I calculate the percentage that are not blank?

If the range is, say, R1:R20, it would be:-

=counta(r1:r20)/rows(r1:r20)

 

[Ruddles]

This one is my favorite column because I have all three, dates, x's and blanks. I need a percent that is a combination of non blank cells (x's) and non expired dates. So this percent should read 67% (basically all the green cells divided by the total cells)

I make that 11/18 = 61% - no?

=(COUNTIF(R1:R18,"<"&NOW())+COUNTBLANK(R1:R18))/ROWS(R1:R18)

 

[cmlloveless]

Thank you for your help! This is what I came up with using what you gave me and tinkering until I figured it out.

=(COUNT(E4:E21)-(COUNTIF(E4:E21,"<"&NOW()-365))-COUNTBLANK(E4:E21))/COUNT(E4:E21)

 

[Ruddles]

Glad you got there in the end. Thanks for the feedback.