Functions in Excel
Excel has three random functions: RAND for decimals between 0 and 1, RANDBETWEEN for whole numbers in a range, and RANDARRAY for a whole grid at once. With them you can make random decimals, dates, picks from a list and numbers that never repeat. Decimal 0 to 1=RAND() Whole number=RANDBETWEEN(1,100) A grid (365)=RANDARRAY(5,3) RAND: a … Read more
To put a list or table in random order, for quiz questions, a random draw or a fair rota, use =SORTBY(A2:C20,RANDARRAY(ROWS(A2:C20))) in Excel 365. In any version, a RAND helper column and a sort does the same. Excel 365SORTBY + RANDARRAY Any versionRAND column + Sort New orderPress F9 Method 1: SORTBY and RANDARRAY (Excel … Read more
To split a column of amounts into positive and negative columns, such as receipts and payments in a bank statement, use =MAX(B3,0) and =MIN(B3,0), an IF formula that leaves blanks, or FILTER for two separate lists. Power Query does it for large data. fx=MAX(B3,0) ProblemSplit amounts into positive and negative columns Functions usedMAX, MIN, IF, … Read more
In Excel you divide and then apply the Percent Style (Ctrl + Shift + 5), with no need to multiply by 100. Here are the formulas for percentage of a total, percentage change, adding or removing a percentage, and working backwards. Percentage=Part/Total, then % Change=(New−Old)/Old Add 6%=A2*(1+6%) Quick reference To find Formula Example Percentage of … Read more
To count how many dates of birth fall in July, compare the month of each date with 7: =SUMPRODUCT(–(MONTH(C2:C17)=7)). The same idea counts birthdays this month, or lists who they are. FormulaSUMPRODUCT + MONTH Excel 365FILTER to list the names Works inAll versions Count birthdays in a month =SUMPRODUCT(–(MONTH($C$2:$C$17)=7))Copy Step Result MONTH($C$2:$C$17) {12;7;11;3;1;8;…}, the month … Read more
To count how many dates fall in 2020, use COUNTIFS with a date range: =COUNTIFS(B3:B15,">="&DATE(2020,1,1),B3:B15,"<"&DATE(2021,1,1)). SUMPRODUCT with YEAR does the same in a shorter formula. FastCOUNTIFS with a date range ShortSUMPRODUCT + YEAR Every year at onceUNIQUE + COUNTIFS, or a PivotTable Method 1: COUNTIFS =COUNTIFS(B3:B15,">="&DATE(2020,1,1),B3:B15,"<"&DATE(2021,1,1))Copy Counts dates on or after 1 January 2020 and … Read more
To count how many cells in a range show an error, use =SUMPRODUCT(–ISERROR(E3:E12)). You can also count one error type, such as #N/A, or count only the cells that are not errors. All errorsSUMPRODUCT + ISERROR One error typeCOUNTIF Works inAll versions Count all errors =SUMPRODUCT(–ISERROR(E3:E12))Copy Step Result ISERROR(E3:E12) {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE} — turns TRUE/FALSE into 1/0 … Read more
To count how many dates of birth fall in July, compare the month of each date with 7: =SUMPRODUCT(–(MONTH(C2:C17)=7)). The same idea counts birthdays this month, or lists who they are. FormulaSUMPRODUCT + MONTH Excel 365FILTER to list the names Works inAll versions Count birthdays in a month =SUMPRODUCT(–(MONTH($C$2:$C$17)=7))Copy Step Result MONTH($C$2:$C$17) {12;7;11;3;1;8;…}, the month … Read more
To show the current sheet’s name in a cell, for a report title or a header, use CELL(“filename”) and take everything after the closing bracket. In Excel 365 it’s a short TEXTAFTER formula. Excel 365TEXTAFTER + CELL Any versionMID + FIND + CELL NeedsThe workbook to be saved Excel 365 =TEXTAFTER(CELL("filename",A1),"]")Copy Any version =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)Copy 31 … Read more
XLOOKUP Function against VLOOKUP and INDEX+MATCH