Conditional Formatting a Table with Blank Cells

Jbutz11

New Member
Joined
Oct 13, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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!!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top