Introduction The complex number system is an extension of real numbers. Historically, the invention of complex numbers allowed mathematicians to find the roots of previously unsolvable polynomials. Nowadays, engineers use complex numbers to solve problems related to electronics, signal processing, and fluid dynamics. The way the formula engine implements complex numbers is an example of functional programming. These new functions make it easier to use complex numbers in Excel without needing helper columns or array formulas....

I’ve been playing around with theTEXTJOINandCONCATfunctions this week. These are both new functions in Excel 2016, introduced in the Office 365 subscription service. Both of these functions let you join (concatenate) text in different cells together. CONCAT simply mashes all values together without options. What’s nice about both of these functions is that they can handle cell ranges. But I’m also intrigued about how this might be useful inside other formulas....

The CONCAT function automatically ignores empty cells. The CONCAT function accepts multipleargumentscalled text1, text2, text3, etc. up to 255 total. Arguments may be supplied as cell references, ranges, and hard-coded text strings. Only the first argument is required, and values are concatenated in the order they appear. To join many values with a common delimiter, see theTEXTJOIN function. TEXTJOIN can do everything CONCAT can do, but can also apply a delimiter and optionally ignore empty values....

In Excel 2019 and later, theCONCAT functionandTEXTJOIN functionare better, more flexible alternatives. The CONCATENATE function accepts multipleargumentscalledtext1,text2,text3, etc. up to 30 total. Values may be supplied as cell references, and hard-coded text strings. Only the first argument is required, and values are concatenated in the order they appear. To concatenate many values with a common delimiter, see theTEXTJOIN function. TEXTJOIN can do everything CONCAT can do, but can also accept a delimiter and optionally ignore empty values....

Concatenation refers to the operation of joining values together to create a text string. CONCAT and TEXTJOIN are new functions available in Office 365 and Excel 2019.

You are here

Let’s take a look. The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. Start by selecting the cells you want to format. For the formula itself, start with an equal sign. Then add the logical test you need. In this case, we can use H6 < 80. This will return true when the average is less than 80 and trigger the rule....

However, by using your own formula, you have more flexibility and control. When a new value is entered, the highlighting is immediately updated. The reference to C5 isrelativeand changes as the formula is evaluated for each cell in the range. The reference to cell J6 is “locked” as anabsolute reference($J$6).

When the formula returns TRUE, the rule is triggered and the highlighting is applied.

When all tests return FALSE, the OR function returns FALSE and no conditional formatting is applied. For more information on building formula criteria, see50+ formula criteria examples. 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. When given TRUE, NOT returns FALSE....

Then we check the result with a logical expression. When an expression returns TRUE, the conditional formatting is triggered. Rule 1 tests if the variance is less than 3 days. Rule 2 checks if the variance is less than 10 days. Rule 3 checks if the variance is greater than or equal to 10 days. Both rule 1 and rule 2 have “stop if true” enabled. When either rule returns TRUE, Excel will stop checking additional rules....

Explanation Consider for a moment how overlapping dates work. The SUMPRODUCT function is perfect for this kind of test because it handles array comparisons elegantly.

Here we have a table that contains employee data. Let’s say we want to highlight people in this table who belong to group A. I’ll use a helper column to help illustrate. This is because structured references are not recognized inside a conditional formatting rule. The workaround is to use regular references. In this case, I need to use: F5 equals A, with column F locked. This allows the formula to highlight an entire row....