Highlight cell depending on values of adjacent cells

[thorr]

Hi community,

I thought that what I want to do would be rather simple, but after I spent a few hours on trying and searching the internet, I am finally here to ask you for ideas.

I have got a quantity that piecewise increases and decreases monotonically. So I would like to figure out my local extrema to some kind of determine the frequency and do some further evaluations at these points. I didn’t want to write a program for this because I thought it would be easier to do this using an application like Calc in the first place, but now I’m pretty surprised about how difficult that simple task seems to be.

I already tried to do something by appending a +STYLE(AND(H299<CURRENT(), H301<CURRENT()), "Red, "Green") to my formulas and applying them to all cells of that column (so that H299 and H301 also shift). Do you have any other ideas?

[kompilainenn]

do you want to use conditional formatting or formula in cells?

[jimk]

You already tried +STYLE… Did that do what you wanted? If not, did it produce errors? If it did work but you are looking for an easier way, then please give a complete example of what worked. For more hints about questions, see guidelines for asking.

[kompilainenn]

[erAck]

Make sure the cell cursor is on B2 (with the selected range B2:B13) when defining the conditional formula using $A2, so the relative reference of the reference points to the same row.

[linuxyz]

It works perfectly for me. It should be validated as the right answer if you ask me.

[jimk]

Normally, conditional formatting is best.

However, if you’re asking how to use STYLE correctly, here is an example.

=1+2+STYLE(IF(AND(H299<CURRENT();H301<CURRENT());"Bad";"Good"))

[erAck]

The STYLE() function should be avoided if possible. It exists for backwards compatibility and for some cases where conditional formatting depends on an interim result of a formula expression or results of volatile Add-In functions to be “styled” before conditional formatting could be executed. Also note that the result of CURRENT() depends on the exact position and precedence in the formula expression, and use of CURRENT() twice in one expression is almost guaranteed to fail the intention. Specifically the above example of AND(H299<CURRENT();H301<CURRENT()) does not what one could think it does, the two CURRENT() there do not result in the same value of the previous 1+2 expression. The first CURRENT() returns the value of H299 (as that was pushed to the stack as the latest operand before the CURRENT() was encountered), the second CURRENT() returns the value of H301 for the same reason. Hence the conditions and the AND() are never true.