Problem with cell values outside their absolute differences

Hello,

Can anyone help me workout what is wrong with the formulas in my attached spreadsheet for checking the performance of some analyzers. I only need to highlight in 'red' any figures that are not within their 'absolute differences' but some cells are highlighting red even though there is no data within them and some cells are highlighting 'red' even though they are within the formulas parameter.

Any help would be much appreciated.

Thanks in advance,

Keith.

CooperHawke,

Please exam the outcomes of the conditional formatting I applied to the attached workbook.

If these appear to work I will explain the steps and the formulas. So far the CFs are applied only to the first 11 day sections of each sheet.

I took some liberties with the workbook.

• The sheetnames are all in A1 or each sheet. This is used as a reference for the lookup table B2:Q7.
• I also changed the HGB in row 11 to Hb in order to match the parameters in the lookup table.

The CF formula is

Formula:

Please Login or Register  to view this content.

I applied it to C12:Q15 then used Format Painter to apply it to the other day sections. I continued this on the other sheets. Done this way Excel will re-created 31 separate rules for each sheet. It beats hand typing them.

I suspect the MEDIAN(C12:C15) range will need some attention, but I will wait for your feedback on that.

Kieth, welcome to the forum
Thanls for the title change

Hopefully, Dave has answered your question?

CooperHawke,

Do you see what TheN is describing? That is exactly what I was concerned about

I suspect the MEDIAN(C12:C15) range will need some attention, but I will wait for your feedback on that.

I have not tried absolute referencing those ranges, yet. I suspect that is the answer. I was not ambitious enough to hand type that many separate CF formulas ... one for each day.

My attendance here has been light lately. I am hopeful to find a one-formula-fits-all-ranges CF. That way it could be Format Painter applied to all the remaining ranges.

If what TheN proposed does the job I will endeavor to find that formula. Please let me know.

CooperHawke,

I went ahead with a solution (?).

I took another liberty of putting a helper column in R12:R197 of each sheet. There are merged cells in column A. If you are not aware of it merged cells cause havoc for formulas. This helper column is a way out.

The formula in R12:R197 of each sheet is

Formula:

Please Login or Register  to view this content.

This enables a consistent ‘Day’ reference for each ‘Day’ range in order to set the MEDIAN range addresses absolute.

I selected C12:Q15 of sheet X-Check Level 1 and applied this conditional formatting rule. The formula is

Formula:

Please Login or Register  to view this content.

I then used Format Painter to apply the same formatting to each ‘Day’ range in all sheets. That’s 93 separated CF rules.

I have not done a painstaking check of the results. If I understand the MEDIAN addressing issue correctly the ones that I have checked are all correct.

Only cells X-Check Level 1 C31 and X-Check Level 2 J75 return positive.

Is this what you were expecting?

Keith,

This is a much better CF formula. It needs to be applied to sheet X-Check Level 1 range B12:Q195. (I had to include column B due to some merged cells between the ‘Day’ sub-ranges.) Once applied use format painter to apply to the remaining sheets.

Here is the replacement formula.

Formula:

Please Login or Register  to view this content.

This reduces the number of CF rules from 93 to 3. The results are the same as before.

I don’t know why I didn’t think of this before. My apologies.