50question Excel Assignments
50question Excel Assignments
Agent Name First Space Position Second Space Position Third Space Position
Manoj Kumar Jaihind Yadav
Tabrez Md Shahid Alam
Q3 Please fill data as per below grid..
Grid Output
0% Fail
41% Pass
61% First
81% A
100% A+
Branch GCount
BG 2
BG 3
BG 4
BG 5
BG 6
BG 7
BG 8
Total 0
Q10 What is the sum of active agent as per below fields…
Ans.
Q15 What is the number of count without space for below mention name..
Text Hello
Ans
Q17 What is the male candidate %...
male
female
female
female
male
male
male
male
female
female
male
female
male
male
Q18 What is the total sum of below nos.
Ans. 23 66
43 66
#N/A
#Div/#
AA
66
66
Q21 Please change date format as per below example by using formula..
Monday, October 10, 2011 10/10 October-11 10-Oct 10-Oct-11 Monday, October 10, 2011
10/9/11 12:11 AM 09 October 2011 09-10 October-11
10/10/11 12:11 AM
10/11/11 12:11 AM
10/12/11 12:11 AM
10/13/11 12:11 AM
10/14/11 12:11 AM
10/15/11 12:11 AM
10/16/11 12:11 AM
10/17/11 12:11 AM
10/18/11 12:11 AM
10/19/11 12:11 AM
10/20/11 12:11 AM
Q24 What is the %ach of Active,Suspended and deleted agent.
Status %age
Active 21%
Suspended 21%
deleted 19%
Terminated 21%
Q25 How to remove default row from excel.
Ans.
Q26 How to link current sheet to first sheet. Agent ID
Data
Male
Male
Female
Male
Female
Female
Male
Male
Male
Female
Q30 Which person made sales = 4521.
http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/
Sales Person
Prince Kumar
Q32 Who sold to minimum number of customers?
http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/
No Customers Sales Person
4 #N/A
Q33 Please color >50% to red >80% to blue & >100% to green…..
%age
79%
115%
142%
128%
47%
39%
162%
89%
124%
124%
62%
63%
114%
118%
100%
92%
60%
50%
201%
94%
146%
94%
105%
132%
44%
76%
67%
110%
91%
31%
91%
155%
132%
117%
9%
126%
103%
52%
267%
71%
129%
106%
32%
133%
47%
51%
33%
60%
102%
94%
115%
86%
82%
75%
123%
192%
43%
100%
36%
23%
56%
140%
199%
169%
85%
129%
121%
73%
71%
97%
99%
66%
76%
201%
112%
100%
25%
73%
133%
118%
156%
33%
107%
74%
192%
45%
55%
293%
200%
125%
256%
117%
264%
126%
151%
135%
77%
117%
226%
56%
217%
138%
202%
182%
123%
6%
99%
123%
137%
143%
135%
88%
31%
102%
60%
159%
129%
74%
145%
69%
81%
142%
81%
47%
101%
110%
68%
102%
199%
119%
48%
163%
104%
60%
93%
70%
72%
90%
126%
176%
146%
79%
95%
238%
95%
119%
146%
119%
205%
104%
168%
62%
107%
143%
144%
128%
115%
42%
122%
141%
150%
67%
30%
35%
73%
43%
77%
29%
71%
45%
214%
23%
36%
115%
54%
130%
86%
163%
115%
94%
156%
58%
108%
160%
190%
100%
163%
355%
176%
83%
200%
114%
60%
53%
135%
108%
195%
92%
79%
95%
149%
92%
70%
54%
86%
36%
203%
112%
108%
119%
127%
110%
136%
160%
76%
92%
167%
58%
85%
223%
147%
192%
78%
93%
11%
81%
44%
236%
96%
100%
145%
152%
119%
95%
94%
84%
97%
150%
100%
142%
35%
78%
176%
97%
60%
83%
82%
150%
74%
165%
52%
79%
119%
103%
192%
82%
75%
148%
39%
170%
88%
125%
73%
54%
159%
193%
89%
99%
138%
128%
75%
78%
339%
211%
85%
96%
101%
223%
103%
109%
236%
151%
146%
127%
55%
243%
56%
105%
156%
279%
335%
80%
224%
93%
86%
76%
85%
131%
91%
78%
104%
101%
368%
92%
131%
83%
716%
140%
32%
77%
113%
139%
496%
267%
41%
368%
45%
55%
74%
98%
100%
81%
152%
64%
70%
77%
134%
93%
123%
68%
131%
155%
39%
102%
131%
17%
94%
56%
175%
78%
77%
182%
119%
98%
171%
97%
120%
75%
113%
95%
122%
136%
51%
62%
60%
129%
133%
128%
69%
64%
53%
149%
135%
194%
257%
158%
50%
106%
120%
145%
61%
91%
72%
144%
44%
215%
114%
142%
122%
112%
44%
97%
170%
63%
79%
119%
165%
72%
199%
159%
76%
86%
139%
120%
323%
172%
160%
335%
105%
127%
97%
219%
182%
207%
182%
156%
157%
48%
107%
63%
49%
77%
134%
163%
190%
33%
192%
122%
105%
89%
206%
123%
75%
111%
151%
151%
346%
207%
91%
123%
59%
50%
55%
63%
93%
136%
380%
188%
45%
147%
118%
117%
133%
89%
220%
108%
161%
64%
140%
185%
43%
163%
261%
182%
148%
115%
128%
75%
117%
146%
50%
78%
178%
90%
86%
151%
265%
119%
100%
169%
189%
208%
135%
143%
146%
189%
64%
206%
72%
61%
187%
89%
72%
104%
23%
98%
127%
140%
53%
55%
46%
102%
67%
191%
377%
363%
280%
175%
38%
63%
61%
140%
186%
141%
118%
45%
39%
89%
232%
141%
68%
133%
149%
196%
66%
73%
122%
253%
162%
117%
138%
169%
237%
81%
146%
327%
122%
165%
88%
157%
176%
65%
115%
115%
68%
171%
85%
122%
101%
93%
98%
81%
148%
245%
64%
107%
63%
76%
124%
175%
104%
147%
29%
118%
83%
139%
140%
103%
85%
111%
161%
135%
9%
75%
34%
92%
111%
80%
67%
71%
38%
144%
270%
54%
159%
149%
177%
140%
218%
133%
74%
116%
93%
16%
115%
152%
84%
89%
79%
88%
49%
198%
69%
86%
103%
34%
195%
68%
101%
159%
91%
145%
89%
141%
192%
191%
71%
101%
320%
126%
77%
78%
244%
118%
99%
160%
164%
118%
143%
134%
112%
68%
116%
270%
200%
77%
86%
49%
775%
71%
81%
79%
120%
163%
146%
160%
140%
101%
221%
112%
39%
31%
43%
34%
120%
85%
78%
183%
131%
148%
60%
192%
72%
37%
78%
129%
138%
184%
200%
50%
192%
44%
11%
301%
214%
135%
201%
164%
124%
160%
123%
61%
97%
164%
40%
33%
87%
94%
76%
121%
39%
53%
93%
109%
167%
46%
103%
67%
152%
32%
95%
84%
161%
114%
94%
177%
256%
336%
274%
63%
152%
94%
130%
207%
137%
238%
70%
117%
91%
227%
136%
193%
77%
88%
71%
116%
144%
109%
151%
49%
128%
53%
123%
294%
313%
77%
80%
91%
98%
110%
54%
146%
64%
100%
206%
81%
47%
117%
27%
90%
108%
159%
60%
189%
77%
136%
71%
64%
166%
151%
97%
114%
119%
147%
256%
67%
230%
174%
0%
182%
147%
50%
226%
172%
52%
70%
71%
100%
173%
168%
68%
78%
57%
40%
123%
158%
82%
158%
123%
131%
243%
102%
738%
87%
90%
234%
200%
171%
94%
78%
61%
14%
70%
221%
164%
53%
148%
25%
181%
84%
131%
272%
235%
95%
50%
90%
295%
83%
145%
242%
99%
53%
315%
16%
23%
121%
174%
75%
175%
84%
113%
130%
161%
145%
143%
228%
231%
240%
85%
61%
73%
32%
152%
70%
145%
45%
93%
68%
133%
306%
129%
240%
103%
121%
122%
152%
243%
68%
198%
88%
137%
146%
594%
121%
213%
65%
177%
250%
87%
132%
115%
159%
18%
180%
52%
80%
70%
103%
8%
97%
168%
120%
201%
44%
70%
95%
88%
203%
157%
144%
185%
260%
121%
178%
129%
287%
120%
102%
124%
137%
219%
111%
205%
106%
45%
136%
172%
99%
134%
179%
45%
102%
166%
336%
62%
267%
220%
106%
159%
157%
184%
332%
95%
14%
177%
70%
60%
63%
130%
136%
63%
110%
151%
424%
88%
185%
162%
134%
132%
86%
198%
79%
113%
294%
409%
256%
137%
280%
45%
143%
73%
348%
253%
206%
98%
51%
113%
269%
221%
145%
30%
833%
159%
120%
181%
142%
50%
142%
384%
113%
86%
179%
148%
105%
120%
89%
39%
147%
247%
85%
427%
85%
118%
27%
264%
96%
156%
127%
153%
214%
74%
178%
48%
147%
109%
41%
145%
435%
343%
217%
201%
121%
206%
148%
6%
34%
244%
73%
218%
275%
177%
76%
82%
90%
152%
95%
112%
394%
157%
147%
187%
261%
81%
141%
134%
23%
117%
84%
64%
185%
65%
82%
22%
138%
33%
485%
107%
69%
8%
88%
137%
123%
29%
56%
104%
243%
163%
138%
104%
51%
152%
67%
104%
436%
170%
138%
139%
49%
253%
102%
63%
223%
133%
282%
147%
94%
80%
156%
164%
354%
224%
214%
95%
57%
116%
169%
121%
103%
119%
285%
270%
199%
84%
157%
134%
84%
59%
24%
185%
126%
82%
116%
101%
96%
149%
103%
106%
160%
312%
136%
91%
72%
150%
233%
144%
141%
52%
73%
85%
100%
30%
106%
131%
184%
71%
91%
99%
11%
103%
244%
102%
6%
130%
114%
110%
135%
128%
147%
80%
173%
93%
137%
245%
142%
22%
120%
315%
88%
181%
43%
96%
452%
102%
297%
117%
64%
78%
145%
174%
127%
5%
744%
57%
35%
174%
205%
115%
179%
35%
41%
67%
23%
48%
127%
119%
111%
117%
106%
149%
241%
82%
136%
166%
64%
24%
219%
84%
97%
161%
401%
84%
88%
65%
122%
66%
114%
117%
217%
206%
128%
145%
190%
191%
161%
147%
12%
141%
109%
45%
267%
113%
86%
58%
207%
153%
86%
73%
92%
117%
316%
154%
176%
28%
125%
46%
93%
106%
###
80%
134%
120%
51%
108%
135%
102%
144%
76%
201%
126%
265%
345%
21%
104%
185%
153%
157%
30%
24%
97%
316%
110%
70%
254%
310%
42%
207%
34%
50%
63%
183%
114%
178%
117%
70%
21%
219%
75%
49%
82%
200%
75%
197%
47%
94%
98%
40%
50%
497%
###
28%
62%
61%
97%
11%
69%
299%
71%
270%
86%
261%
223%
12%
888%
149%
51%
171%
72%
95%
52%
108%
78%
70%
26%
45%
19%
15%
276%
83%
348%
301%
56%
245%
19%
96%
197%
185%
57%
170%
50%
81%
30%
22%
131%
108%
648%
143%
140%
373%
93%
68%
304%
172%
590%
159%
87%
139%
101%
11%
177%
273%
95%
103%
127%
355%
53%
257%
93%
72%
17%
442%
9%
54%
42%
352%
24%
5%
242%
40%
101%
11%
177%
273%
95%
103%
127%
355%
53%
257%
93%
72%
17%
442%
9%
54%
42%
352%
24%
5%
242%
40%
Q35 Please color duplicate value.
Agent ID
376172
376175
376172
376184
376187
376172
376238
376187
375347
376187
375355
375355
375365
375366
375355
375366
375366
Q36 Please hide the formula which is showing in below row..
13-Dec-15 7-Oct-79
13-Dec-15 25-Oct-63
13-Dec-15 11-Nov-47
13-Dec-15 27-Nov-31
13-Dec-15 13-Dec-15
Q38 Please fill below field..
Agent ID Agent Name Join Date Status GO Reporting To SM ID Branch Group Plan Actual %age
376172 Manoj Kumar Jaihind 12-Apr-10 Active X0072 399532 BG 5 1199561 951452 79%
376175 Tabrez Md Shahid Ala 12-Apr-10 Active X0415 419863 BG 5 1001514 1151591 115%
376181 Alok Anil Parkar 12-Apr-10 Active X1291 399534 BG 7 622708 885633 142%
376184 Ankita Kishor Wadkar 12-Apr-10 Active X1892 377076 BG 7 489534 628449 128%
376187 Aniket Suresh Manjre 4-Jan-10 Active X0108 377076 BG 7 1697902 791370 47%
376196 Ranjani S 10-Apr-10 Active X0345 387266 BG 7 1489572 581866 39%
376238 Anugrah singh Hiraman12-Apr-10 Active X0219 419863 BG 6 700215 1133645 162%
375340 Amit Kumar 12-Apr-10 Suspended X0279 391321 BG 6 710336 631906 89%
375347 Prem Prakash 12-Apr-10 Suspended X0079 460909 BG 6 1394015 1733935 124%
375350 Adity Roy 12-Apr-10 Suspended X0696 460909 BG 5 1115157 1386513 124%
375355 Jitendra Khatri 19-Apr-10 Suspended X0031 424950 BG 5 919591 574544 62%
375359 Vijyendra Kumar Dixit 12-Apr-10 Suspended X1744 377963 BG 6 874452 552111 63%
375365 Shiv Kumar 19-Apr-10 Suspended X0166 377329 BG 5 1202039 1373674 114%
375366 Lalan Kumar 19-Apr-10 Suspended X0588 411082 BG 6 1065405 1252551 118%
375367 Pradeep Yadav 12-Apr-10 Suspended X0895 411082 BG 6 811765 813095 BG100%
5 BG 5 BG 7
375692 Manoj Kumar 12-Apr-10 Suspended X0431 411082 BG 6 1089281 1000552 BG92%
5 BG 5 BG 6
375699 Prince Kumar 12-Apr-10 Suspended X0207 460909 BG 6 1882688 1129711 60%
Plan
BG 5 BG 5 BG 7 BG 7 BG 7 BG 7 BG 6 BG 6 BG 6
BG 5 BG 5 BG 6 BG 5 BG 6 BG 6 BG 6 BG 6
Q43. what is the branch group wise sale%, through BAR Chart
Agent ID Agent Name Join Date Status GO Reporting To SM ID Branch Group Plan Actual %age
376172 Manoj Kumar Jaihind 12-Apr-10 Active X0072 399532 BG 5 1199561 951452 79%
376175 Tabrez Md Shahid Ala 12-Apr-10 Active X0415 419863 BG 5 1001514 1151591 115%
376181 Alok Anil Parkar 12-Apr-10 Active X1291 399534 BG 7 622708 885633 142%
376184 Ankita Kishor Wadkar 12-Apr-10 Active X1892 377076 BG 7 489534 628449 128%
376187 Aniket Suresh Manjre 4-Jan-10 Active X0108 377076 BG 7 1697902 791370 47%
376196 Ranjani S 10-Apr-10 Active X0345 387266 BG 7 1489572 581866 39%
376238 Anugrah singh Hiraman12-Apr-10 Active X0219 419863 BG 6 700215 1133645 162%
375340 Amit Kumar 12-Apr-10 Suspended X0279 391321 BG 6 710336 631906 89%
375347 Prem Prakash 12-Apr-10 Suspended X0079 460909 BG 6 1394015 1733935 124%
375350 Adity Roy 12-Apr-10 Suspended X0696 460909 BG 5 1115157 1386513 124%
375355 Jitendra Khatri 19-Apr-10 Suspended X0031 424950 BG 5 919591 574544 62%
375359 Vijyendra Kumar Dixit 12-Apr-10 Suspended X1744 377963 BG 6 874452 552111 63%
375365 Shiv Kumar 19-Apr-10 Suspended X0166 377329 BG 5 1202039 1373674 114%
375366 Lalan Kumar 19-Apr-10 Suspended X0588 411082 BG 6 1065405 1252551 118%
375367 Pradeep Yadav 12-Apr-10 Suspended X0895 411082 BG 6 811765 813095 100%
375692 Manoj Kumar 12-Apr-10 Suspended X0431 411082 BG 6 1089281 1000552 92%
375699 Prince Kumar 12-Apr-10 Suspended X0207 460909 BG 6 1882688 1129711 60%
Q46. Protect a workbook through password.
Q47. how to remove error from below data.
Item Plan
A 324,121 9183646 6 COUNT-6 SUM-9183646
B 2,455,078
A 1,598,048
D 582,882
E 842,074
B 293,416
A 419,782
B 139,261
B 1,000,000
A 3,114,227
D 2,172,534
D 1,589,999
A 2,100,380
B 1,046,399
C 770,641
C 2,822,285
A 1,627,088
E 639,672
E 2,660,632
D 1,047,563