Help on If function and conditional formatting

[mieleko]

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?

[#2]

@mieleko The  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 "*"?

[#3]

@Riny_van_Eekelen 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

[#4]

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

[#5]

@Riny_van_Eekelen I'll upload an example, since I cant post the original workbook. My excel is in portuguese, but I changed the formulas to english, hoping it might work on your excel

[#6]

@mieleko Okay, now I get it (I think). 

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"*" if the p-value is below 0,05. It displays the asterisk if the condition is met, but it does not change the underlying number. 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).

See if it works for you.

[#7]

@Riny_van_EekelenThank you so much! I think I understood it and got it working! You just saved me hours of work! Once again, thank you so much! 

[#8]

@mieleko Glad I could help!