Formula test

References

=A1
=A1:B10
=Sheet2!A1:D12
='Sheet 2'!A1:E9
=table[column]
=SUM(table[amount])
=A1#

Ranges

=A1:F100
=B10:K:1000
=A:A
=1:1
=ROWS(A:A)*COLUMNS(1:1) // count all cells in worksheet

Errors

=#DIV/0!
=A1/0 // returns #DIV/0!
={1,1,0,#DIV/0!,#N/A,#NAME!}
={TRUE,TRUE,FALSE}

Basic formulas

=A1<100
=A1/(B1-B2)
=A1&" apples"
=A1<>B1 // returns TRUE or FALSE

Simple functions

=OR(A1="x",A1="y")
=AND(A1>100,A1<120)
=IF(A1>85,"Pass","Fail")
=VLOOKUP(10,A1:B100,2,FALSE)
=TEXT(F2,"dd-mm-yyyy")
=LARGE($C5:$G5,I$4)

Comments and text

=LARGE(C5:G5,1) // best score
=LARGE(C5:G5,2) // 2nd best score
=LARGE(C5:G5,3) // 3rd best score

="The date is "&TEXT(A1,"mmmm d") //  returns "The date is July 1"

Concatenation and text strings

=COUNTIFS(joined,">="&start,joined,"<="&end,
age,">="&LEFT(G8,FIND("-",G8)-1),
age,"<="&RIGHT(G8,LEN(G8)-FIND("-",G8)))

=COUNTIFS(joined,">="&start,joined,"<="&end,age,">="&LEFT(G8,FIND("-",G8)-1),age,"<="&RIGHT(G8,LEN(G8)-FIND("-",G8)))

Complex formulas

{=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)^0))>=N))}

=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))

=LET(
name,VLOOKUP(G5,B5:D16,2,0),
points,VLOOKUP(G5,B5:D16,3,0),
"Hi, "&name&", you have "&points&" points."&IF(points>300," Great job, "&name&"!",""))

Wrapped formulas

=LET(
  n,7,
  tday,TODAY(),
  calendar,TEXT(SEQUENCE(EDATE(tday,12)-tday,1,tday),"mmdd"),
  birthdays,TEXT(data[Birthday],"mmdd"),
  sorted,SORTBY(data,XMATCH(birthdays,calendar)),
  INDEX(
    sorted,
    SEQUENCE(MIN(n,ROWS(data))),
    SEQUENCE(1,COLUMNS(data))
  )
)