Understanding formula sintax on conditional formatting
Hi everyone.
I've been googling for a long time and I can't find an answer to what I've been looking for.
Let's put the things simple: I would like a tutorial on how to use formulas on conditional formatting. Simple, right? But I can't find it anywhere... at least not the way I would like to find...
usually I find only examples of especific formulas, but none of them explains, for example, why I put "=B2 < 100", or how to use correctly the "IF" formulas, or even how can I apply a conditional formatting to a table that, if it changes size (by adding more table lines), so the created rule automatically would apply to these new added lines; no one explains HOW TO CREATE the formulas from zero.
The things I found until now only tell me that, in short, I shall put in things that I can't understand one hundred percent...
Don't get me wrong, although... I understand how to use formulas, I even change recorded macros to get especific results, so I am not a newbie... but I REALLY need a complete explanation regarding using formulas on conditional formatting, so I can create my own formulas understanding key concepts of what to do and what not to do... I need a friendly tutorial on sintax of formulas on conditional formatting and that is that...
Thanks!!
Last edited by maglorluinwe; 2014-05-30 at 02:35 PM.
It's simple, you just need to create a formula that generates a result of TRUE, that will trigger the formatting part of CF. You rarely need an IF, because a conditional test, such as the one you show, will generate TRUE or FALSE, so the formatting either applies or it doesn't. If you understand formulas, you should have no problems.
Okay, it is simple, but still does not answer my question.It is simple if you think ONLY about the examples I gave, but... those were only examples...Originally Posted by Bob Phillips
What I want, for short, is to understand how to enter any formula I want, and I believe I can acomplish this only by understanding the sintax, the logic excel uses when I am entering a formula on the Conditional Formatting fields, savvy?
If I ask how to CF a cell when the value is bigger than 10, I would get an answer. Great, and simple.
Then, I have to do another kind of CF... I would google it and find the answer, or ask again... someone gives me the answer, okay... great. Again, problem solved.
Then, I have to, again, do another kind of CF... Google again... or ask again...
Then, another CF... google...
Savvy? I don't want an answer regarding just my example, that is too simple, as you said. I want an answer regarding all the questions I might have about entering a custom formula on CF. What is the logic behind that? How to enter any formula I want? What to understand to do it correctly? That, at least at my point of view, is harder to accomplish, and to answer...
Sorry for being so obnoxious, but I really want to REALLY UNDERSTAND what I am doing, even if I need to read a long article about CF (Since it answers my questions)
Anyone?
Thanks
There is no really long answer about CF. You said you know aboutr formulas, CF uses formulas, so you should be able to answer any question you encounter.
Hello maglorluinwe - I will attempt to make you understand conditional formatting how it works. specially with the formulas -
Usually Formulas answers based on your input. let's take an eg : = if ( 100>50,"I am big"," I am small) -- based on your input cell comparison you will get an result if it's true 'I am big' if it false 'I am small' - You know this. First Excel understand true or false, then accordingly provides the user definitions for True and False.
Let's look at CF , here also the same logic works either True or False, where as instead of results in different cell(like above) which will color the cells as its conditional formatting.
Formula -- True / False >> Results as per the user difinition (eg I am big / small) - if(100>50,"I am big", I am small) >> True / False >> Answer big / small
CF ------- True / False >> color the cell as per user difinition (green / yellow) - (100>50) >> True / False >> Color the cell.
You got the difference in the syntax, in CF you mention only the logical Test.
Formula - IF (logical test,[value if true],[value if false])
CF --(logical test)
Very simple right, hope you understand the first above part. let's get into the next level.
Formula - you provide what if and what if not.
CF - usually, you write only if it is true - color this cell. By default Excel understand that if it false ' Do not do anything' - where as in formula you have to write for both True and false or else it will through you an error.
Let's look at the practical problem .
You have A1 : A6 has 50 , 100,150,175,200,250 these values in each cell. Select all cells >> conditional format>>new rule>>rule type as " formula to determine.." last option
1. condition1 - if cell greater than 100 make it as green >> = A1>100 >>format as Green >> except 50 and 100 everything becomes green now. add the below condition
2. condition2 - if cell greater than or equal 200 make it red >> A1>=200 >> format as Red >> now 200 and 250 become Red, 150 and 170 as green (above), 50 and 100 as nothing because first two cells does not qualified (True). False will be no action by default. if you want you can convert that diff color by adding one more condition A1<100
3. condition3 : above 150 and less than 200 color it as yellow >> need to write AND condition(to test both the condition to qualify) like >> = And(A1>150,A1<200) >> yellow color >> you will get an result as yellow for 175.
Enclosed same attachment for your reference.
And also attached MS help contents to understand, when there is conflict condition while evaluating - for more detail.
Hope this helps you. Let me know your thought.
Regards
Vinod.
Reply With Quote
Bookmarks