Interpolation estimates a value between two known points: the level at chainage 115.7 when you know the levels at 99.5 and 201.1. Excel can do it with the straight-line formula, with FORECAST.LINEAR, or straight from a whole table with a lookup. Two pointsFORECAST.LINEAR From a tableMATCH + FORECAST, or LET Example answer40.49 The formula For … Read more
To pick a random winner, question or name, use =INDEX(B3:B8,RANDBETWEEN(1,ROWS(B3:B8))). It works for a list of any length and gives a new pick each time you press F9. Any versionINDEX + RANDBETWEEN Several, no repeatsExcel 365: TAKE + SORTBY + RANDARRAY New pickPress F9 Method 1: INDEX and RANDBETWEEN =INDEX(B3:B8,RANDBETWEEN(1,ROWS(B3:B8)))Copy RANDBETWEEN picks a position from … 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 split Anil Kumar into Anil and Kumar, use TEXTBEFORE and TEXTAFTER in Microsoft 365, or LEFT and FIND in any version. These formulas also handle middle names and people with only one name. First name (365)=TEXTBEFORE(A2," ",,,1) Last name (365)=TEXTAFTER(A2," ",-1,,,"") Any versionLEFT + FIND Excel 365: TEXTBEFORE and TEXTAFTER =TEXTBEFORE(A2," ",,,1)Copy TEXTBEFORE returns … Read more
The following is the generic formula to extract the first name from a full name. =LEFT(“Full Name”,SEARCH(” “,”Full Name”)-1) Explanation SEARCH function in the formula will return the position of ” ” (Space character) from the Full name. In the formula used in the cell C3, SEARCH function will return the position of ” ” … 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