IF function and ISERROR function are used to check for an error in Excel. This example shows you how to create an array formula that counts the number of errors in a range. 1. We use the IF function and the ISERROR function to check for an error in the record below: Explanation: the IF function returns 1, if an …
How to count data based upon multiple criteria? Counting with Or criteria in Excel can be tricky. This article shows several easy to follow examples. 1. We start simple. For example, we want to count the number of cells that contain Google or Facebook (one column). 2a. However, if we want to count the number of rows that contain Google or Stanford …
This example teaches you how to insert checkbox to create a checklist in Excel. First, turn on the Developer tab. Next, you can create a checklist. You can also insert a check mark symbol. To create this checklist, execute the following steps. 1. On the Developer tab, in the Controls group, click Insert. 2. Click Check Box in the Form Controls section. 3. …
In Excel, <> means not equal to. The <> operator in Excel checks if two values are not equal to each other. Let’s take a look at a few examples. 1. The formula in cell C1 below returns TRUE because the text value in cell A1 is not equal to the text value in cell B1. 2. …
The OR function in Excel returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false. Combine the OR function with other functions and become an Excel expert. 1. For example, take a look at the OR function in cell D2 below. Explanation: the OR function returns TRUE if the first score is …
To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. The ISNUMBER functions is used to check for a numeric value and the SEARCH function returns the location of a substring in a string. There’s no CONTAINS function in Excel. 1. To find the position of a substring in a text …
This example illustrates the EVEN and the ODD function in Excel. It also shows you how to determine if a number is even or odd. Even 1. Rounds a positive number up to the nearest even integer. 2. Rounds a negative number down to the nearest even integer. Odd 1. Rounds a positive number up to the nearest odd integer. …
Learn how to use Excel’s logical functions such as the IF, AND, OR and NOT function. If The IF function checks whether a condition is met, and returns one value if true and another value if false. 1. For example, take a look at the IF function in cell C2 below. Above Case Study Explanation: if the …
IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples. Simple If Examples The IF function checks whether a condition is met, and returns one value if true and another value if false. 1a. For example, take a look at the IF function in cell B2 below. …
What is IF function in Excel? IF function is one of logical functions in Microsoft Office Excel that returns one value if the condition is TRUE, or another value if the condition is FALSE. Syntax of IF function The syntax for the IF function in Microsoft Excel is: IF( condition, value_if_true, [value_if_false] ) IF formula explanation condition The value …
This tutorial shows how to create 1st and 2nd level outline formatting using excel formulas. To generate basic outline numbering you can use COUNTA, MID, FIND, IF, and LEN functions. Note: The formula used will only handle a 2-level outline. Explanation In the example shown, the formula in D5 is: =COUNTA($B$5:B5)&”.”&IF(B5<>””,1, MID(D4,FIND(“.”,D4)+1,LEN(D4))+1) How this formula works At …
This tutorial shows how to set a check register formula that calculates a running balance and also, blank values using formula based on simple addition and subtraction. See illustrations below: Formula =balance-debit+credit Explanation In the example shown, the formula in G6 is: =G5-E6+F6 How this formula works The value in G5 is hard-coded. The formula picks …
To calculate a conditional mode with one or more criteria you can use an array formula based on the IF and MODE functions. Note: this is an array formula and must be entered with control + shift + enter. Formula {=MODE(IF(criteria,data))} Explanation In the example shown, the formula in F5 is: {=MODE(IF(group=E5,data))} where “group” is the named range B5:B14, …
To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function. Formula =NOT(COUNTIF(range,”<65″)) Explanation In the example shown, the formula in G5 is: =NOT(COUNTIF(B5:F5,”<65″)) How this formula works At the core, this formula uses the COUNTIF function to count any …
To get the last relative position (i.e. last row, last column) for mixed data that may contain empty cells, you can use the MATCH function as described below. Note: this is an array formula and must be entered with Control+Shift+Enter. Formula {=MATCH(2,1/(range<>””))} Explanation In the example shown, the formula in E5 is: {=MATCH(2,1/(B4:B10<>””))} Last *relative* …
To calculate a conditional percentile, you can use an array formula using the IF function inside the PERCENTILE function. See example below: Formula =PERCENTILE(IF(criteria,values),k) Note: This is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula in G5 is: =PERCENTILE(IF(Table[Gender]=G$4,Table[Score]),$F5) Where “Table” is an Excel Table with data …
To count the total words in a cell, you can use a formula based on the LEN and SUBSTITUTE functions. Formula =LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1 Explanation In the example shown, C3 contains this formula: =LEN(TRIM(B3))-LEN(SUBSTITUTE(B3,” “,””))+1 How the formula works SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This …
To find the most frequently occurring text in a range, based on criteria you supply, you can use an array formula based on several Excel functions MATCH, MODE, INDEX, and IF. Formula =INDEX(range1,MODE(IF(range2=criteria, MATCH(range1,range1,0)))) Note: this is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula …
To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. Formula {=TEXTJOIN(“”,TRUE,IF(ISERR(MID(A1, ROW(INDIRECT(“1:100”)),1)+0), MID(A1,ROW(INDIRECT(“1:100″)),1),””))} Explanation In the example shown, the formula in C5 is: =TEXTJOIN(“”,TRUE,IF(ISERR(MID(B5, ROW(INDIRECT(“1:100”)),1)+0), MID(B5,ROW(INDIRECT(“1:100″)),1),””)) Note: this is an array formula and must be entered with control + shift + …
Case study: If Monday, roll back to Friday To check the weekday of a date, and roll back to Friday when the date is a Monday, you can use the IF and WEEKDAY functions. Formula =IF(WEEKDAY(date)=2,date-3,date) Explanation In the example shown, the formula in C5 is =IF(WEEKDAY(B5)=2,B5-3,B5) How this formula works The WEEKDAY function returns …
To list holidays that occur between two dates, you can use a formula based on the TEXTJOIN and IF functions. Formula {=TEXTJOIN(“, “,TRUE,IF(dates>=start,IF(dates<=end,holidays,””),””))} Note: the TEXTJOIN function is new in Excel 2016. Explanation In the example shown, the formula in F8 is: {=TEXTJOIN(“, “,TRUE,IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,””),””))} This is an array formula and must be entered with control + shift + enter. How …
Case study You have a start time and an end time, and you want to know how many of the hours in between these two times overlap another time period in the day. For example, you want to know how many hours fall between 8:00 AM and 5:00 PM, or 7:00 PM to 7:00 AM, etc. …
This tutorials covers how to generates Series of dates increment by weekends from a single start date in Excel. If need to generate a dynamic series of dates with a formula that include only future weekend dates (i.e. Sat and Sun), you can do so with a formula that uses the IF and WEEKDAY functions. Formula …
This tutorials covers how to generate series of dates increment by workdays from a single start date in Excel If need to generate a dynamic series of dates that are workdays only (i.e. Monday through Friday), you can do so with a simple formula that uses the the WORKDAY function Formula =WORKDAY(date,1) Explanation In the …
To add a line break taking into account the current OS (Mac or Windows), you can use the INFO function to test the system and then return the correct break character — CHAR(10) for Windows, CHAR(13) for Mac. Note: make sure you have text wrap enabled on cells that contain line breaks. Formula =IF(INFO(“system”)=”mac”,CHAR(13),CHAR(10)) Explanation The character …
If you have a list of things (words, substrings, etc) and want to find out which of these things appear in a cell, you can build a simple table and use a formula based on the SEARCH function. Setup Suppose you have a cells that contain text that mentions various colors, and you want to …
If you need to compare two text strings in Excel to determine if they’re equal, you can use the EXACT function. Formula =EXACT(text1, text2) Explanation For example, if you want to compare A2 with B2, use: =EXACT(A2,B2) If the two strings are identical, EXACT will return TRUE. If not, EXACT will return FALSE. You can …
To display a conditional message, without the IF function, you can use boolean logic and the REPT function. Formula =REPT(“message”,logical test) Explanation In the example shown, the formula in D5 (copied down) is: =REPT(“low”,C5<100) If the value in column C is less than 100, the formula returns “low”. If not, the formula returns an empty string (“”), which …
This tutorial shows how to count line breaks in cell in Excel worksheet using example below. To count total lines in a cell, you can use a formula based on the LEN, SUBSTITUTE, and CHAR functions. Formula =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 Explanation In the example, we are using this formula: =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 How the formula works First, the LEN function counts …
To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control + …
To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. Formula =(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN (MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start) *MOD(start,1),upper,lower) Explanation In the example shown, E5 contains this formula: =(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN (MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5) *MOD(B5,1),upper,lower) where “lower” is the named range H5 and “upper” is the named range H6. How this formula …
It you need to assign points based on late time in Excel then this tutorials is for you. See the example below. To assign penalty points based on an amount of time late, you can use a nested IF formula. Formula = IF(time<VALUE(“0:05”),0, IF(time<VALUE(“0:15”),1, IF(time<VALUE(“0:30”),2, IF(time<VALUE(“0:60”),3, IF(time<VALUE(“4:00”),4, 5))))) Explanation In the example shown, the formula in E5 …
This tutorials shows how to calculate number of hours between two times in Excel. To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary …
This tutorial shows how to calculate If this AND that OR that in Excel using the example below; Formula =IF(AND(A1=”x”,OR(B1=”y”,B1=”z”)),”x”,””) Explanation To test for various combinations of this AND that, or this OR that, you can use the IF function with the AND and OR functions. In the example shown, the formula in D6 is: =IF(AND(B6=”red”,OR(C6=”small”,C6=”medium”)),”x”,””) When an item …
This tutorial shows how to calculate IF with wildcards in Excel using the example below; Formula =IF(COUNTIF(A1,”??-????-???”),””,”invalid”) Explanation The IF function doesn’t support wildcards, but you can combine IF with COUNTIF or COUNTIF to get basic wildcard functionality. In the example shown, the formula in C5 is: =IF(COUNTIF(B5,”??-????-???”),””,”invalid”) How this formula works Unlike several other frequently used functions, …
This tutorial shows how to calculate IF with boolean logic in Excel using the example below; Formula = IF(criteria1*criteria2*criteria3,result) Explanation In the example shown, the formula in F8 is: {=SUM(IF((color=”red”)*(region=”East”)*(quantity>7),quantity))} Note: this is an array formula, and must be entered with control + shift + enter. How this formula works Note: This example demonstrates how to replace a nested …
This tutorial shows how to calculate If cell is not blank in Excel using the example below; Formula =IF(A1<>””,”Not blank”,”Blank”) Explanation If you want to test a cell and take some action if the cell is not blank (not empty), you can use a simple formula based on the IF function. In the example shown, we have …
This tutorial shows how to calculate If cell is this OR that in Excel using the example below; Formula =IF(OR(A1=”this”,A1=”that”),”x”,””) Explanation If you want to do something specific when a cell equals this or that (i.e. is equal to X or Y, etc.) you can use the IF function in combination with the OR function to …
This tutorial shows how to calculate If cell is x or y and z in Excel using the example below; Formula =IF(AND(OR(A1=x,A1=y),B1=z),”yes”,”no”) Explanation You can combine logical statements with the OR and AND functions inside the IF function. If color is red or green and quantity is greater than 10 In the example shown, we …
This tutorial shows how to calculate If else in Excel using the example below; Formula =IF(test, true result, false result) Explanation If you need to test a condition, then take one action if the condition is TRUE, and another action if the condition if FALSE, you can use the IF function. Context In the example shown, we …