Jan 24 2021 02:51 AM
Jan 24 2021 02:51 AM
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?
Jan 24 2021 03:09 AM
@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 "*"?
Jan 24 2021 03:12 AM - edited Jan 24 2021 03:13 AM
@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
Jan 24 2021 03:15 AM
@mieleko Sorry, but what are "the p" and "the R" values? Can you upload a workbook that clarifies what you want to achieve?
Jan 24 2021 03:26 AM - edited Jan 24 2021 03:27 AM
@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
Jan 24 2021 04:13 AM
@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.
Jan 24 2021 04:45 AM
@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!