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 that you want to test.…
Tag: IF function
Calculate conditional mode with criteria in Excel
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, and “data” is the named…
How to set check register balance in Excel
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 up the value in G5,…
Basic outline numbering in Excel
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 the core, this formula builds…
Calculate Conditional Percentile ‘IF’ in table in Excel
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 in B5:D14. How this formula…
How to get last row in mixed data with blanks in Excel
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* position, not row on worksheet…
How to test for all values in a range are at least in Excel
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 cells that fall below a…
How to strip numeric characters from cell in Excel
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 + enter. How this formula works…
Find most frequent text within a range with criteria in Excel
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 in G5 is: =INDEX(supplier,MODE(IF(client=F5, MATCH(supplier,supplier,0))))…
How to count total words in a cell in Excel
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 number is then subtracted from…
How to calculate next scheduled event in Excel
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 + Shift + Enter. Explanation In…
Get work hours between dates and times in Excel
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 works This formula calculates total…
Roll back weekday to Friday base on a particular date in Excel
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 a number, 1-7, that corresponds…
List holidays between two dates in Excel
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 this formula works At a…
Calculate total hours that fall between two times in Excel
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. You might need this information…
Generate series of dates by weekends in Excel
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 =IF(WEEKDAY(date)=7,date+1,date+(7-WEEKDAY(date))) Explanation In the…
Calculate series of dates by workdays in Excel
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 example, B6 is the hard-coded…
Add line break based on OS in Excel
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 used for line breaks is…
How to compare two text strings in Excel
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 use this result inside the…
How to check cell contains which things in Excel
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 figure out which colors are…
How to display conditional message with REPT function in Excel
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 looks like a blank cell.…
How to count line breaks in cell in Excel worksheet
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 total characters in the cell…
Calculate number of hours between two times in Excel
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 (midnight), things can get tricky.…
Assign points based on late time in Excel
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 is: = IF(D5<VALUE(“0:05”),0, IF(D5<VALUE(“0:15”),1, IF(D5<VALUE(“0:30”),2,…
Excel Data validation with conditional list
How to create a drop-down and multiple dependent drop-down lists.in Excel To allow a user to switch between two or more lists, you can use the IF function to test for a value and conditionally return a list of values based on the result. Formula =IF(A1=”See full list”,long_list,short_list) Explanation In the example shown above, the data validation applied to C4…
Highlight row and column intersection exact match in Excel
This tutorial shows how to Highlight row and column intersection exact match in Excel using the example below; Formula =OR($A1=row_val,A$1=col_val) Explanation To highlight intersecting row(s) and column(s) with conditional formatting based on exact matching, you can use a simple formula based on mixed references and the OR function. In the example shown, the formula used to apply conditional formatting is:…
Highlight numbers that include symbols in Excel
This tutorial shows how to Highlight numbers that include symbols in Excel using the example below; Formula =IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)=”<“,(MID(B4,2,LEN(B4))+0)<input)) Explanation To highlight numbers less than a certain value, including numbers entered as text like “<9”, “<10”, etc., you can use conditional formatting with a formula strips the symbols as needed and handles the result as a number. In the example shown,…
Running count group by n size in Excel
This tutorial shows how to Running count group by n size in Excel using the example below; Formula =CEILING(COUNTA(expanding_range)/size,1) > Explanation To creating a running count of groups of a variable size, you can use the COUNTA and CEILING function. In the example shown, C5 contains this formula: =CEILING(COUNTA($B$5:B5)/size,1) where “size” is the named range F4. How this formula works The core…
Tax rate calculation with fixed base in Excel
This tutorial shows how to work Tax rate calculation with fixed base in Excel using the example below; Formula =IF(A1<limit,A1*rate,(A1-limit)*rate+fixed) Explanation This example shows how to set up simple formula using the IF function to calculate a tax amount with both fixed and variable components. In the example shown, the formula in C5 is: =IF(B5<limit,B5*rate,(B5-limit)*rate+fixed) in a worksheet with the following named ranges:…
Return blank if in Excel
This tutorial shows how to calculate Return blank if in Excel using the example below; Formula =IF(A1=1,B1,””) Explanation To return a blank result using the IF function, you can use an empty string (“”). In the example shown, the formula in D5 (copied down) is: =IF(B5=1,C5,””) How this formula works This formula is based on the IF function, configured with a simple logical…
Nested IF function example in Excel
This tutorial shows how to calculate Nested IF function example in Excel using the example below; Formula =IF(T1,R1,IF(T2,R2,IF(T3,R3,IF(T4,R4,R5)))) Explanation In the code above, T1-T5 represents 5 different logical tests, and R1-R5 represents 5 different results. You can see that each IF function requires it’s own set of parentheses. This article describes the Excel nested IF construction. Usually, nested IFs are used when…
Invoice status with nested if in Excel
This tutorial shows how to calculate Invoice status with nested if in Excel using the example below; Formula =IF(balance=0,”Paid”,IF(current_date<due_date,”Open”,”Overdue”)) Explanation To determine invoice status (i.e. paid, open, overdue), you can use a nested IF formula and the TODAY function. In the example shown, the formula in G5 is: =IF(F5=0,”Paid”,IF(TODAY()<C5,”Open”,”Overdue”)) How this formula works Note: the “current date” (i.e. today) for this example…
IF with boolean logic in Excel
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 IF formula with a single IF…
IF with wildcards in Excel
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, the IF function does not…
If this AND that OR that in Excel
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 is “red”, and either “small”…
If this AND that in Excel
This tutorial shows how to calculate If this AND that in Excel using the example below; Formula =IF(AND(A1=”this”,B1=”that”),”x”,””) Explanation If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result is TRUE, and (optionally) do…
If NOT this or that in Excel
This tutorial shows how to calculate If NOT this or that in Excel using the example below; Formula =IF(NOT(OR(A1=”red”,A1=”green”)),”x”,””) Explanation If you want to do something specific when a cell is NOT this or that (i.e. is NOT equal to X or Y, etc.) you can use the IF function in combination with the NOT and OR functions to run a test, then…
If else in Excel
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 have a list of T-shirts…
If cell is x or y and z in Excel
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 simply want to “mark” or…
If cell is this OR that in Excel
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 run a test, then take…