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 get the day of the week for a date, such as Tuesday for 28-Dec-1937, use =TEXT(B3,”dddd”). Here are four ways: two formulas, a number format that shows the day without changing the date, and Power Query, plus day names in Malayalam and Hindi. Looking for the DAY formula? =DAY(A2) returns the day of the … Read more
Excel has underline but no overline button. You can get the look with a formula that adds a line of underscores above the text, a combining overline character, or the Equation tool. FormulaREPT and CHAR(10) Excel 365UNICHAR(773) Exact lookInsert → Equation Method 1: Underscores above the text =REPT("_",LEN(B3))&CHAR(10)&B3Copy REPT writes one underscore per character, CHAR(10) … Read more
Excel has no built-in function to spell out numbers, but one long formula can do it: 456,571 becomes Four Hundred and Fifty Six Thousand Five Hundred and Seventy One. Copy the formula below, or for rupees in lakh and crore, use the RUPEES function instead. fx=TRIM(CHOOSE(LEFT(TEXT(B3,”000000000″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”)&IF(–LEFT(TEXT(B3,”000000000″))=0,,IF(AND(–MID(TEXT(B3,”000000000″),2,1)=0,–MID(TEXT(B3,”000000000″),3,1)=0),” Hundred”,” Hundred and “))&CHOOSE(MID(TEXT(B3,”000000000”),2,1)+1,,,”Twenty “,”Thirty “,”Forty “,”Fifty “,”Sixty “,”Seventy … Read more
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