Tabrez Blog

In this article, I'll show you how to work with mathematical formulas. 

Adding Numbers: 

• = [20]+[30]+[40]
• = SUM([20]+[30]+[40])

Subtracting Numbers:

• = [30]-[20]

Difference as Percentage:

• = ([30]-[20])/ABS([30])

 

Create the List with following fields: name "ProductSales"

Title	Data Type	Calculated Column value
ProductID	LookUP(Products)
ProductID:ProductName	Based on ProductID
2012	Currency
2013	Currency
2014	Currency
2015	Currency
TotalSalesAdd	Calculated Column	=[2012]+[2013]+[2014]+[2015]
TotalSales-SUM	Calculated Column	=SUM([2012],[2013],[2014],[2015])
2015-2014	Calculated Column	=[2015]-[2014]
PercentageDifference	Calculated Column	=([2015]-[2014])/ABS([2015])

 

Output:

Multiplying Numbers:

• =numberOfShows * averageSales
• =PRODUCT(numberOfShows, averageSales)
• =PRODUCT(numberOfShows, averageSales,2)

Dividing Numbers:

• =projectedSales / numberOfShows

Create the List with following fields: name "merchSales"

Title	Data Type	Calculated Column value
Band(Title column text change)	Single Line of text
numberOfShows	Number
averageSales	Currency
salesLastYear	Calculated Column	=numberOfShows * averageSales
projectedSales	Calculated Column	=PRODUCT(numberOfShows, averageSales,2)
projectedAverageSales	Calculated Column	= projectedSales / numberOfShows

 

Output:

 

Let's see Average, Median, Smallest, and Largest 

for showing examples for this I'll work with Student Film Attendance By Month 2016

ADD COLUMN : averagMonthlyAttendance

Average:

• =AVERAGE(January,Febrauary,.....December)
• =AVERAGE(January,February,March,April,May,June,July,August,September,October,November,December)

Output:

Median:

• MEDIAN(1,25,65,8,15)

Place numbers in order => (1,8,15,25,65)

ODD - Middle Number (15)  --> 15 is median this will return as result.

Even - Average of 2 Middle Numbers --> see below example:

(1,25,65,8) --> (1,8,25,65)  --> 8 + 25  = 32 --> 32/2 = 16 is median for this situation.

Add "median" column:

=MEDIAN(January,February,March,April,May,June,July,August,September,October,November,December)

Output:

Smallest:

• MIN

Add "min" column:

=MIN(January,February,March,April,May,June,July,August,September,October,November,December)

Output:

Largest:

• MAX

Add "max" column:

=MAX(January,February,March,April,May,June,July,August,September,October,November,December)

Output:

 

 

Counting, Increasing by Percentage, Raising to a Power:

 

 

 

 

Rounding:  (Use calculator for confirmation of round is correct or big difference )

• =ROUND(numberToRound,2)
• =ROUND((unitsInStock * productCost),2)