Useful Formulas
Core Calculation Formulas
Function
Formula
Result
Sum
+ add
- subtractÂ
* multiply
/ divide
Formula
=Sum(A1:A9)
=Sum(A9-A1)
=Sum(A1*A2)
=Sum(A1/A2)
Result
Adds all cells within cells A1 to A9
 Subtract A1 from A9
Multiplies A1 by A2
Divides A1 by A2
Average
Formula
=Average(B1:B9)
Result
Calculates the average of cells B1 to B9
Round
Formula
=Round(C1,2)
Result
Rounds the data in cell C1 to 2 decimal places
Min
Formula
=Min(D1:D9)
Result
Identifies the lowest number within cells D1 to D9
Max
Formula
=Max(D1:D9)
Result
Identifies the highest number within cells D1 to D9
Count
Formula
=Count(E1:E9)
Result
Counts the number of cells which contain data
Count blanks
Formula
=Countblank(E1:E9)
Result
Counts the number of cells which contain no data
Referencing Formulas
Index
Formula
=Index(A1:E9,3,2)
Result
Returns the value in the 3rd Row and 2nd column in the range of data A1 to E9
Match
Searches for a value and returns it's position
Formula
=Match(A1,B1:B9,0)
Result
Searches for A1 within the data in cells B1 to B9
Conditional Calculations
CountIF/CountIFS
Formula
=countif(A1:A9, ">5")
Result
Counts all cells with A1 to A9 where the value is greater than 5
CountA
Formula
=counta(A1:A9)
Result
Counts all cells with A1 to A9 which are not empty
SumIf/SumIFS
Adds data which meets a criteria
Formula
=SumIF(B1:B9, ">5")
Result
Adds data in cells B1 to B9 where the values are greater than 5
AverageIf/AverageIFS
Averages data which meets a criteria
Formula
=AverageIF(B1:B9, ">5")
Result
Averages the data in cells B1 to B9 where the values are greater than 5
Taken from @letslearnX on X (Formally Twitter)
