I am trying to make a data sheet with some conditional formatting to make it easier to read. What I would like seems to pretty complicated. The rules I want to be followed are as follows: (for the example let's say A5=40%, A4=0%, A3=55%)
1) if A5 is >= 80% then be green (if this is true it can stop there)
2) obviously if this is not true the value in A5 is below 80% so I would like it to check if A5>0 (if it's 0 I don't want anything to happen so no color and stop)
-Since in my example A5>0 but less than 80% these rules so far don't apply
3)the next step would be that I want it to check this: if A5<A4 and A4 is not 0 be red (if A5>A4 then stop and no color)
-since in the example A4 is 0, I want the formula to keep going
4)I would want it to check this next: if A5<A3 and A3 is not 0 be red (if A5>A3 then stop and no color)
-this would mean that A5 should be red
To give more context, the table I am making is data from a test giving to an individual over several weeks. Each row would represent a different week. The problem is that in some weeks the test was not given, so that is why there are some zeros. I would like my table to show me green if the individual scored 80% or above during any given week. I want it to show me red if the score was lower than the previous week (if they scored 86% and then 84% it doesn't matter, I want them both green). This is where the zero's also give me trouble. If the individual scored 75% in week 3, we didn't test in week 4, and they scored 70% in week 5, I would want that shown as a decrease from week 3 (red) not an increase from week 4. I can only get my formula to check against week 4. Obviously, the individual decreased from the last time they had the test which was week 3 and that's why I want it to show red. So in general, any time there is a decrease from the last time a test was given (a week that wasn't 0) I would like it to display red. I hope someone can follow this and help me out!!
1) if A5 is >= 80% then be green (if this is true it can stop there)
2) obviously if this is not true the value in A5 is below 80% so I would like it to check if A5>0 (if it's 0 I don't want anything to happen so no color and stop)
-Since in my example A5>0 but less than 80% these rules so far don't apply
3)the next step would be that I want it to check this: if A5<A4 and A4 is not 0 be red (if A5>A4 then stop and no color)
-since in the example A4 is 0, I want the formula to keep going
4)I would want it to check this next: if A5<A3 and A3 is not 0 be red (if A5>A3 then stop and no color)
-this would mean that A5 should be red
To give more context, the table I am making is data from a test giving to an individual over several weeks. Each row would represent a different week. The problem is that in some weeks the test was not given, so that is why there are some zeros. I would like my table to show me green if the individual scored 80% or above during any given week. I want it to show me red if the score was lower than the previous week (if they scored 86% and then 84% it doesn't matter, I want them both green). This is where the zero's also give me trouble. If the individual scored 75% in week 3, we didn't test in week 4, and they scored 70% in week 5, I would want that shown as a decrease from week 3 (red) not an increase from week 4. I can only get my formula to check against week 4. Obviously, the individual decreased from the last time they had the test which was week 3 and that's why I want it to show red. So in general, any time there is a decrease from the last time a test was given (a week that wasn't 0) I would like it to display red. I hope someone can follow this and help me out!!
