Lectures Notes Of Class 3
Working with Formulas and Functions
Objective:
Introduce basic
formulas and functions in Excel, providing students with the foundational
skills to perform calculations and analyze data effectively.
1. Basic Formulas:
A formula in Excel is used to do mathematical calculations. Formulas always start with the equal sign (=) typed in the cell, followed by your calculation.
Formulas can be used for calculations such as:
=1+1
=2*2
=4/2
It can also be used to calculate values using cells as input.
These are the arithematic operators used in Excel :
- Addition (+): Combines values from different cells.
- Example: =A1 + B1
- Subtraction (-): Subtracts one value from another.
- Example: =A1 - B1
- Multiplication (*): Multiplies two values.
- Example: =A1 * B1
- Division (/): Divides one value by another.
- Example: =A1 / B1
ADDITION In Excel:
1.Addition Operator and SUM() function
The total is called SUM in Excel.
There are two ways to calculate the SUM.
- Adding cells (with addition operator)
- SUM function
Addition Operator
Addition uses the + symbol in Excel, and is also known as plus.
There are two ways to do addition in Excel. Either by using the + symbol in a formula or by using the SUM function.
How to add cells:
- Select a cell and type (
=) - Select a cell
- Type (
+) - Select another cell
- Press enter
You can add more cells to the formula by typing (+) between the cells.
Adding Two Manual Entries
Type A1(=)
Type 5+5
Press enter
Adding Two Cells
Step by step:
Step 1: In excel sheet , in cell A1 , Input some value as A1=2 suppose
and in A2, Input some value as A2=3 suppose
Step 2: To do arithmatic operation
- Select
C1and type (=) - Left click
A1 - Type (
+) - Left click
A2 - Press enter
How to do it, step by step:
- Type
B1(=) - Select
A1 - Type (
+) - Select
A2 - Press enter
Adding Several Cells
Step by step to add several cells:
- Type
B1(=) - Select
A1 - Type (
+) - Select
A2 - Type (
+) - Select
A3 - Type (
+) - Select
A4 - Type (
+) - Select
A5 - Press enter
SUM() function to Addition
step by step:
- Type
E5(=) - Write SUM
- Double click SUM in the menu
- Mark the range
E2:E4 - Press enter
100 Nos Of Formula and Functions
In MSExcel
1-20: Basic Mathematical Functions
1.
SUM - Adds up a range
of numbers.
o Example:
=SUM(A1:A10)
For Detail --->Click here
2.
AVERAGE
- Calculates the average of a range of numbers.
o Example:
=AVERAGE(B1:B10)
For Detail --->Click here
3.
MIN - Finds the
smallest number in a range.
o Example:
=MIN(C1:C10)
For Detail --->Click here
4.
MAX - Finds the
largest number in a range.
o Example:
=MAX(D1:D10)
For Detail --->Click here
5.
COUNT
- Counts the number of cells with numbers in a range.
o Example:
=COUNT(E1:E10)
For Detail --->Click here
6.
COUNTA
- Counts non-empty cells.
o Example:
=COUNTA(F1:F10)
For Detail --->Click here
7.
COUNTIF
- Counts cells that meet a specific condition.
o Example:
=COUNTIF(G1:G10, ">10")
For Detail --->Click here
8.
SUMIF
- Adds cells based on a condition.
o Example:
=SUMIF(H1:H10, ">5")
For Detail --->Click here
9.
SUMIFS
- Adds cells based on multiple conditions.
o Example:
=SUMIFS(I1:I10, J1:J10, ">5", K1:K10, "<20")
For Detail --->Click here
10. ROUND
- Rounds a number to a specified number of digits.
o Example:
=ROUND(L1, 2)
For Detail --->Click here
11. ROUNDUP
- Rounds a number up.
o Example:
=ROUNDUP(M1, 0)
For Detail --->Click here
12. ROUNDDOWN
- Rounds a number down.
o Example:
=ROUNDDOWN(N1, 1)
For Detail --->Click here
13. INT - Rounds a number
down to the nearest integer.
o Example:
=INT(O1)
14. MOD - Returns the
remainder of a division.
o Example:
=MOD(P1, 3)
15. PRODUCT
- Multiplies a range of numbers.
o Example:
=PRODUCT(Q1:Q10)
16. POWER
- Raises a number to a power.
o Example:
=POWER(R1, 2)
17. SQRT
- Calculates the square root.
o Example:
=SQRT(S1)
18. EXP - Returns e
raised to a power.
o Example:
=EXP(T1)
19. ABS - Returns the
absolute value.
o Example:
=ABS(U1)
20. RAND
- Generates a random number between 0 and 1.
o Example:
=RAND()
21-40: Logical Functions
21. IF - Tests a
condition and returns a value if true or false.
o Example:
=IF(A1 > 10, "Yes", "No")
22. AND - Returns TRUE if
all conditions are met.
o Example:
=AND(A1 > 5, B1 < 10)
23. OR - Returns TRUE if
any condition is met.
o Example:
=OR(A1 > 5, B1 < 10)
24. NOT - Reverses the
logic of an argument.
o Example:
=NOT(A1 > 5)
25. IFERROR
- Returns a specified value if a formula results in an error.
o Example:
=IFERROR(A1/B1, "Error")
26. IFNA
- Returns a specified value if #N/A error occurs.
o Example:
=IFNA(VLOOKUP(A1, B1:B10, 2, FALSE), "Not Found")
27. XOR - Returns TRUE if
an odd number of arguments are TRUE.
o Example:
=XOR(A1 > 5, B1 > 5)
28.SWITCH
- Chooses a value from a list of options.
o Example:
=SWITCH(A1, 1, "One", 2, "Two", "Other")
29.IFS - Tests multiple
conditions and returns the first true value.
o Example:
=IFS(A1 = "Red", "Stop", A1 = "Yellow",
"Slow")
30.CHOOSE
- Returns a value from a list based on a position.
o Example:
=CHOOSE(2, "Apple", "Banana", "Cherry")
31. TRUE
- Represents a TRUE logical value.
o Example:
=TRUE()
32.FALSE
- Represents a FALSE logical value.
o Example:
=FALSE()
33.ISNUMBER
- Checks if a value is a number.
o Example:
=ISNUMBER(A1)
34.ISEVEN
- Checks if a number is even.
o Example:
=ISEVEN(A1)
35.ISODD
- Checks if a number is odd.
o Example:
=ISODD(A1)
36.ISERROR
- Checks if a value is an error.
o Example:
=ISERROR(A1)
37.ISERR
- Checks for any error except #N/A.
o Example:
=ISERR(A1)
38.ISBLANK
- Checks if a cell is empty.
o Example:
=ISBLANK(A1)
39.ISLOGICAL
- Checks if a value is TRUE or FALSE.
o Example:
=ISLOGICAL(A1)
40.ISFORMULA
- Checks if a cell contains a formula.
o Example:
=ISFORMULA(A1)
41-60: Text Functions
41. CONCATENATE
- Joins multiple strings into one.
o Example:
=CONCATENATE(A1, B1)
42.TEXTJOIN
- Joins text with a delimiter.
o Example:
=TEXTJOIN(", ", TRUE, A1:C1)
43.LEFT
- Extracts a specific number of characters from the left.
o Example:
=LEFT(A1, 5)
44.RIGHT
- Extracts characters from the right.
o Example:
=RIGHT(A1, 3)
45.MID - Extracts
characters from the middle of a string.
o Example:
=MID(A1, 2, 3)
46.LEN - Returns the
length of a text string.
o Example:
=LEN(A1)
47.TRIM
- Removes extra spaces.
o Example:
=TRIM(A1)
48.LOWER
- Converts text to lowercase.
o Example:
=LOWER(A1)
49.UPPER
- Converts text to uppercase.
o Example:
=UPPER(A1)
50.PROPER
- Capitalizes the first letter of each word.
o Example:
=PROPER(A1)
51. REPT
- Repeats text a specified number of times.
o Example:
=REPT("*", 5)
52.SUBSTITUTE
- Replaces specific text.
o Example:
=SUBSTITUTE(A1, "old", "new")
53.REPLACE
- Replaces part of a text string based on position.
o Example:
=REPLACE(A1, 2, 3, "new")
54.FIND
- Finds the position of text.
o Example:
=FIND("text", A1)
55.SEARCH
- Finds position, case-insensitive.
o Example:
=SEARCH("text", A1)
56.TEXT
- Formats a number as text.
o Example:
=TEXT(A1, "$#,##0.00")
57.DOLLAR
- Formats a number as currency.
o Example:
=DOLLAR(A1, 2)
58.VALUE
- Converts text to a number.
o Example:
=VALUE(A1)
59.EXACT
- Checks if two strings are identical.
o Example:
=EXACT(A1, B1)
60.CODE
- Returns a character code.
o Example:
=CODE(A1)
61-80: Date and Time Functions
61. NOW - Returns the
current date and time.
o Example:
=NOW()
62.TODAY
- Returns the current date.
o Example:
=TODAY()
63.YEAR
- Extracts the year from a date.
o Example:
=YEAR(A1)
64.MONTH -
Extracts the month from a date.
o Example:
=MONTH(A1)
65.DAY - Extracts the
day from a date.
o Example:
=DAY(A1)
66.HOUR
- Extracts the hour from a time.
o Example:
=HOUR(A1)
67.MINUTE
- Extracts the minute from a time.
o Example:
=MINUTE(A1)
68.SECOND
- Extracts the second from a time.
o Example:
=SECOND(A1)
69.WEEKDAY
- Returns the day of the week as a number.
o Example:
=WEEKDAY(A1)
70.WEEKNUM
- Returns the week number of the year.
o Example:
=WEEKNUM(A1)
71. DATEDIF
- Calculates the difference between two dates.
o Example:
=DATEDIF(A1, B1, "d")
72.EDATE
- Adds or subtracts months to/from a date.
o Example:
=EDATE(A1, 3)
73.EOMONTH
- Returns the last day of the month after a certain number of months.
o Example:
=EOMONTH(A1, 1)
74.DATE -
Creates a date from year, month, and day.
o Example:
=DATE(2024, 10, 25)
75.TIME
- Creates a time from hour, minute, and second.
o Example:
=TIME(14, 30, 0)
76.YEARFRAC
- Calculates the fraction of the year between two dates.
o Example:
=YEARFRAC(A1, B1)
77.NETWORKDAYS
- Counts the workdays between two dates.
o Example:
=NETWORKDAYS(A1, B1)
78.WORKDAY
- Calculates the workday after a specified number of days.
o Example:
=WORKDAY(A1, 5)
79.ISOWEEKNUM
- Returns the ISO week number of the year.
o Example:
=ISOWEEKNUM(A1)
80.DATEDIF (alternative use)
- With different units, like "y" for years, "m" for months,
or "d" for days.
o Example:
=DATEDIF(A1, B1, "m")
81-90: Financial Functions
81. PMT - Calculates the
payment for a loan based on constant payments and a constant interest rate.
o Example:
=PMT(5%/12, 60, -10000)
82.PV - Returns the
present value of an investment.
o Example:
=PV(5%/12, 60, -200)
83.FV - Calculates the
future value of an investment.
o Example:
=FV(5%/12, 60, -100)
84.NPV - Returns the net
present value of an investment based on periodic cash flows.
o Example:
=NPV(0.1, A2:A10)
85.IRR - Returns the
internal rate of return for a series of cash flows.
o Example:
=IRR(A2:A10)
86.RATE -
Returns the interest rate per period of an annuity.
o Example:
=RATE(60, -200, 10000)
87.SLN - Returns the
straight-line depreciation of an asset.
o Example:
=SLN(10000, 2000, 5)
88.DB - Returns the
depreciation of an asset using the declining balance method.
o Example:
=DB(10000, 1000, 5, 1)
89.DDB - Returns the
depreciation of an asset using the double-declining balance method.
o Example:
=DDB(10000, 1000, 5, 1)
90.CUMIPMT
- Returns the cumulative interest paid on a loan between two periods.
o Example:
=CUMIPMT(5%/12, 60, 10000, 1, 12, 0)
91-100: Lookup and Reference Functions
91. VLOOKUP
- Searches for a value in the first column of a range and returns a value in
the same row from another column.
o Example:
=VLOOKUP(A1, B2:D10, 3, FALSE)
92.HLOOKUP
- Searches for a value in the first row of a range and returns a value in the
same column from another row.
o Example:
=HLOOKUP(A1, B1:H10, 4, FALSE)
93.INDEX
- Returns a value from within a specified range based on row and column
numbers.
o Example:
=INDEX(A1:C10, 2, 3)
94.MATCH
- Returns the position of a value in a range.
o Example:
=MATCH("Apple", A1:A10, 0)
95.LOOKUP
- Finds a value in a range and returns a corresponding value.
o Example:
=LOOKUP(A1, B1:B10, C1:C10)
96.CHOOSE
- Returns a value from a list based on a specified position.
o Example:
=CHOOSE(2, "Apple", "Banana", "Cherry")
97.OFFSET
- Returns a reference to a range that is a specified number of rows and columns
from a cell.
o Example:
=OFFSET(A1, 1, 2)
98.INDIRECT -
Returns the reference specified by a text string.
o Example:
=INDIRECT("A" & B1)
99.TRANSPOSE
- Converts a vertical range to a horizontal range and vice versa.
o Example:
=TRANSPOSE(A1:A10)
100.FORMULATEXT - Returns the formula in a given cell as text. - Example: =FORMULATEXT(A1)
These functions are essential tools for both beginners and advanced users, offering powerful capabilities for data analysis and calculations in Excel.
