Spreadsheet
It is an application software that allows us to organize data in rows and columns. It allows to perform mathematical, logical operations in simple way. Some of the spreadsheet software are: MS-Excel, Lotus, Appleworks etc.
Basics of Spreadsheet
Rows: The horizontal space (left to right) is known as row. There are 1,048,576 rows.
Columns: The vertical space (top to bottom) is known as columns. There are 16,384 columns.
Cell: It is a rectangular space formed by the intersection of a rows and a columns.
Active cell
A cell that is currently selected is called active cell and its surrounded by dark lines, only one cell can be selected at one time and we can edit or enter the data.
Worksheet
It is a wide sheet made up of rows and columns where we enter data and perform all the calculations.(also known as spreadsheet)
Workbook
It is a file that contains one or more worksheet and chart sheets. By default workbook has three worksheet.
Chart sheet: It is a graphical representation of data. It contains only one chart.
Range of cell : A group of adjacent cells that forms a rectangle area is known as range of cell.
Name box and formula bar: It displays the address of active cell also known as address bar. The formula bar displays the content of active cell.
Formulas and Functions
Formula is the equation in worksheet that defines the relationship of two or more cells and performs the calculation.
Function is predefined formulas that performs calculation on specific values called arguments. Examples: Date and Time functions, Financial function, Logical functions etc. In Excel SUM(), MIN(), MAX(), AVERAGE() and IF() are some common functions.
IF function
It tests on values and formulas returns one values if condition is True and another value if the condition is False. It checks the condition and returns True or False value.
Syntax: IF(Logical_test, true_value, False_value)
OR Function
It returns True if any arguments is True. If all arguments are False then it returns False.
Syntax: OR(logical 1, logical 2)
AND Function
If returns True if all arguments are True otherwise it returns False.
Syntax: AND(logical 1, logical 2)
Examples
ex:1
Total: =SUM(C5:G5)
Percentage: =(H5/500*100)
Remarks: =IF(AND(C5>=40,D5>=40,E5>=40,F5>=40,G5>=40), “PASS”, “Fail”)
Highest marks in class: =MAX(C5:C12)
Lowest marks in class: =MIN(C5:C12)
Average marks in class: =AVERAGE(C5:C12)
Grade: =IF(C2>=90,”A+”,IF(C2>=80,”A”,IF(C2>=70,”B+”,IF(C2>=60,”B”,IF(C2>=50,”C+”,IF(C2>=40,”C”,IF(C2>=30, “D+”,IF(C2>=20,”D”,IF(C2>=0,”E”,”N”)))))))))
[C2 indicates the marks obtained]
GradePoint: =IF(C2>=90,4,IF(C2>=80,3.6,IF(C2>=70,3.2,IF(C2>=60,2.8,IF(C2>=50,2.4,IF(C2>=40,2,IF(C2>=30,1.6,IF(C2>=20,1.2,IF(C2>=0,0.8,”N”)))))))))
[C2 indicates marks.]
keep only two decimal points while doing mail merge: =TEXT(A1,”#,##0.00″)
[A1 indicates Grade Point. Example: Display 3.920032674134 as 3.92]
ex 2
Amount: =(C4*D4)
Grand Total: =SUM(E4:E7)
Discount 20%: =(E8*0.2)
Net Amount: =(E8-E9)
ex:3
If salary is More than or equals to Rs.45000 provide 20% Tax and if less than Rs.45000 10% Tax
=IF(D4>=45000,D4*20%,D4*10%)
