Functions, logic, text and dates

In depth

Logical tests and IF

A comparison such as =J2>=50 returns a logical value, TRUE or FALSE. IF turns that into a decision: IF(logical_test,value_if_true,value_if_false) returns its second argument when the test is TRUE and its third when it is FALSE. Text arguments go in double quotation marks; without them Excel looks for a name and returns #NAME?. Numbers go without quotes.

Lantern promises subscribers 10% off. With the rate in an input cell P1 (10%), the discount a subscription order should carry is =IF(L2="yes",ROUND(G2*H2*$P$1,2),0): if the subscription column says yes, 10% of quantity times price, rounded to the cent; otherwise nothing. Comparing that with the recorded discount in column I tests the promise row by row, and the simple example does it.

AND, OR and NOT combine tests. AND(test1,test2,…) is TRUE only when every test is TRUE; OR(…) is TRUE when at least one is; NOT(test) reverses a test. Microsoft allows up to 255 conditions, and notes that AND ignores text and empty cells inside a range argument and returns #VALUE! if it finds no logical values at all. A flag for orders that need checking is then =IF(AND(L2="yes",I2<>N2),"check","ok"): a subscription order whose recorded discount differs from the expected one in N2.

Comparisons with = ignore letter case: ="Yes"="yes" returns TRUE (try it). Microsoft describes EXACT as the case-sensitive test, for when case matters. Lantern’s subscription column is all lower case, so the question doesn’t arise, but data typed by people often isn’t.

Comparisons don’t convert text, either: ="10"=10 is FALSE, and an order total stored as the text “$58.00” passes =J2>=75, because Excel ranks text above numbers in a comparison. Convert such values to numbers first, as B02 showed, before a formula makes decisions with them.

Several outcomes: nested IF and IFS

For more than two outcomes, put an IF in the false branch of another. For the packing rota, Ana flags orders of $75 or more “large”, $25 or more “medium”, and the rest “small” (packing labels, not the $20, $40 and $100 order-size bands she reports with, which Module 2 looks up from a table): =IF(J2>=75,"large",IF(J2>=25,"medium","small")). Excel stops at the first test that is TRUE, so the order of the tests is part of the logic. Test the $25 threshold first and a $111 order is labelled “medium”, because 111 ≥ 25 is TRUE before the $75 test is ever reached. Microsoft makes the same point with a commission example, and warns that, although Excel allows 64 nested IFs, such formulas are hard to get right and harder to maintain.

IFS lists the tests and their results in pairs: =IFS(J2>=75,"large",J2>=25,"medium",TRUE,"small"). It is available in Excel 2019, 2021 and 2024 and in Microsoft 365, and takes up to 127 conditions. If no test is TRUE, IFS returns #N/A, so the last test is usually TRUE, a catch-all. Two habits help with either form: put the thresholds in input cells rather than in the formula, and when the bands multiply, use a lookup table, which Module 2 builds with an approximate-match lookup.

Errors you expect and errors you don’t

IFERROR(value,value_if_error) returns the value unless it is an error, in which case it returns the second argument. It catches every error type: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. IFNA(value,value_if_na) catches only #N/A, the error a lookup returns when a key is missing; IFNA was added in Excel 2013.

That breadth is the danger. Suppose a cell works out each channel’s average order value as =IFERROR(B2/C2,0), so that a channel with no orders shows 0 rather than #DIV/0!. If someone later deletes a column the formula used, or mistypes a reference, the #REF! or #NAME? that should have shouted is also turned into 0, and 0 looks like a real result. Microsoft’s page on #DIV/0! errors makes the same point: IFERROR suppresses every error, so check that a formula works before you add error handling. Test for the case you expect instead, =IF(C2=0,"",B2/C2), and keep IFNA for lookups, where #N/A is the expected error.

Taking text apart

A piece of text in a cell is a text string, and its characters are numbered from 1. LEFT and RIGHT take characters from either end, MID takes them from a given position, and LEN counts them. For LC-10001 in A2, =LEFT(A2,2) is “LC”, =MID(A2,4,5) is “10001” and =LEN(A2) is 8.

