Microsoft Excel Spreadsheet Formulas Tutorial
Microsoft Excel Spreadsheet Formulas
In this tutorial, I will tell you everything you need to know about Microsoft Excel spreadsheet formulas.
Microsoft Excel is a wonderful tool used by professionals worldwide. It is the most popular spreadsheet application and is available on Windows and Mac OS X. The latest version of Microsoft Excel is Excel 2010.
Microsoft Excel also includes a programming component based on Visual Basic for Applications.
Excel also includes a wide range of spreadsheet formulas which you can use to automate your workflow and make it easier along with macros. After completing this tutorial, you will know everything there is to know about Excel formulas and functions.
You can use either direct values in Excel: values like numbers (1,2,3,4.5,12.10 etc) or strings (“BrightHub”, “Pathik”, “Awesome”).
You can also use cell values as variables. For example, to use the value in the first cell as a variable, use A1. For the cell in the first row and 5th column, use E1.
All Excel formulas and functions start with a “=” sign.
For example, =SUM(A1,B1) will give you the sum of the values in the cells A1 and B1. You can enter the formula syntax in the function bar on the top.
I will list all the basic in Microsoft Excel Spreadsheet Formulas and Functions.
Mathematical Formulas in Excel
SUM(value1, value2)
This returns the sum of the 2 values.
For example,
=SUM(1,2) returns 3
=SUM(A1, B1) returns the sum of the values stored in A1 and B1
PRODUCT(value1, value2)
This returns the product of the 2 values.
For example,
=PRODUCT(2,3) returns 6
=PRODUCT(A1, B1) returns the product of the values stored in A1 and B1
POWER(value1, value2)
This returns the value of value1 raised to the power of value 2.
For example,
=POWER(2,3) returns 8
=POWER(A1, B1) returns the value of value1 raised to value2.
RAND
This returns a random number between 0 and 1
=RAND() could return 0.53545 or 0.98273
ABS(value)
This returns the absolute positive value of the value or number entered.
For example,
=ABS(5) returns 5
=ABS(-10) returns 10
CEILING(value, significance)
This returns the value of the number entered rounded up to the nearest multiple of significance.
The significance is the unit you want to round it up to. CEILING always increases the absolute value of the number
For example,
=CEILING(5.4,1) returns 6
=CEILING(-10.2,-1) returns -11
FLOOR(value,significance)
This returns the value of the number entered rounded down to the nearest multiple of significance.
The significance is the unit you want to round it down to. FLOOR always decreases the absolute value of the number
=FLOOR(5.4,1) returns 5
=FLOOR(-10.2,-1) returns -10
GCD(value1, value2)
This returns the greatest common divisor (GCD) of the two numbers.
For example,
=GCD(20,15) returns 5
=GCD(A1,B1) returns the GCD of the values stored in A1 and B1
LCM(value1, value2)
This returns the least common multiple (LCM) of the two numbers.
For example,
=LCM(20,15) returns 60
=LCM(A1,B1) returns the LCM of the values stored in A1 and B1
LOG(value1, base)
This returns the log of the value to the base. If no base is specified, the default base is 10.
For example,
=LOG(10) returns 1
=LOG(10,2) returns 3.321928
Besides these, there are also the trigonometric formulas.
The most basic ones are
PI
This returns the value of the trigonometric constant Pi.
=PI() returns 3.141593
SIN(value1)
This returns the sine value of the variable specified in radians
=SIN(10) returns -0.54402
COS(value1)
This returns the cosine value of the variable specified in radians
=COS(10) returns -0.83907
TAN(value1)
This returns the tangent value of the variable specified in radians
=TAN(10) returns 0.648361
Text Functions in Excel
Text functions are the most widely used after math functions
CHAR (value1)
This returns the character corresponding to the given ASCII value
=CHAR(97) returns the result “a”
=CHAR(100) returns the result “d”
CODE (value1)
This returns the ASCII code for the character value
=CODE(“a”) returns the value 97
=CODE(“z”) returns the value 122
LOWER(string)
This returns the string in lowercase
=LOWER(“Brighthub is an awesome WEBSITE”) returns the string “brighthub is an awesome website”
UPPER(string)
This returns the string in uppercase
=UPPER(“Brighthub is an awesome WEBSITE”) returns the string “BRIGHTHUB IS AN AWESOME WEBSITE”
PROPER(string)
This returns the string in the proper case with all first characters capitalized.
=PROPER(“Brighthub is an awesome WEBSITE”) returns the string “Brighthub Is An Awesome Website”
CONCATENATE(string1, string2)
This concatenates the two strings specified and returns “string1 + string2”
=CONCATENATE(“Brighthub “, “Pat”) returns “Brighthub Pat”
=CONCATENATE(“Brighthub “, A1) returns “Brighthub (value in A1)”
LEN(string value)
This returns the length of the specified string.
=LEN(“Lalalalalala”) returns 12
SEARCH(character, string to be searched, starting position)
This returns the position of the specified character or string in the given string.
=SEARCH(“h”, “Brighthub”, 1) returns 5
These are the basic text functions in Microsoft Excel
Logical Functions in Excel
AND (condition1, condition2)
This function will return true only if both condition1 and condition2 are true, else it will return false
=AND(1<2,1<3) returns TRUE
=AND(1>2,1<3) returns FALSE
OR (condition1, condition2)
This function will return true even if any one condition is true, it will return false only if both are false else it will return false
=OR(1>2,1>3) returns FALSE
=OR(1>2,1<3) returns TRUE
NOT(statement)
This returns the opposite of the value returned by the statement
=NOT(TRUE) returns FALSE
=NOT(1<2) returns FALSE
IF(condition, truevalue, falsevalue)
The IF function returns truevalue if the condition is true and falsevalue if the condition is false.
=IF(1<2, “1 is less”, “2 is less”) returns “1 is less” as the condition is true, 1 is less than 2.
Statistical Formulas in Excel
MAX(value1, value2, value3)
This returns the maximum value from the list of values provided
=MAX(1,2,3,4) returns 4
MIN(value1, value2, value3)
This returns the minimum value from the list of values provided
=MIN(1,2,3,4) returns 1
AVERAGE(list of values)
This returns the average of the list of values specified
=AVERAGE(1,2,3,4,5) returns 3
MODE(list of values)
This returns the mode of the list of values specified
=MODE(1,1,3,4,5) returns 1
MEDIAN(list of values)
This returns the median of the list of values specified
=MEDIAN(1,2,3,4,5) returns 3
COUNT(list of values)
This returns the count of the total number of values present in the list
=COUNT(1,2,3,4,5,1,2,3,4,5) returns 10
Date and Time Formulas in Excel
This is the last major type of functions. It returns the date and time of the system which Excel is running on.
=NOW()
This returns the current time and date
=TODAY()
This returns today’s date
=DATE(year, month, date)
This returns the specified date in the correct format
=TIME(hour, minute, second)
This returns the specified time in the correct format.
These are all the basic functions you will use in your Excel spreadsheets. There are many more functions which you can access directly from Microsoft Excel’s help. Just open up Microsoft Excel and press F1 to launch the Help window and search for the function you want.
