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)

Page updated

Report abuse