Help on If function and conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2091154%22%20slang%3D%22en-US%22%3EHelp%20on%20If%20function%20and%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091154%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20data%20goes%20like%20this.%20I%20have%20a%20conditin%20on%20my%20cells%20that%20states%20the%20following%3A%26nbsp%3B%3DIF(C86%26lt%3B0%2C05%3B%20CONCATENATE(C85%3B%22*%22)%3B%20C85).%3C%2FP%3E%3CP%3EI%20was%20using%20conditional%20formatting%20on%20the%20cells%20to%20that%20it%20would%20change%20the%20colour%20on%20cells%20greater%20and%20lesser%20than%20a%20certain%20value%2C%20but%20when%20I%20added%20the%20%22*%22%20with%20the%20if%20function%2C%20that%20conditional%20formatting%20stopped%20working%20and%20no%20longer%20sees%20the%20values%20with%20the%20%22*%22%20as%20lesser%20or%20greater%20than.%20Any%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2091154%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2091183%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20If%20function%20and%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F940524%22%20target%3D%22_blank%22%3E%40mieleko%3C%2FA%3E%26nbsp%3BThe%20%26nbsp%3BCONCATENATE%20part%20of%20your%20formula%20creates%20a%20text%20and%20will%20not%20be%20recognised%20as%20a%20number%20when%20you%20test%20for%20%22greater%20or%20less%20then%22%20another%20number.%20What%20exactly%20is%20the%20purpose%20of%20adding%20the%20%22*%22%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2091184%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20If%20function%20and%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2091184%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BIm%20trying%20to%20add%20the%20*%20in%20order%20to%20mark%20values%20for%20which%20the%20respective%20p%20value%20is%20less%20than%200%2C05.%20The%20conditional%20formatting%20is%20to%20mark%20values%20of%20the%20cell%20itself%20(which%20refers%20to%20the%20R%20value)%20which%20are%20between%20a%20certain%20set%20of%20numbers%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

7 Replies

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

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

@mieleko Glad I could help!