Register now or log in to join your professional community.
I'm agree with all answers, Thank you Mr. Ahmad Maher for invitation
At some point in time, when you are faced with a lot of data in your spreadsheets, you may want to find a way to highlight or “filter” out some of your data based on specific criteria. For example, if you wanted to see if the value of a cell in column A is equal to the value of a cell in column B (duplicates), you can use a conditional formula in column C to give you a TRUE or FALSE result.
=IF(A1=B1,TRUE,FALSE)
This can be helpful if you are using this formula across a large range of cells and you want to be able to catch any anomalies. In another example, if you were trying to determine which cells have values of a certain range, you can also use the IF function to create a conditional formula like:
=IF(A1>3,TRUE,FALSE)
If your objective is to count how many cells fit a specific criteria (e.g. values greater than50), you would probably be better off using the COUNTIF function. However, the benefit to using basic conditional formulas with the IF function is that you can use this formula for conditional formatting so you can highlight cells that match a criteria of your choosing.
=COUNTIF(D2:D5,B1) for cell references and numerical values
=COUNTIF(D2:D5,”Player1″) for text vaues—don’t forget to include quotation marks if you’re referring to a text value
Applications of Conditional Formulas
Finding duplicates across rows or columns [e.g. =IF(A1=B1,”Same”,”Different”)]
Finding values in a specific range [e.g. values greater than4: =IF(A1>4,TRUE,FALSE)]
Calculate different equations based on different values of a single cell
[e.g. if you’re trying to use a formula that is dependent upon conditions, you can “nest” your functions (where multple functions are used within each other)
In this example, if you were to calculate the difference of one player’s victories over the other (without ending up with negatives) and to also denote if there is a tie, you could use a formula like:
=IF(B7>C7,B7-C7,IF(C7>B7,C7-B7,”Tie”))
This formula is actually two IF formulas in one…first, you have the first IF formula IF(B7>C7,B7-C7,…). However, with the value_if_false part of the equation, you include yet another IF formula: IF(C7>B7,C7-B7,”Tie”). If both the first IF formula is false (meaning that B7 is NOT greater than C7) AND the second IF formula is also false (C7 is NOT greater than B7), then the final false value carries over from the second (or in this case, inner-most) IF formula, which is “Tie.”
Applying multiple condtions in a single formula
For this example, let’s say you wanted to determine if Player2 had a good game day by not only determining if Player2 was a winner of the game, but that they also scored more than3 points. You can create an IF formula with a nested AND formula so that you can narrow down your results to just the games where Player2 met BOTH criteria like so:
=IF(AND(D2=C1,C2>3),”Good Game”,”Needs Improvement”)
In this formula, if Player2 (cell C1) is the victor (cell D2) of the game, AND Player2’s score (cell C2) is greater than3, then a result of “Good Game” is produced, otherwise it will result in “Needs Improvement.” This means that if Player1 wins, if there is a tie, or if Player2 wins but doesn’t score more than3 points, it will result in a “Needs Improvement” result. If you wanted to adjust the formula so that if Player2 scores3 points and you want a “Good Game” result instead of “Needs Improvement,” then you would adjust the > symbol to a greater than or equal to symbol >= in the formula.
=IF(AND(D2=C1,C2>=3),”Good Game”,”Needs Improvement”)
Conditional formatting
Formatting cells based on multiple criteria outside of the standard function
With Excel2007 &2010’s conditional formatting, you have several options available that you can use to highlight the cells that you apply the formatting to based on the selected cells’ value. But what if you wanted to format cells based on criteria that is out of the scope of the default options? You do it with a formula, of course!
In the Conditional Formatting menu, you can select the “Use a formula to determine which cells to format” rule type, which will provide you with a box for entering your formula. If, for instance, you wanted to highlight the cell listing the Game number (i.e. cells A2:A5) based on the values of the cells in an adjacent column (column D in this case which lists who the victor was), you could use a formula such as:
=IF($D2=”Player2″,TRUE,FALSE)
Keep in mind that by default, if you click on the cell you are using in the formula, Excel will create an absolute reference (using $ in front of the column letter and row number). If you want your formula to adjust along with the cells that it applies to, you will need to remove these absolute references by simply removing the $ accordingly). Once you’ve got your formula nice and spiffed up, click on the Format button to designate the formatting you wish to apply (I’ve chosen the cell fill color of blue), then hit OK.
Since I only applied this formatting to cell A2, I want to adjust the range of this conditional format, so I will go to the Conditional Formatting menu, and under Manage Rules, I can “stretch out” the range of this condition. Also, if I wanted to apply another conditional format, say to highlight the games that Player1 had won but in a different color, you can create a new rule with a new format by following the same steps you did before, but adjusting the formula to =IF($D2=”Player1″,TRUE,FALSE) and changing the cell fill color (I chose red).
You can repeat the same steps to highlight the cells that have a tie (I used a purple cell fill color) with this formula in a new rule:
=IF($D2=”Tie”,TRUE,FALSE)
Make sure to adjust the ranges for the conditional formatting rules and you’ve got a color coded list that adjusts when the scores change.
write this formula where you need the result =a1<>b1
I fully agree with the answers been added by EXPERTS....................................Thanks.
