Microsoft EXCEL Formulas
Introduction
Excel is a spreadsheet program in the Microsoft Office system. You can use Excel to create and
format workbooks (a collection of spreadsheets) in order to analyze data and make more
informed business decisions. Specifically, you can use Excel to track data, build models for
analyzing data, write formulas to perform calculations on that data, pivot the data in
numerous ways, and present data in a variety of professional looking charts.
Formulas Tab
This tab has commands to use when creating Formulas. This tab holds an immense function
library which can assist when creating any formula or function in your spreadsheet.
Creating Formulas to Calculate Values
To write an Excel formula, you begin the cell’s contents with an equal (=) sign; when Excel sees
it, it knows that the expression following it should be interpreted as a calculation, not text.
After the equal sign, type the formula. Excel makes it easy to enter complex calculations.
To create a new calculation, click the Formulas tab, and then in the Function
Library group, click Insert Function. The Insert Function dialog box opens, with a
list of functions, or predefined formulas, from which you can choose.
Note: If you're not sure which function to use, type a question that describes what you want to
do in the Search for a function box (for example, "add numbers" returns the SUM function),
or browse from the categories in the Or Select a category box. Enter the arguments. After you
complete the formula, press ENTER.
1
, You can use a similar technique when you add a formula to an Excel table column. If you
want a cell reference to remain constant when the formula using it is copied to another cell,
you can use an absolute reference. To write a cell reference as an absolute reference, type $
before the row letter and the column number.
Note: Another way to ensure your cell references don’t change when you copy the formula to
another cell is to click the cell that contains the formula, copy the formula’s text in the formula
bar, press the Esc key to exit cut-and-copy mode, click the cell where you want to paste the
formula, and press Ctrl + V. Excel doesn’t change the cell references when you copy your
formula to another cell in this manner..
One quick way to change a cell reference from relative to absolute is to select the cell reference
in the formula box and then press F4. Pressing F4 cycles a cell reference through the four
possible types of references:
• Relative columns and rows (for example, C3)
• Absolute columns and rows (for example, $C$3)
• Relative columns and absolute rows (for example, C$3)
• Absolute columns and relative rows (for example, $C3)
Functions: Functions are predefined formulas that take value or values, perform complex
calculation by using a specific value in a particular order to give a result.
AutoSum: The AutoSum button, located on Formulas tab in Function Library group. When
you click on the AutoSum button, the SUM function is entered into the active cell.
To use the AutoSum button
1. Click on the cell where you want the answer to appear.
2. Click on the AutoSum button.
3. The function will auto select the closest range of the data cells. The selected cells are
surrounded by the marching ants.
4. Check to make sure that the selected range for the function is correct. If it is correct, press
Enter on the keyboard.
5. If it is incorrect, drags selected with the mouse the correct range and then press Enter on
the keyboard.
Note: Do not assume that the range that AutoSum selects is always correct.
Auto Average: The Auto Average function work same as AutoSum function but its calculating
average of the range data.
Auto Count Number: The Auto Count Number function work same as AutoSum function but
its count how many cells contain numbers in given range of data
2
, Auto Max: The Auto Max function work same as AutoSum function but its finds the maximum
number of the given range of data.
Auto Min: The Auto Min function work same as AutoSum function but its finds the minimum
number of the given range of data.
Excel creating a special Recently Used list that contains a list of the functions you’ve
used most often. From this menu, you can just see a list of your favorite functions any
ignore the other hundred functions.
Create a Formula with Cell References
The first cell reference is B3, the color is blue, and the cell range has a blue border with square
corners. The second cell reference is C3, the color is green, and the cell range has a green
border with square corners.
To create your formula:
1. Click the cell in which you want to enter the formula.
2. In the formula bar, at the top of the Excel window that you use, , type =
(equal sign).
3. Click on the 1st cell you want in the formula.
4. Enter an Operator such as +, or *.
5. Click on the next cell you want in the formula. Continue steps 3 – 5 until the formula is
complete
6. Hit the ENTER key on your keyboard.
Mathematical & Trigonometric Functions
1. SUM ( ) – This function is used to calculate sum of values.
Syntax: =SUM(number 1, number2, …..) and press enter Key. Example:
=SUM(1,2,3,4,5) Result = 15
2. ROUND ( ) – This function is use to round a number to a specific number of digits.
Syntax: = ROUND(number, number digits) and press enter Key. Example:
=ROUND(33.12345, 2) Result = 33.12
3. ROUNDUP ( ) – This function rounds a number up, away from zero.
Syntax: =ROUNDUP(number, number digits/0) and press enter Key. Example:
=ROUNDUP(33.12345, 2) Result = 33.13
4. ROUNDDOWN ( ) – This function rounds a number down, towards zero.
Syntax: =ROUNDDOWN(number, number digits/0) and press enter Key. Example:
=ROUNDDOWN(33.12345, 2) Result = 33.12
3
Introduction
Excel is a spreadsheet program in the Microsoft Office system. You can use Excel to create and
format workbooks (a collection of spreadsheets) in order to analyze data and make more
informed business decisions. Specifically, you can use Excel to track data, build models for
analyzing data, write formulas to perform calculations on that data, pivot the data in
numerous ways, and present data in a variety of professional looking charts.
Formulas Tab
This tab has commands to use when creating Formulas. This tab holds an immense function
library which can assist when creating any formula or function in your spreadsheet.
Creating Formulas to Calculate Values
To write an Excel formula, you begin the cell’s contents with an equal (=) sign; when Excel sees
it, it knows that the expression following it should be interpreted as a calculation, not text.
After the equal sign, type the formula. Excel makes it easy to enter complex calculations.
To create a new calculation, click the Formulas tab, and then in the Function
Library group, click Insert Function. The Insert Function dialog box opens, with a
list of functions, or predefined formulas, from which you can choose.
Note: If you're not sure which function to use, type a question that describes what you want to
do in the Search for a function box (for example, "add numbers" returns the SUM function),
or browse from the categories in the Or Select a category box. Enter the arguments. After you
complete the formula, press ENTER.
1
, You can use a similar technique when you add a formula to an Excel table column. If you
want a cell reference to remain constant when the formula using it is copied to another cell,
you can use an absolute reference. To write a cell reference as an absolute reference, type $
before the row letter and the column number.
Note: Another way to ensure your cell references don’t change when you copy the formula to
another cell is to click the cell that contains the formula, copy the formula’s text in the formula
bar, press the Esc key to exit cut-and-copy mode, click the cell where you want to paste the
formula, and press Ctrl + V. Excel doesn’t change the cell references when you copy your
formula to another cell in this manner..
One quick way to change a cell reference from relative to absolute is to select the cell reference
in the formula box and then press F4. Pressing F4 cycles a cell reference through the four
possible types of references:
• Relative columns and rows (for example, C3)
• Absolute columns and rows (for example, $C$3)
• Relative columns and absolute rows (for example, C$3)
• Absolute columns and relative rows (for example, $C3)
Functions: Functions are predefined formulas that take value or values, perform complex
calculation by using a specific value in a particular order to give a result.
AutoSum: The AutoSum button, located on Formulas tab in Function Library group. When
you click on the AutoSum button, the SUM function is entered into the active cell.
To use the AutoSum button
1. Click on the cell where you want the answer to appear.
2. Click on the AutoSum button.
3. The function will auto select the closest range of the data cells. The selected cells are
surrounded by the marching ants.
4. Check to make sure that the selected range for the function is correct. If it is correct, press
Enter on the keyboard.
5. If it is incorrect, drags selected with the mouse the correct range and then press Enter on
the keyboard.
Note: Do not assume that the range that AutoSum selects is always correct.
Auto Average: The Auto Average function work same as AutoSum function but its calculating
average of the range data.
Auto Count Number: The Auto Count Number function work same as AutoSum function but
its count how many cells contain numbers in given range of data
2
, Auto Max: The Auto Max function work same as AutoSum function but its finds the maximum
number of the given range of data.
Auto Min: The Auto Min function work same as AutoSum function but its finds the minimum
number of the given range of data.
Excel creating a special Recently Used list that contains a list of the functions you’ve
used most often. From this menu, you can just see a list of your favorite functions any
ignore the other hundred functions.
Create a Formula with Cell References
The first cell reference is B3, the color is blue, and the cell range has a blue border with square
corners. The second cell reference is C3, the color is green, and the cell range has a green
border with square corners.
To create your formula:
1. Click the cell in which you want to enter the formula.
2. In the formula bar, at the top of the Excel window that you use, , type =
(equal sign).
3. Click on the 1st cell you want in the formula.
4. Enter an Operator such as +, or *.
5. Click on the next cell you want in the formula. Continue steps 3 – 5 until the formula is
complete
6. Hit the ENTER key on your keyboard.
Mathematical & Trigonometric Functions
1. SUM ( ) – This function is used to calculate sum of values.
Syntax: =SUM(number 1, number2, …..) and press enter Key. Example:
=SUM(1,2,3,4,5) Result = 15
2. ROUND ( ) – This function is use to round a number to a specific number of digits.
Syntax: = ROUND(number, number digits) and press enter Key. Example:
=ROUND(33.12345, 2) Result = 33.12
3. ROUNDUP ( ) – This function rounds a number up, away from zero.
Syntax: =ROUNDUP(number, number digits/0) and press enter Key. Example:
=ROUNDUP(33.12345, 2) Result = 33.13
4. ROUNDDOWN ( ) – This function rounds a number down, towards zero.
Syntax: =ROUNDDOWN(number, number digits/0) and press enter Key. Example:
=ROUNDDOWN(33.12345, 2) Result = 33.12
3
