Chapter 16- Spreadsheet I PUC

Chapter-16
SPREADSHEET
1. What is spread sheet?
 A spreadsheet is a software tool for entering, manipulating and analyzing sets of number.

2. What is Workbook?
 A workbook is a multipage Excel document.

3. Define Cell.
 The intersection of rows and columns is called a cell.

4. What is cell address?
 Every cell is identified by unique address called cell address, which includes the column
alphabet followed by the row number.

5. Why is the cell pointer used?
 A rectangular box which is used to identify the active cell is called cell pointer.

6. What is the extension with which a workbook is saved?
 A workbook is saved under the extension .xls or .xlsx.

7. How many rows and columns are there in the worksheet?
 65,536 (10, 48,576) rows and 256 (16,384) columns are there in the worksheet.

8. Explain any five features of Spreadsheet or ESS or Excel.
a) Tip wizard
 provides helpful tips and techniques based on what you are doing

 More efficient
b) External Data:
 Allows you retrieve or load data from external data source and use it in your worksheet.

c) Autosum:
 You can add a large range of data by simply selecting a tool button.

d) Autofill:
 Helps you to fill rows or columns with series of data

e) Financial Analysis:
 Used to make quick and easy financial analysis

 You can also analyses data and create presentation with charts. \

f) Drag and drop features
 Helps you to move the data and text from one place to another simply by dragging the data
with help of mouse
1|Page
Chapter 16- Spreadsheet I PUC

g) Windows Interface
 Microsoft Excel is windows based package, therefore the user interface is consistent.

h) Webpage
 Used to create web pages with ease and also run queries on data available on the net.

i) Shortcut menus
 Commands appropriate to the task that you are doing by clicking the right mouse button.

BUILT –IN FUNCTIONS

 DATE AND TIME FUNCTIONS


1. DAY( ) – Returns day from the date
 Syntax :=DAY(“MONTH-DAY-YEAR”)

 Example :=DAY(“01-26-2017”)

 Ans : 26

2. MONTH( ) – Returns month from the date
 Syntax :=MONTH(“MONTH-DAY-YEAR”)

 Example :=MONTH(“01-26-2017”)

 Ans : 01

3. YEAR( ) – Returns year from the date
 Syntax:=YEAR(“MONTH-DAY-YEAR”)

 Example:=YEAR(“01-26-2017”)

 Ans : 2017

4. WEEKDAY ( ) – Returns Weekday from the date
 Syntax:=WEEKDAY(“MONTH-DAY-YEAR”)

 Example:=WEEKDAY(“01-26-2017”)

 Ans: 5

5. TODAY ( ) – Returns system date from the date
 Syntax:=TODAY( )

 Example:=TODAY( )

 Ans: 1/19/2017

6. NOW ( ) – Returns System date and time from the date
 Syntax:=NOW( )

 Example:=NOW( )

 Ans: 1/19/2017 10:08

2|Page
Chapter 16- Spreadsheet I PUC

7. DATE ( ) – Returns the serial number of the date or display the date in American format.
 Syntax:=DATE(YEAR,MONTH,DAY)

 Example:=DATE(2017,01,26)

 Ans: 1/26/2017

 ARITHMETIC FUNCTIONS
1. ABS ( )
 Returns the absolute value of the number.

 Number without a sign is called absolute number.

 Syntax : ABS(NUMBER)

 Example : =ABS(5 – 10)

 Answer : 5

2. SQRT ( )
 Returns the square root of the number.

 Syntax : SQRT(NUMBER)

 Example : =SQRT(100)

 Answer : 10

3. MOD ( )
 Returns the remainder after number is divided by divisor

 Syntax : MOD(NUMBER,DIVISOR)

 Example : =MOD(5,3)

 Answer : 2

4. PRODUCT ( )
 This function multiplies all the numbers given as arguments and returns the product

 Syntax : PRODUCT(NUMBER1,NUMBER2…..)

 Example : =PRODUCT(10,20)

 Answer : 200

5. SUM ( )
 Returns the sum of all the numbers in the list of A
1 10
arguments 2 20
 Syntax : SUM(NUMBER1,NUMBER2…..) 3 30
