Computer Function in excel For Std 11

Chapter 10         Functions In Excel

String Functions

1.     The Concatenate( ) function

Select - Cell H3

Type - = CONCATENATE( B3,C3,D3)

Result - PujaSSethia

Function Bar Type - = CONCATENATE( B3, “ ”, C3, “ ”,D3)

Result – Puja S Sethia

2.     The Left( ) Function

Select -                  Cell H3

 Function Bar Type - = LEFT( F3,1)

Result – A                                          [ ARTS]

3.     The Right( ) Function

Select - Cell H3

Function Bar Type - = RIGHT( F3,2)

Result – ts                                          [ ARTS]

4.     The Mid( ) Function

Select - Cell H3

Function Bar Type - = MID( F4,3,4)

Result – mmer                                  [ COMMERCE]

5.     The Trim( ) Function

Select - Cell A3

Function Bar Type - = Trim( A3)

Result – Harshit Amit Jain

6. The Len( ) Function

Select - Cell B2

Function Bar Type - = Len( B2)

Result –  4

7.    Lookup/ Reference functions.

Vlookup( ) function

	A	B	C
1	Product Number	Name of Producmt	Rate of Product
2	1	School bag	400
3	2	Water bottle	60
4	3	Lunch box	120
5	4	Fullscape Notebook	15
6	5	Marker pen	10

Fig. 10.4        Sample list of Products

Sheet 1 –          Rename                Product list   ( Just include the data)

Sheet 2-           Rename                Orders

Sheet 1 –

Select          Cell range A2…C6

Title bar -    Insert

                             Name

Define

Products

	A	B	C	D	E	F	G
1	Order No	Date	Product Number	Product Name	Qty	Rate	Amount
2				=VLOOKUP(C2,Products,2,0)		=VLOOKUP(C2,Products,3,0)	=E2*F2
3
4

Sheet 2     (Orders)-

Select-                   Cell  D2

Click -                   Function bar

                             Vlookup

