Conditioning formatting failing

[bob33]

Attached workbook has conditional formatting applied that only works in the first row of data (row 3).
I've tried to change to formula to only apply to specific cells but Excel either then deletes the formula in other cells in the range or misapplies this new formula to other cells.
I don't know if I'm doing something wrong or if my installation is corrupted.
The formatting that is desired is illustrated in row 3 for the conditions explained in row 2.

TIA!

[etaf]

dont see any explanation
Also the order of the conditional formatting
Its testing M3 & N3
then doing the colour scale
you dont have stop if true

Otherwise i see colours
screen shot attached

[bob33]

The issue is the the formulae I use for row three work correctly.
However, these formulas are written specific to row three cells, i.e., $w$3, $x$4, as absolute references.
If I change these to relative references, i.e., $w4 for the next row, the formulas in row three also change.
I've tried to make the formatting relative to row 3 cells, i.e., d3, but then it doesn't work for following rows, i.e., d4.
Does this help clarify my issue?

[bob33]

PS. A key issue is for the mid point, the formula references the values in column m and n and are different for each row. If I copy the formatting for row 3, and apply it to the rest of the rows, the absolute references for this formula don't change from the correct references for row 3.

[AliGW]

Relative references cannot be used in colour scales.

Explain in words what you are trying to achieve.

[bob33]

This is to track swimming pool chemistry values. The cell color will change based on the Ideal range values for each in column N and O.
The low test and high test rules work fine but when the three-color rule is applied, it falls apart because Excel wants to use absolute values and not relative ones.
If you look at the formula for the three-color in row three:
the Midpoint formula is: =$N$3+(($O$3-$N$3)*0.5)
For row 4, it should be =$N$4+(($O$4-$N$4)*0.5)
Now, Excel uses that same formula for row 4 but since the Ideal Range values are different for row 4 than row 3, it shows a wrong color range for row 4.
If I try to change the setting to only apply to the cell in row 4, and correct the formula to represent row 4, it removes the formula for the other rows.
How can each row have the Three-color rule work without the same formula applied to each row?
The sample sheet (now v2), has sample values for May 10 and May 15. The blue background columns headers show that the Conditional Formatting rules work for row 3 but not the other rows. In use, these blue columns will not be there, these are there for diagnostic purposes.

[etaf]

relative address dont work with Excel icon sets
SO one way to get a relative address is to use an OFFSET() formula

so in the icons for LOW I used
=OFFSET($N$3,ROW()-3,0,1,1)
For High
=OFFSET($O$3,ROW()-3,0,1,1)

And MIDpoint

=OFFSET($N$3,ROW()-3,0,1,1)+((OFFSET($O$3,ROW()-3,0,1,1)-OFFSET($N$3,ROW()-3,0,1,1))*0.5)

Then just for a REFERENCE to see if formula worked in columns , see Q,R & S - Just to see the working - NOT used

I then removed the other conditional format rules and just applied the Icon Set to see if they worked OK

What do you think ?

[bob33]

Etaf,
Thanks for the efforts but it doesn't seem to work for me. Example, if cell d7 is changed to 70, it should show yellow/amber as this is midvalue of the low 60 and high 80.
Here, the cell background is still green.
Any thoughts?
Thanks,
Bob

[etaf]

OK,
i did a search and found this site
http://dailydoseofexcel.com/archives...ve-references/

Which explains that OFFSET does not work for a range ,

So i just selected row 3
changed the applied to range to be D3 to I3
then selected D4 to I13
The cleared all the rules from that selection

Next
copied D3 to I3
then
selected
D4 to I4
PASTE - Special > Formatting
then
selected
D5 to I5
PASTE - Special > Formatting
then
selected
D6 to I6
PASTE - Special > Formatting
And repeated each row , upto ROW 13

see what you think attached

[bob33]

In case anyone wants it; final version attached.