Excel OfficeExcel Date and Time Functions return a dynamic date or time in a cell.
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 »
This tutorial shows how to Highlight bottom values in Excel using the example below; Formula =A1<=SMALL(data,N) Explanation To highlight the smallest (bottom) values in a set of data with conditional formatting, you can use a formula based on the SMALL function. In the example shown, the formula used for conditional formatting is: =B4<=SMALL(data,input) Note: Excel contain a… read more »
This tutorial shows how to Get nth match in Excel using the example below; Formula =SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n) Explanation To get the position of the nth match (for example, the 2nd matching value, the 3rd matching value, etc.), you can use a formula based on the SMALL function. In the example shown, the formula in G5 is:… read more »
This tutorial shows how to Find lowest n values in Excel using the example below; Formula =SMALL(range,n) Explanation To find the n lowest values in a set of data, you can use the SMALL function. This can be combined with INDEX as shown below to retrieve associated values. In the example shown, the formula in… read more »
This tutorial shows how to calculate Extract multiple matches into separate rows in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate rows, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in E5 is: {=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),””)} This is an array… read more »
This tutorial shows how to Extract multiple matches into separate columns in Excel using the example below; Explanation To extract multiple matches to separate cells, in separate columns, you can use an array formula based on INDEX and SMALL. In the example shown, the formula in F5 is: {=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),””)} This is an array formula and… read more »
This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5… read more »
This tutorial shows how to Break ties with helper column and COUNTIF in Excel using the example below; Formula =A1+(COUNTIF(exp_rng,A1)-1)*adjustment Explanation To break ties, you can use a helper column and the COUNTIF function to adjust values so that they don’t contain duplicates, and therefore won’t result in ties. In the example shown, the formula in D5… read more »
This tutorial shows how to calculate nth smallest value with criteria in Excel using the example below; To get the 2nd smallest value, 3rd smallest value, 4th smallest value, and so on, where each value matches supplied criteria, you can use an array formula that uses the SMALL and IF functions. Formula {=SMALL(IF(criteria,values),n)} Explanation In… read more »
This tutorial shows how to calculate nth smallest value in Excel using the example below; To get the 2nd smallest value, 3rd smallest value, 4th smallest value, and so on, from a set of data, you can use the SMALL function. Formula =SMALL(range,nth) Explanation In the example shown, the formula in G5 is: =SMALL(times,F5) How… read more »