I'm having trouble getting the average amount of a years worth of data to turn the particular shade of color I have it set to. I have the entire data sheet to turn Green if the value is between 99%-100%, Yellow 97%-98% and Red 1%-96%.

While the conditional formatting works great throughout the rest of the spreadsheet, it seems to be a hit or miss when it comes to the column that I'm finding the overall average of a particular category. For example in the picture attached it won't turn Yellow when it becomes 98% but if I start changing the data to cause the average to change to 100% it will eventually go Green. I can then put in lower amounts for the data to make the overall average change to 99% and it will remain Green, then change another piece of data slightly which ultimately keeps the average at 99% yet the green shade disappears and there is no fill at all. Any help would be appreciated. Thanks!1.JPG