Function Argument Box –

                   Lookup_value –              C2              = 0

                   Table _Array-                 Products     = { 1,”School bag”,400.

                   Col_Index_num-             2                 = 2

                   Range_lookup-                0                 = False

Ok

Result –       Cell D2 …  # N/A          Information is not available

Select -                  Cell   F2

Click -                   Function bar

                             Vlookup

Function Argument Box –

                   Lookup_value –              C2              = 0

                   Table _Array-                 Products     = { 1,”School bag”,400.

                   Col_Index_num-             3                 = 3

                   Range_lookup-               0                 = False

Ok

Result –       Cell F2 …  # N/A          Information is not available

 
Select -                  Cell   G2

Function bar -        = E2*F2

Hlookup( )

	A	B	C	D	E	F
1	Product Number	1	2	3	4	5
2	Name of Product	School bag	Water bottle	Lunch box	Fullscape Notebook	Marker pen
3	Rate of Product	400	60	120	15	10

Tab 10.10      Horizontal lookup table

Sheet 1 –       Rename             Product list Hor.   ( Just include the data)

Sheet 2-          Rename            Orders Hor.

Sheet 1 –

Select          Cell range  B1…F3

Title bar -    Insert

                   Name

Define

Products Hor.

Sheet 2 – Orders Hor.

	A	B	C	D	E	F
1	Product Number		2	5	3
2	Name of Product	=HLOOKUP(B1,Products hor,2,0)	Water bottle	Marker pen	Lunch box
3	Qty.		30	8	15
4	Rate of Product	=HLOOKUP(B1,Products hor.,3,0)	60	10	120
5	Amount	=B3*B4	1800	80	1800

Select-                   Cell  B2

Click -                   Function bar

                             Hlookup

Function Argument Box –

                 Lookup_value –    B1                        = 0

                 Table _Array-        Products  Hor      = { 1,2,3,4,5,”School bag”,400.

                 Row_Index_num- 2                           = 2

                 Range_lookup-      0                           = False

Ok

Result –       Cell B2 …  # N/A          Information is not available

Select-                   Cell  B4

Click -                   Function bar

                             Hlookup

Function Argument Box –

                 Lookup_value –    B1                        = 0

                 Table _Array-        Products  Hor      = { 1,2,3,4,5,”School bag”,400.

                 Row_Index_num- 3                           = 3

                 Range_lookup-      0                           = False

Ok

Result –       Cell B4 …  # N/A          Information is not available

Select -                  Cell   B5

Function bar -        = B3*B4

 

	A	B	C
1		Percentage	Grade
2		0	F
3		35	D
4		50	C
5		65	B
6		80	A
7		100

Fig 10.11     Grade lookup table

                                                   

 Sheet 5-  Grade lookup

Select -                                       Cell Range B2.. C7    

Title Bar –                 INSERT

Dropdown Menu -     NAME

                               DEFINE

                                                Grade

	A	B	C	D	E	F	G	H
1
2	Roll NO	First Name	Middle Name	Last Name	Boy/Girl	Stream	%age in 10th Std.	Grade
3	1	Puja	S	Sethia	G	Arts	99	A
4	2	Varun	U	Gangele	G	Commerce	82	A
5	3	Zakiya	S	Malek	G	Commerce	56	C
6	4	Harshit	A	Jain	B	Science	74	B
7	5	Aryan	V	Nair	B	Science	62	C
8	6	Varun	J	Pathak	B	Science	89	A
9	7	Prachi	M	Thakkar	G	Arts	80	A
10	8	Amit	N	Surena	B	Science	67	B
11	9	Suvidha	M	Arolkar	G	Science	79	B
12	10	Nihal	P	Shah	B	Arts	45	D

Select-                   Cell  H3

Click -                   Function bar

                             Vlookup                         =VLOOKUP(G3,Grade,2,1)

Function Argument Box –

                   Lookup_value –              G3              = 0

                    Table _Array-                 Grade         = { “percentage”, “Grade”

                   Col_Index_num-             2                 = 2

                   Range_lookup-               1                 =True

Ok

 

8.  The Match() Function.

Select - Cell I3

Function Bar Type -        = Match( I3)

Result –   7                           (Match function is always a number)

9. The Index() Function.  

Select - Cell I3

Function Bar Type -        =INDEX(B3:B13,7)

Result –   Prachi

    The Index() Function.     

Select - Cell I3

Function Bar Type -        =INDEX(B3:E12,5,4)

Result –   Nair

10. Combining Index and Match function.

	A	B	C	D	E	F	G	H
1
2		2001	2002	2003	2004	2005	2006	2007
3	8	Mount Abu	Jaipur	Diu	Shimla	Junagadh	Ajmer	Goa
4	9	Sasangir	Junagadh	Nainital	Pushkar	Udaipur	Goa	Mount Abu
5	10	Udaipur	Shimla	Jaipur	Goa	Mahabaleshwer	Shimla	Nainital
6	11	Manali	Goa	Mount Abu	Ajmer	Sasangir	Mahabaleshwer	Sasangir
7	12	Goa	Nanital	Mahabaleshwar	Shimla	Jaipur	Mount Abu	Mahableshwer

Table 10.14       Creating Row and Column lookup

	A	B	C
10	Class	9	2	=MATCH(B10,A3:A7,0)
11	Year	2002	3	=MATCH(B11,A2:H2,0)
12	Place visited	Junagadh

Select -              Cell C10

Type -               =MATCH(B10,A3:A7,0)        [ 2, Second row of data table]

Select -              Cell C11

Type -               =MATCH(B11,A2:H2,0)        [ 3, Third column of data table]

Select -              Cell B12

Type -               = index(B3:H7,2,3)

Select -              Cell B12

Type -     =INDEX(B3:H7,MATCH(B10,A3:A7,0),MATCH(B11,B2:H2,0))

11. Date and Time function.

	A	B	C	D	E	F	G	H	I
1	Roll NO	First Name	Middle Name	Last Name	Birthdate	Age	Date of Joining	Date of leaving	No. of years spent in school
2	1	Puja	S	Sethia
3	2	Varun	U	Gangele
4	3	Zakiya	S	Malek
5	4	Harshit	A	Jain
6	5	Aryan	V	Nair
7	6	Varun	J	Pathak
8	7	Prachi	M	Thakkar
9	8	Amit	N	Surena
10	9	Suvidha	M	Arolkar
11	10	Nihal	P	Shah
12	11	Robina	U	Arora

Table 10.15      Worksheet for demonstration of date functions.

Select -    Cell  E2

Title bar -          Insert

                         Function

Dialog box

12. The Today( ) function.

      Select -        Any Cell

      Type -         =Today()

Faraday's Experiments on Magnet

 Faraday's Experiment

Faraday's Experiment

Faraday wound a long copper wire, on a cardboard cylinder. Between the turns, he wound twine and between the layers he placed calico-cloth. The ends of the wire were connected to a galvanometer.

Faraday thrust a pole of a bar magnet quickly into the coil. The galvanometer showed the presence of an electric current. He pulled the magnet out of coil. The pointer of the galvanometer deflected, showing the presence of an electric current; but this time the pointer moved in the opposite direction. The amount of deflection was found to increase with the increase in speed of the magnet. He found that when the magnet was at rest inside the coil, no electricity was produced. He repeated the experiment in a different way, moving the coil and keeping the magnet still. The result was the same. Thus, he discovered how magnetism could produce electricity. Relative motion between the conductor and the magnet produces electricity in the conductor.

Activity

i.    Conduct the above experiment by using magnets of different strengths and a coil of insulated copper wire.

ii.   Keep the magnet stationary and move the coil.

iii.  Increase the number of turns of the coil and repeat the experiment.

iv. Observe the deflections for different speeds of the magnet.

Record your observations in all the situations. 
What conclusion can you draw out of your observation?

MICHAEL FARADAY

(1791 – 1867)

Michael Faraday

Faraday was born in a poor blacksmith’s family. He has made remarkable discoveries in both physics and chemistry. He was an assistant to Sir Humphery Davy. In 1831, he liquified chlorine and he discovered banzene in 1825. in 1831, he conducted his experiments on electro-magnetic induction. He has formulated the laws of electrolysis also.
Food for thought

Food for thought

1.Why did Faraday wind twine in between the turns of copper wire?

2.Why did he place calico-cloth in between layers?

What you know about magnetic field

What do you know 

Magnetic Field

As you know, a current flowing through a conductor produces a magnetic field around it.

Can we have reverse effect of this?

Can a magnetic field produce an electric field?

Michael Faraday, a British scientist, answered this question through a series of experiments.

Electromagnetic Induction

Review

An electric current passing through a conductor produces a magnetic field around it. This effect of electric current is called magnetic effect.


When a cell is not connected to a load, the potential difference between the poles of the cell is called its electro motive force (emf).

The S,I. unit of potential difference and ‘electro motive’ force is ‘volt’ and the device used to measure them is ‘voltmeter’.

Dry Cell

 

 

Voltmeter