IF function in Excel

Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.

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… read more »

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*… read more »

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… read more »

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… read more »

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… read more »

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… read more »

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 +… read more »

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… read more »

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… read more »

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… read more »

Sidebar