for the column titled "Progress", i have set conditional formatting rules to display each cell at different colors when having different percentages. However, some of the cells are not showing any colors at all,
(you can see the attached mini-sheet)
(you can see the attached mini-sheet)
excel issue.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | Total Progress | Total Expected Progress | Color Legend | |||||||||||||||
2 | 32% | 28% | Exceeding | On Track | At Risk | Off Track | ||||||||||||
3 | ||||||||||||||||||
4 | Progress | Expected Progress | ||||||||||||||||
5 | sample | 30% | 29% | start date | finish date | start value | target value | unit | current value | |||||||||
6 | sample | 30% | 29% | 8/1/2021 | 11/30/2022 | 0 | 100 | % | 30 | |||||||||
7 | ||||||||||||||||||
8 | sample | 22% | 27% | start date | finish date | start value | target value | unit | current value | |||||||||
9 | sample | 19% | 27% | 8/1/2021 | 12/31/2022 | 0 | 210000 | JD | 40000 | |||||||||
10 | sample | 20% | 27% | 8/1/2021 | 12/31/2022 | 0 | 70000 | JD | 14000 | |||||||||
11 | sample | 29% | 27% | 8/1/2021 | 12/31/2022 | 0 | 70000 | JD | 20000 | |||||||||
12 | sample | 20% | 27% | 8/1/2021 | 12/31/2022 | 0 | 350000 | JD | 70000 | |||||||||
13 | ||||||||||||||||||
14 | sample | 45% | 56% | start date | finish date | start value | target value | unit | current value | |||||||||
15 | sample | 50% | 66% | 8/1/2021 | 2/28/2022 | 0 | 100 | % | 50 | |||||||||
16 | sample | 40% | 46% | 8/1/2021 | 5/31/2022 | 0 | 100 | % | 40 | |||||||||
sample |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2 | E2 | =AVERAGE(I5,I8,I14) |
F2 | F2 | =AVERAGE(J5,J8,J14,) |
I5:J5 | I5 | =AVERAGE(I6:I6) |
I6,I15:I16,I9:I12 | I6 | =(Q6-N6)/(O6-N6) |
J6,J15:J16,J9:J12 | J6 | =IF(L6>TODAY(),0,(IF(M6<TODAY(),100%,((DAYS(TODAY(),L6)/(DAYS(M6,L6))))))) |
I8:J8 | I8 | =AVERAGE(I9:I12) |
I14:J14 | I14 | =AVERAGE(I15:I16) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B14 | Expression | =AND(J14>0, I14=0, J14>0%, J14<=33%) | text | NO |
B14 | Expression | =AND(J14>0, I14=0, J14>33%, J14<=66%) | text | NO |
B14 | Expression | =AND(J14>0, I14=0, J14>66%, J14<=100%) | text | NO |
B14 | Expression | =AND(J14=0,I14>0) | text | NO |
B14 | Expression | =AND(J14=0,I14=0) | text | NO |
B14 | Expression | =AND((I14/J14)>100%) | text | NO |
B14 | Expression | =AND((I14/J14)>66%,(I14/J14)<=100%) | text | NO |
B14 | Expression | =AND((I14/J14)>33%,(I14/J14)<=66%) | text | NO |
B14 | Expression | =AND((I14/J14)>0,(I14/J14)<=33%) | text | NO |
I12 | Expression | =AND(L12<TODAY(), I12=0, J12>=0, J12<=33%) | text | NO |
I12 | Expression | =AND(L12<TODAY(), I12=0, J12>33%, J12<=66%) | text | NO |
I12 | Expression | =AND(L12<TODAY(), I12=0, J12>66%, J12<=100%) | text | NO |
I12 | Expression | =AND(L12>TODAY(),I12>0) | text | NO |
I12 | Expression | =AND(L12>TODAY(),I12=0) | text | NO |
I12 | Expression | =AND(L15<TODAY(),(I12/J12)>100%) | text | NO |
I12 | Expression | =AND(L15<TODAY(),(I12/J12)>66%,(I12/J12)<=100%) | text | NO |
I12 | Expression | =AND(L15<TODAY(),(I12/J12)>33%,(I12/J12)<=66%) | text | NO |
I12 | Expression | =AND(L15<TODAY(),(I12/J12)>0,(I12/J12)<=33%) | text | NO |
I11 | Expression | =AND(L11<TODAY(), I11=0, J11>=0, J11<=33%) | text | NO |
I11 | Expression | =AND(L11<TODAY(), I11=0, J11>33%, J11<=66%) | text | NO |
I11 | Expression | =AND(L11<TODAY(), I11=0, J11>66%, J11<=100%) | text | NO |
I11 | Expression | =AND(L11>TODAY(),I11>0) | text | NO |
I11 | Expression | =AND(L11>TODAY(),I11=0) | text | NO |
I11 | Expression | =AND(L14<TODAY(),(I11/J11)>100%) | text | NO |
I11 | Expression | =AND(L14<TODAY(),(I11/J11)>66%,(I11/J11)<=100%) | text | NO |
I11 | Expression | =AND(L14<TODAY(),(I11/J11)>33%,(I11/J11)<=66%) | text | NO |
I11 | Expression | =AND(L14<TODAY(),(I11/J11)>0,(I11/J11)<=33%) | text | NO |
I10 | Expression | =AND(L10<TODAY(), I10=0, J10>=0, J10<=33%) | text | NO |
I10 | Expression | =AND(L10<TODAY(), I10=0, J10>33%, J10<=66%) | text | NO |
I10 | Expression | =AND(L10<TODAY(), I10=0, J10>66%, J10<=100%) | text | NO |
I10 | Expression | =AND(L10>TODAY(),I10>0) | text | NO |
I10 | Expression | =AND(L10>TODAY(),I10=0) | text | NO |
I10 | Expression | =AND(L13<TODAY(),(I10/J10)>100%) | text | NO |
I10 | Expression | =AND(L13<TODAY(),(I10/J10)>66%,(I10/J10)<=100%) | text | NO |
I10 | Expression | =AND(L13<TODAY(),(I10/J10)>33%,(I10/J10)<=66%) | text | NO |
I10 | Expression | =AND(L13<TODAY(),(I10/J10)>0,(I10/J10)<=33%) | text | NO |
I9 | Expression | =AND(L9<TODAY(), I9=0, J9>=0, J9<=33%) | text | NO |
I9 | Expression | =AND(L9<TODAY(), I9=0, J9>33%, J9<=66%) | text | NO |
I9 | Expression | =AND(L9<TODAY(), I9=0, J9>66%, J9<=100%) | text | NO |
I9 | Expression | =AND(L9>TODAY(),I9>0) | text | NO |
I9 | Expression | =AND(L9>TODAY(),I9=0) | text | NO |
I9 | Expression | =AND(L12<TODAY(),(I9/J9)>100%) | text | NO |
I9 | Expression | =AND(L12<TODAY(),(I9/J9)>66%,(I9/J9)<=100%) | text | NO |
I9 | Expression | =AND(L12<TODAY(),(I9/J9)>33%,(I9/J9)<=66%) | text | NO |
I9 | Expression | =AND(L12<TODAY(),(I9/J9)>0,(I9/J9)<=33%) | text | NO |
I6 | Expression | =AND(L6<TODAY(), I6=0, J6>=0, J6<=33%) | text | NO |
I6 | Expression | =AND(L6<TODAY(), I6=0, J6>33%, J6<=66%) | text | NO |
I6 | Expression | =AND(L6<TODAY(), I6=0, J6>66%, J6<=100%) | text | NO |
I6 | Expression | =AND(L6>TODAY(),I6>0) | text | NO |
I6 | Expression | =AND(L6>TODAY(),I6=0) | text | NO |
I6 | Expression | =AND(L9<TODAY(),(I6/J6)>100%) | text | NO |
I6 | Expression | =AND(L9<TODAY(),(I6/J6)>66%,(I6/J6)<=100%) | text | NO |
I6 | Expression | =AND(L9<TODAY(),(I6/J6)>33%,(I6/J6)<=66%) | text | NO |
I6 | Expression | =AND(L9<TODAY(),(I6/J6)>0,(I6/J6)<=33%) | text | NO |
E2 | Expression | =AND(F2>0, E2=0, F2>0%, F2<=33%) | text | NO |
E2 | Expression | =AND(F2>0, E2=0, F2>33%, F2<=66%) | text | NO |
E2 | Expression | =AND(F2>0, E2=0, F2>66%, F2<=100%) | text | NO |
E2 | Expression | =AND(F2=0,E2>0) | text | NO |
E2 | Expression | =AND(F2=0,E2=0) | text | NO |
E2 | Expression | =AND((E2/F2)>100%) | text | NO |
E2 | Expression | =AND((E2/F2)>66%,(E2/F2)<=100%) | text | NO |
E2 | Expression | =AND((E2/F2)>33%,(E2/F2)<=66%) | text | NO |
E2 | Expression | =AND((E2/F2)>=0,(E2/F2)<=33%) | text | NO |
B8 | Expression | =AND(J8>0, I8=0, J8>0%, J8<=33%) | text | NO |
B8 | Expression | =AND(J8>0, I8=0, J8>33%, J8<=66%) | text | NO |
B8 | Expression | =AND(J8>0, I8=0, J8>66%, J8<=100%) | text | NO |
B8 | Expression | =AND(J8=0,I8>0) | text | NO |
B8 | Expression | =AND(J8=0,I8=0) | text | NO |
B8 | Expression | =AND((I8/J8)>100%) | text | NO |
B8 | Expression | =AND((I8/J8)>66%,(I8/J8)<=100%) | text | NO |
B8 | Expression | =AND((I8/J8)>33%,(I8/J8)<=66%) | text | NO |
B8 | Expression | =AND((I8/J8)>0,(I8/J8)<=33%) | text | NO |
B5 | Expression | =AND(J5>0, I5=0, J5>0%, J5<=33%) | text | NO |
B5 | Expression | =AND(J5>0, I5=0, J5>33%, J5<=66%) | text | NO |
B5 | Expression | =AND(J5>0, I5=0, J5>66%, J5<=100%) | text | NO |
B5 | Expression | =AND(J5=0,I5>0) | text | NO |
B5 | Expression | =AND(J5=0,I5=0) | text | NO |
B5 | Expression | =AND((I5/J5)>100%) | text | NO |
B5 | Expression | =AND((I5/J5)>66%,(I5/J5)<=100%) | text | NO |
B5 | Expression | =AND((I5/J5)>33%,(I5/J5)<=66%) | text | NO |
B5 | Expression | =AND((I5/J5)>0,(I5/J5)<=33%) | text | NO |
I15:I16 | Expression | =AND(L15<TODAY(), I15=0, J15>=0, J15<=33%) | text | NO |
I15:I16 | Expression | =AND(L15<TODAY(), I15=0, J15>33%, J15<=66%) | text | NO |
I15:I16 | Expression | =AND(L15<TODAY(), I15=0, J15>66%, J15<=100%) | text | NO |
I15:I16 | Expression | =AND(L15>TODAY(),I15>0) | text | NO |
I15:I16 | Expression | =AND(L15>TODAY(),I15=0) | text | NO |
I15:I16 | Expression | =AND(#REF!<TODAY(),(I15/J15)>100%) | text | NO |
I15:I16 | Expression | =AND(#REF!<TODAY(),(I15/J15)>66%,(I15/J15)<=100%) | text | NO |
I15:I16 | Expression | =AND(#REF!<TODAY(),(I15/J15)>33%,(I15/J15)<=66%) | text | NO |
I15:I16 | Expression | =AND(#REF!<TODAY(),(I15/J15)>0,(I15/J15)<=33%) | text | NO |