FIND and SEARCH return the position of one string inside another, which lets a formula cope with text of different lengths: =MID(A2,FIND("-",A2)+1,LEN(A2)) returns everything after the first hyphen, however long the prefix. The two differ in a way that matters. FIND is case-sensitive and doesn’t allow wildcards; SEARCH ignores case and allows them (the next lesson explains wildcards). Microsoft’s example: in “Miriam McGovern”, =FIND("M",A2) is 1 but =FIND("m",A2) is 6. If the text isn’t found, both return #VALUE!.

The results of LEFT, MID and RIGHT are text, even when they look like numbers. “10001” sorts as text and SUM skips it. =VALUE(MID(A2,4,5)) converts it to the number 10001; Microsoft’s VALUE accepts any number, date or time format Excel recognises, so =VALUE("$1,000") is 1000. An arithmetic operator converts too, which is why =--RIGHT(A2,5) (minus minus) also works.

Cleaning and joining text

UPPER, LOWER and PROPER change letter case: =PROPER("hOUSE bLEND") is “House Blend”. TRIM removes spaces from the ends of a string and reduces runs of spaces inside it to one, but Microsoft warns that it removes only the ordinary space character (code 32), not the non-breaking space (code 160) that web pages and exports often contain; B02’s lesson on text showed how to find those. SUBSTITUTE(text,old_text,new_text) replaces one string with another: =SUBSTITUTE(A2,"LC-","") leaves “10001”. The full cleaning workflow, on Lantern’s raw export, is in the next module.

The & operator joins text: =A2&" ("&L2&")" gives “LC-10001 (no)” for the first order. Joining is called concatenation. CONCAT does the same for a list of values or ranges, and replaces the older CONCATENATE, which stays for compatibility. TEXTJOIN adds a delimiter between the pieces and can skip empty cells: =TEXTJOIN(", ",TRUE,D2:D6). Both are available in Excel 2019 and later and in Microsoft 365, and both return #VALUE! if the result would exceed a cell’s 32,767 characters.

TEXT formats a number as text: =TEXT(J2,"$#,##0.00") gives “$111.00” and =TEXT(B2,"mmmm") gives “March”. The result is text, which is exactly what you want in a label and exactly what you don’t want in a calculation. Microsoft’s advice is to keep the original value in one cell and use TEXT in another.

Dates are numbers

As B02 showed, Excel stores a date as a serial date: in the default 1900 date system, 1 January 1900 is 1, and Lantern’s first order day, 1 March 2026, is 46082. Because dates are numbers, subtracting one from another gives a number of days, and adding 7 gives the same weekday a week later. Excel treats 1900 as a leap year, which it wasn’t, for compatibility with Lotus 1-2-3; the only consequence Microsoft lists is that WEEKDAY is wrong for dates before 1 March 1900.

DATE rolls over in Microsoft’s documented way: =DATE(2008,14,2) is 2 February 2009 and =DATE(2008,1,35) is 4 February 2008, which makes month arithmetic safe. Microsoft also advises four-digit years, because DATE adds 1900 to a year from 0 to 1899. NETWORKDAYS takes an optional list of holidays as a third argument, and NETWORKDAYS.INTL handles weekends other than Saturday and Sunday. A month is not 30 days: =B2+30 lands on 31 March, while =EDATE(B2,1) lands on 1 April, which is the date a monthly subscription actually renews. A date stored as text in a format Excel recognises (“March 2, 2026” is one with US settings) is turned into a serial number with DATEVALUE; the next module uses it, with the day-and-month caution from B02, on Lantern’s raw export.

You may meet DATEDIF, which counts complete years, months or days between two dates: =DATEDIF(B2,C2,"M"). Microsoft keeps it to support old Lotus 1-2-3 workbooks, warns that it “may calculate incorrect results under certain scenarios”, advises against its “MD” unit, and notes that it returns #NUM! when the start date is after the end date. For days, subtract the dates; for months, prefer a formula you can check.