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()