Help on If function and conditional formatting

Question

My data goes like this. I have a conditin on my cells that states the following: =IF(C86<0,05; CONCATENATE(C85;"*"); C85).

I was using conditional formatting on the cells to that it would change the colour on cells greater and lesser than a certain value, but when I added the "*" with the if function, that conditional formatting stopped working and no longer sees the values with the "*" as lesser or greater than. Any help?

riny_van_eekelen · Answer

mieleko&nbsp;The &nbsp;CONCATENATE part of your formula creates a text and will not be recognised as a number when you test for "greater or less then" another number. What exactly is the purpose of adding the "*"?

riny_van_eekelen · Answer

mieleko&nbsp;Sorry, but what are "the p" and "the R" values? Can you upload a workbook that clarifies what you want to achieve?

riny_van_eekelen · Answer

mieleko&nbsp;Okay, now I get it (I think).&nbsp;&nbsp;You want to conditionally format the R-value on the basis of its own value AND on the basis of the corresponding p-value. Since you started with the * to indicate the lower p-value, I added one more CF rule that applies a custom number format 0.0"*"&nbsp;if the p-value is below 0,05. It displays the&nbsp;asterisk if the condition is met, but it does not change the&nbsp;underlying number.&nbsp;To keep this extra CF rule relatively easy, I introduced some helper cells (green shaded) that determine if the p-value is below 0,05 (TRUE) or not (FALSE).&nbsp;See if it works for you.

riny_van_eekelen · Answer

mieleko&nbsp;Glad I could help!

mieleko · Answer

Riny_van_Eekelen&nbsp;Im trying to add the * in order to mark values for which the respective p value is less than 0,05. The conditional formatting is to mark values of the cell itself (which refers to the R value) which are between a certain set of numbers

Forum Discussion

Help on If function and conditional formatting

7 Replies

Resources