Forum Discussion
Help on If function and conditional formatting
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 "*"?
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
mieleko 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 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
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.