Formula

  

Averages – Excel

Mean – arithmetic mean - Excel

=AVERAGE(range)

Median – middle point in a set of observations - Excel

=MEDIAN(range)

Mode – most common number in a set of observations - Excel

=MODE(range)

Weighted Average Mean – Excel

=SUMPRODUCT(A1:A2,B2:B3)/SUM(B2:B3)

	A Price	B Quantity
1	$50	10
2	$20	90

Geometric mean – Excel

=GEOMEAN(range)

Geometric mean can be used for a range of positive data in situations involving growth over time.

Remove #N/A errors - Excel

Overview   =IF(ISNA(lookup),0,lookup)  

#N/A errors are replaced with 0.

Example   =IF(ISNA(VLOOKUP(A1,D1:E5,2,0)),0,VLOOKUP(A1,D1:E5,2,0))

  

 

EDATE - Excel and PowerPivot

Calculates the date a number of months before/after a start date

Type:   =EDATE(date,6) 

Calculates the date 6 months from the start date. To calculate prior dates use -(number of months).
For example, could be used to calculate a maturity a certain number of months from a start date.

Calculate first day in month relative to a date

Type:   =EOMONTH(A1,2)+1

Calculate last day in month relative to a date

Type:   =EOMONTH(A1,6)

 

Calculate financial quarter from a date - Excel

Type   =MOD(CEILING(22+MONTH(A1)-6-1,3)/3,4)+1   

Where cell A1 contains the date, 6 (month 6) represents last month of the financial year.

Format cell as number with no decimal places.

   

Insert current time - Excel

Type   =NOW()   Volatile formula updates each time refresh data.
   

Insert current date - Excel

Type   =TODAY()   Volatile formula updates each time refresh data.

Filename & path displayed within a cell - Excel

Type   =CELL("filename")

Comment within a formula - Excel

Type   +N("comments")   at the end of the formula

 

Generate random number - Excel

Type   =rand()