4 =SUM(A1:A3)
 Example : =SUM(A1:A3)
 Answer : 60

3|Page
Chapter 16- Spreadsheet I PUC

 STATISTICAL FUNCTIONS
A
1. MAX( ) – 1 10
 Returns the maximum value from the range of cells 2 20
 3 30
 Syntax : MAX(RANGE)
 4 =MAX(A1:A3)
 Example : =MAX(A1:A3)

 Answer : 30

2. MIN( ) –
 Returns the minimum value from the range of cells A
 1 10
 Syntax : MIN(RANGE)
 2 20
 Example : =MIN(A1:A3) 3 30

 Answer : 10 4 =MIN(A1:A3)
 A
3. AVERAGE( )
 Returns the Average value from the range of cells

 Syntax : AVERAGE(RANGE) A
 1 10
 Example : =AVERAGE(A1:A3)
 2 20
 Answer : 30 3 30
4 =AVERAGE(A1:A3)

 LOGICAL FUNCTIONS:
1. AND( )
 This function returns TRUE if all its arguments are TRUE, returns FALSE, if one or more
arguments are FALSE.

 Syntax : AND(LOGICAL1,LOGICAL2…)

 LOGICAL1, LOGICAL2 are 1 to 30 conditions you want to test.

 Example : =AND(10 > 5,8 >7)

 Answer : TRUE

 Example : =AND(10 > 5,8 < 7)

 Answer : FALSE

2. OR( )
 This function returns TRUE if one or more arguments are TRUE, returns FALSE, if all
arguments are FALSE.

 Syntax : OR(LOGICAL1,LOGICAL2…)

 LOGICAL1, LOGICAL2 are 1 to 30 conditions you want to test.

 Example : =OR(10 > 5,8 >7)

 Answer : TRUE
4|Page
Chapter 16- Spreadsheet I PUC

 Example : =OR(10 > 5,8 < 7)


 Answer : TRUE

3. NOT( )
 This function returns the value of its argument.

 Syntax : NOT(LOGICAL)

 Example : =NOT(10 > 5)

 Answer : FALSE

 Example : =NOT(8 < 7)

 Answer : TRUE

4. IF( )
 When the logical_test is true, then it returns value-if-true otherwise value-if-false.

 Syntax: IF(LOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE)

 Example : =IF(A2>35,”PASS”,”FAIL”)

 Answer : FAIL
A B
1 PER CLASS
2 34 =IF(A2>35,”PASS”,”FAIL”)

 FINANCIAL FUNCTIONS:
1. RATE( )
 Returns the interest rate for an annuity.

 Syntax: RATE( Number_Payments, Payment, PV [FV], [Type], [Estimate])

 Number_Payments is the number of payments for the annuity.

 Payment is the amount of the payment made each period.

 PV is the Present Value.

 FV is optional. It is the Future Value.

 Type is Optional. It indicates when the payments are due.

2. PMT( )
 Returns the payment for a loan based on constant amount and a fixed interest.

 Syntax: PMT(RATE,NPER,PV,FV,TYPE)

 RATE – Is the interest rate per period.

 NPER - Is the total number of payments, periods in an annuity.

 PV – Is the loan amount

 Example: = PMT(B1/12,B2*12,B3,0,1)

5|Page
Chapter 16- Spreadsheet I PUC

 Ans : 3,288.55
A B
1 Rate of interest 12%
2 TERM 3
3 Loan amount 100000.00
4 payment = PMT(B1/12,B2*12,B3,0,1)
3. FV( )
 Returns the future value of an investment based on periodic, constant payments and a constant
interest.

 Syntax: FV(RATE,NPER,PMT,FV,TYPE)

 RATE – Is the interest rate per period.

 NPER - Is the total number of payments, periods in an annuity.

 PMT – Is the payment made each period, includes principal amount, interest amount.

 Example: = FV(B1/12,B2*12,B3,0,1)

 Ans : 43,507.65
A B
1 Rate of interest 12%
2 TERM 3
3 Monthly Deposit 1000
4 Future Value = FV(B1/12,B2*12,B3,0,1)

