Posts

Explanation This formula is evaluated for each of the 10 cells in A1:D10. As with the COUNTIF, this formula will return when the result is zero. Seethis pagefor a more detailed explanation.

When MOD returns zero, the expression returns TRUE and the conditional formatting applies. When MOD returns any other result, the expression returns FALSE conditional formatting is not applied. For example, MOD(10,3) = 1. The result of MOD carries the same sign as the divisor.

If this bothers you, feel free to subtract 1. This number is then compared to the value from “input”. For example, =MID(“apple”,2,3) returns “ppl”.

To highlight matching columns, we use this logical expression: The reference to B4 is again mixed. When either (or both) logicals return TRUE, the rule is triggered and the formatting is applied. OR returns TRUEif any condition is TRUE. If all conditions are FALSE, the OR function returns FALSE. AND returns TRUEonly if all the conditions are met. If any conditions are not met, the AND function returns FALSE.

All addresses are entered in “mixed” format, with the columns locked and the rows left relative. Effectively, this means that all 4 cells in each row are tested with exactly the same formula. See the video link below for a more detailed description. SEARCH returns the position offind_textinsidewithin_textas a number. When the text is not found, FIND returns a #VALUE error.

The result is a formula that applies exactly the same logic to every cell in the same row. Cells that contain text, numbers, errors, spaces, etc. Formulas that return empty strings ("")arecounted as blank.

Explanation The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. Dates are just serial numbers in Excel, so earlier dates are always less than later dates. References to the start and end dates (C2 and E2) are absolute and will not change. Itcreates a proper Excel date with given year, month, and day values. AND returns TRUEonly if all the conditions are met. If any conditions are not met, the AND function returns FALSE....

This problem can be easily solved by applying conditional formatting with a formula based on the TEXT function. The dropdown menu is implemented withdata validation. TEXT function TheTEXT functionreturns a number formatted as text, using thenumber formatprovided. We can use the abbreviated day name for each date to match against the target date in F5. When the result from TEXT is different, the formula will return FALSE. This is what we need to trigger a conditional formatting rule....

These are for readability and convenience only.

Explanation TheCOUNTIF functioncounts the number of times each value appears in the data range. When the count is 1, the formula returns TRUE and triggers the rule. Conditional formatting is evaluated for each cell that is applied to.

Explanation The CELL function can provide a wide range of information about cell properties. One property is called “protect” and indicates whether a cell is unlocked or locked. The CELL function returns either 1 or zero to indicate “on” or “off”. When CELL returns 1, the expression returns FALSE and no conditional formatting is applied. The jot down of information to be returned is specified asinfo_type.

To make a more flexible, interactive conditional formatting rule, use other cells like variables in the formula. You must use an absolute address for E2 and G2 to prevent these addresses from changing. AND returns TRUEonly if all the conditions are met. If any conditions are not met, the AND function returns FALSE.

When a conditional formatting rule returns TRUE, the formatting is triggered. Just verify you use an absolute address to keep the input cell address from changing. Another way to lock the reference is to use a named range since named ranges are automatically absolute.