EXCEL_Formulas_Bible

What is the key feature added in Excel 365/Excel 2021's Formulas Bible?

Dynamic Arrays and many new formulas.

What formula would you use to find if a list has duplicates?

=MAX(COUNTIF(A1:A1000,A1:A1000)) If the answer is 1, list is unique.

How do you calculate the number of unique values in a range?

Use the formula: =IF(COUNTA(A1:A100)=0,0,COUNTA(UNIQUE(FILTER(A1:A100&"",A1:A100<>""))))

What formula can generate sequential weekday names?

=TEXT(DATE(2017,1,SEQUENCE(7)),'ddd') for 3 character names.

How can you count the number of working days in a month?

=NETWORKDAYS(INDEX(EOMONTH(A1,0),1),EOMONTH(A1,0)) assuming A1 contains the month.

What formula is used to calculate age from a birthday?

=DATEDIF(A1,TODAY(),'y') where A1 contains the birthday.

What is the formula to convert a month name to a number?

=MONTH('1'&A1) where A1 contains the month name.

Give the formula for converting from mm/dd/yy to dd/mm/yy.

=FILTERXML(''&SUBSTITUTE(TEXT(A1,'mm/dd/yyyy'),'/','')&'','//s[2]')&'/'&FILTERXML(''&SUBSTITUTE(TEXT(A1,'mm/dd/yyyy'),'/','')&'','//s[1]')&'/'&FILTERXML(''&SUBSTITUTE(TEXT(A1,'mm/dd/yyyy'),'/','')&'','//s[3]')

How to find the location of the first number in a string?

=IFERROR(AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A1,SEQUENCE(LEN(A1))),1),'')

What formula can determine if a year is a leap year?

=MONTH(DATE(A1,2,29))=2 where A1 contains the year.

How do you generate non-repeating random numbers in Excel?

=INDEX(UNIQUE(RANDARRAY(30-1+1,,1,30,TRUE)), SEQUENCE(10)).