sponser

Thursday, December 10, 2009

Excel Formulas

A formula is a set of mathematical instructions that can be used in Excel to perform calculations .Formals are started in the formula box with an = sign
There are many elements to and excel formula.
References: The cell or range of cells that you want to use in your calculation
Operators: Symbols (+,-,*,/, etc.) that you want to use in your calculation to be performed
Constants : Numbers or text values that do not change
Functions : Predefined formulas in Excel
2.1. To create a basic formula in Excel
Ø Select the cell for the formula
Ø Type =(the equal sign ) and the formula
Ø Click Enter

2. Calculate with Functions
A function is a built in formula in Excel .A function has a name and arguments (the mathematical function) in parentheses .Common functions in Excel:
Sum: Adds all cells in the argument
Average: Calculates the average of the cells in the arguments
Min: Finds the minimum Value
Count: Finds the number of cells that contain a numerical value within a range of the argument
Ø To calculate a function
Ø Click the cell where you want the function applied
Ø Click the Insert Function button
Ø Choose the function
Ø Click OK
Complete the Number 2 box with the last cell in the last cell in the range that you want calculated
3.1. Function Library
The function library is a large group of functions on the Formula Tab of the Ribbon. These functions include:
Auto Sum: Easily calculates the sum of a range
Recently Used: All recently used functions
Financial: Accrued interest, cash flow return rates and additional financial functions


Logical: And, If, True, False, etc.
Example Functions
What it does
=SUM(A1:C5)
Adds all the numbers in a range of cells A1 to C5.
=AVERAGE (B10:B20)
Returns the average (arithmetic mean) of the range of the cell range.
=MAX(E2:F5)
Returns the largest value in a set /range of values.
=MIN(A1:A20)
Returns the largest value in a set/range of values.
=TODAY ()
Displays the current date of the system
=NOW ( )
Displays the current time of the system
Text: Text based functions
Date and Time: Functions calculated on data and time
Math and Trig: Mathematical Functions
3.2. Some other functions
IF ( ) Logical function
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Syntax: IF (logical _test, value _if _true _value _if _false)
Logical _test is any value or expression that can be evaluated to TRUE or FALSE .For example, A10 =100 is a logical expressions; if the value in cell .A10 is equal to 100, the expressions evaluates to TRUE. Otherwise, the expressions evaluates to FALSE. This argument can use any comparison calculation operator.
Value _if _ true is the value that is returned if logical _test is TRUE.
Value _if _False is the value that is returned if logical _test is FALSE.
Note: Up to seven IF functions can be nested as value _if _true and value _if _false arguments to construct more elaborate tests. See the second of the following examples.
Example 1: A B C
S.N
Predicted Expense
Actual Expense
Remarks
1.
2500
1600
Within Budget
2.
1200
1600
Over Budget
3.
550
650
Over Budget
4.
1400
1350
Within Budget





Formula
Description(Result)
=IF (A2>B2, “Within budget” , “Over budget”)
If the A2 (Predicated Exp.)is more than or equal to B2 (Actual Exp), then the formula displays “Within budget”. Otherwise, the function displays “Over budget”.

Example 2:
If Score is
Then return
If Score is
Then return
Greater than 90
A
From 60 to 69
D
From 80to 89
B
Less than 60
E
From 70 to 79
C



SN
Name
Score
Grade
1
Rajesh
85
B
2
Krishna
68
D
3
Naresh
45
E
4
Prajwol
37
E
5
Manish
85
B
6.
Bikesh
93
A
=if(B2>90, “A” ,if (B2>80, “B” ,if (B2>70, “C” ,if (B2>60, “D”, “E”))))

No comments:

Post a Comment