Logical Functions
AND Returns TRUE if all its arguments are TRUE
IF Specifies a logical test to perform
NOT Reverses the logic of its argument
OR Returns TRUE if any argument is TRUE
Date and Time Functions
DATE Returns the serial number of a particular date
DATEDIF Calculates the difference between two dates.
DAY Converts a serial number to a day of the month
HOUR Converts a serial number to an hour
MINUTE Converts a serial number to a minute
MONTH Converts a serial number to a month
NOW Returns the serial number of the current date and time
TIME Returns the serial number of a particular time
TODAY Returns the serial number of today's date
WEEKDAY Converts a serial number to a day of the week
YEAR Converts a serial number to a year

6|Page
Chapter 16- Spreadsheet I PUC

Mathematical Functions
EVEN Rounds a number up to the nearest even integer
FACT Returns the factorial of a number
GCD Returns the greatest common divisor
INT Rounds a number down to the nearest integer
LCM Returns the least common multiple
MMULT Returns the matrix product of two arrays
MOD Returns the remainder from division
ODD Rounds a number up to the nearest odd integer
PI Returns the value of Pi
POWER Returns the result of a number raised to a power
PRODUCT Multiplies its arguments
QUOTIENT Returns the integer portion of a division
ROUND Rounds a number to a specified number of digits
SUM Adds its arguments
Text Functions
CONCATENATE Joins several text items into one text item
EXACT Checks to see if two text values are identical
FIND Finds one text value within another (case-sensitive)
LEFT Returns the leftmost characters from a text value
LEN Returns the number of characters in a text string
LOWER Converts text to lowercase
REPLACE Replaces characters within text
RIGHT Returns the rightmost characters from a text value
TRIM Removes spaces from text
UPPER Converts text to uppercase

 What is Macro?

 Macro is a small program that carries out pre-defined and pre-recorded series of steps by giving
a few keyboard shortcuts or by running the macro name.

 Explain the steps for creating the macro.

 Select TOOLS->MACRO-> RECORD NEW MACRO

o This opens the Record Macro dialog box, specify the macro name and keyboard shortcut.
Then Click on OK. A very small “Stop Recording” toolbar will appear on the screen.

 You can now proceed on to record your macro. Just perform all the essential steps to complete
your particular task

 The click on “Stop Recording” button available on the “Stop Recording” toolbar.

7|Page
Chapter 16- Spreadsheet I PUC

 Run the macro by using keyboard shortcut, i.e.”ctrl + shift + key” or select TOOLS-
>MACRO->MACROS, Select macro name that you want to execute.

 How do you delete a Macro?

 Select TOOLS->MACRO->MACROS, select the macro name.

 Click on Delete.

 Mention the types of Databases

 Internal Database – Database created in the worksheet.

 External Database – Database which is created through other DBMS or RDBMS package like
MS-ACCESS, FOXPRO, dBASE, SQL SERVER, ORACLE etc.

 Creation of internal databases using built –in data form

 The Databases are entered and maintained by typing directly onto a worksheet.

 Functions of DATA->FORM

 Displays one record at a time

 Allow you to add new records and edit existing records

 Allow you to view records matching the specified criteria.

 What is Chart?

 Chart is a pictorial or graphical representation of numeric data.

 Write any five advantages of Chart.

 Data Effective

 Interesting and various formatting options are available

 Easy to understand

 Easy to Analyze

 Compare data

 Mention any types of Chart

 Line chart

 Bar Chart

 Column Chart

 Pie Chart

 Area Chart

 Explain the steps for creation of a chart.

 To create a chart

8|Page
Chapter 16- Spreadsheet I PUC

1. Select all the cells containing data including labels.

2. Click the Insert tab. Sel ect any type of the Charts group. A
list of chart sub-types ap pears.
3. Click any Chart option.
4. The chart will be created depending upon your choice.

CHAPTER 16 – SPREADSHEET BLUE PRINT

VSA (1 marks) SA (2 marks) LA (3 Marks) Essay (5 Mar ks) Total
01 Question - - 02 Questio n 11 Marks

**************

9|Page