Question about conditional formatting

I have a column of numbers for every day of the year (365 entries). These number represent a Quality Control number. I already have a cell to calculate the average of the total population of numbers. At the moment I have each cell conditionally formatted to highlight the cell red if the number in each cell is 10% over or under the mean average.

The problem I am having right now is that as the value of the QC changes over the year, it is retroactively applying that conditional formatting prior values, and I don't want it to do that. I don't want to retroactively change numbers that were once within parameters.

Thanks

You could work out an average for each week, or for each month, and then use the appropriate average as the baseline for comparing plus/minus 10%.

If you attach a sample Excel workbook, as detailed in the yellow banner at the top of the screen, we could suggest appropriate formulae for you.

Hope this helps.

Pete

I want to keep a running total population in each column (located at the bottom of each sheet). The 10% over/under should calculate the mean average of the total population for every day going forward. I do not want the conditional formatting to change the prior entries based on the "new" mean average.

I have attached what I have been working on.

Example, if you look at the tab 2020 and see the third entry under TG, the 163 is red. But on 1/6/20 that entry was acceptable and should not be red. My problem is right now, it has taken the mean average for 5 years (now 140) and it thinks that 163 is out of range.

I'm a little lost in your workbook, but I would imagine you want something like this in your conditional format formula. A1 would be the first row of the column where your data is.

= A1 / SUM($A$1:$A1) > .10

I'll try to give you a bigger picture. the book is a log book for quality control done on an MRI machine. The factors evaluated every day are the transmit gain, signal, noise, signal-to-noise ratio and the center frequency. These numbers change over time due to many factors, so if you looked at a trend chart you would see a gradual slope of the changes. Obviously, we want to know if there is a major change that could identify a problem with the machine.

Every day when we put in a new value, I want to know is this new value within the margin of error plus 10% or minus 10%. If it is within those margins, nothing happens to the cell. If it is outside those margins the cell will turn red alerting the Technologist something could be wrong. My problem is that the conditional formatting I have done is changing values done in the past which were perfectly normal back then because the average mean was different.

I don't want the change to a new average mean value to change the cells of past values red.

To avoid back-applying conditional formatting to previous QC values, you can use relative addressing functions in your conditional formatting formula. This will format each cell relative to the current average without affecting previous data. Update the conditional formatting to reference the cell with the current average using relative references in the formula. For example, if your average is in cell B1, the formatting for numbers greater than 10% of the average would be: =A1>B1*1.1, and for numbers less than 10% of the average: =A1<B1 *0.9.