Sunday, 20 March 2016


consolodate
exam 1
roll no  sub1 sub2 sub3 sub4 sub5 sub6
1001 45 52 59 45 52 59
1002 78 57 36 78 57 36
1003 89 58 27 89 58 27
1004 98 59 20 98 59 20
1005 78 89 100 78 89 100
1006 45 52 59 45 52 59
1007 78 57 36 78 57 36
1008 89 58 27 89 58 27
1009 98 59 20 98 59 20
1010 78 89 100 78 89 100
1011 78 57 36 78 57 36
1012 78 57 36 78 57 36
1013 78 57 36 78 57 36

exam 2
roll no  sub1 sub2 sub3 sub4 sub5 sub6
1001 78 52 26 78 78 78
1002 89 57 25 89 89 89
1003 78 58 38 78 78 78
1004 45 59 73 45 45 45
1005 79 89 99 79 79 79
1006 98 52 6 98 98 98
1007 56 57 58 56 56 56
1008 89 58 27 89 89 89
1009 56 59 62 56 56 56
1010 45 89 133 45 45 45
1011 69 57 45 69 69 69
1012 74 57 40 74 74 74
1013 39 57 75 39 39 39
exam 3
roll no  sub1 sub2 sub3 sub4 sub5 sub6
1001 78 52 26 78 86 78
1002 89 57 25 89 65 89
1003 78 58 38 78 78 78
1004 45 59 73 45 45 45
1005 79 89 99 79 79 79
1006 98 52 78 98 98 98
1007 56 57 99 56 56 56
1008 89 58 68 89 89 89
1009 56 59 78 56 56 56
1010 45 89 133 45 45 45
1011 69 57 45 69 69 69
1012 74 57 40 74 74 74
1013 39 57 75 39 39 39
 consolidate data for exam 1,exam2,exam3
sum
max
min
avg

qu 1
sale name itm1 itm2 itm3 itm4 itm5
aaa 45 0 45 12 0
bb 78 78 89 45 12
ccc 89 156 133 78 24
dd 56 234 177 111 36
ee 78 312 221 144 48
ff 56 390 265 177 60
gg 45 468 309 210 72
jj 89 546 353 243 84

qu 2
sale name itm1 itm2 itm3 itm4 itm5
aaa 7 0 45 12 0
bb 0 78 89 45 12
ccc 0 156 133 78 24
dd 56 234 177 111 36
ee 8 312 221 144 48
ff 56 78 78 177 60
gg 45 468 98 210 72
jj 89 546 353 243 84


 consolidate data for quat1,quat2
sum
max
min
avg



SCENARIO FOR five period(12,24,36,48)

AMT 100000
RATE 15%
PERIOD 60
EMI ($2,378.99)

SCENARIO FOR THRE  YEARS 
INCOME 780000
EXPEN 95000
PROFIT 685000


MATCH-INDEX
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000
FIAT 45000 78900 112800 146700 180600 214500 248400 282300 316200 350100 384000
CEAT 89000 89000 89000 89000 89000 89000 89000 89000 89000 89000 89000
GODREJ 95000 99100 103200 107300 111400 115500 119600 123700 127800 131900 136000
TATA 98000 109200 120400 131600 142800 154000 165200 176400 187600 198800 210000
AAA 68000 119300 170600 221900 273200 324500 375800 427100 478400 529700 581000
BBB 95500 129400 163300 197200 231100 265000 298900 332800 366700 400600 434500
CCC 101000 139500 178000 216500 255000 293500 332000 370500 409000 447500 486000
DD 106500 149600 192700 235800 278900 322000 365100 408200 451300 494400 537500
EEEE 112000 159700 207400 255100 302800 350500 398200 445900 493600 541300 589000
FFF 117500 169800 222100 274400 326700 379000 431300 483600 535900 588200 640500
GGG 123000 179900 236800 293700 350600 407500 464400 521300 578200 635100 692000
HHH 128500 190000 251500 313000 374500 436000 497500 559000 620500 682000 743500
III 134000 200100 266200 332300 398400 464500 530600 596700 662800 728900 795000
JJJ 139500 210200 280900 351600 422300 493000 563700 634400 705100 775800 846500
KKK 145000 220300 295600 370900 446200 521500 596800 672100 747400 822700 898000


COMPANY TATA
YEAR 1995
SALES 154000


Posted by at No comments:

Thursday, 10 March 2016

IF PRACTICAL 
Name Sex Attendance Assignment Mid-term Final Total Grade
Virak M 9 12 14 45 80
Soa M 13 11 12 34 70
Vibol M 8 8 12 26 54
Theary F 8 14 14 25 61
Sokha M 12 12 14 40 78
1. By using if function, determine the student grade based on the following rules:
Total score>=90 then grade A
90>Total score>=80 then grade B
80>Total score>=70 then then grade C
70>Total score>=60 then grade D
60>Total score>=50 then grade E
Else then grade F
2. By using if function, display the word "Pass" or "Fail" under the Description column
Students who pass need to have total score greater than or equal to 50.



IF
SR NAME M1 M2 M3 M4 M5 M6 TOTAL AVG RESULT
1002 sanju 70 70 70 70 70 69 419 69.83333
1003 dada 25 25 26 25 25 25 151 25.16667
1004 asmita 89 58 58 89 98 98 490 81.66667
1005 kajal 59 95 59 78 35 65 391 65.16667
1006 sanju 59 78 56 48 89 98 428 71.33333
1007 mangesh 58 48 98 49 52 68 373 62.16667
1008 lina 58 49 58 45 89 68 367 61.16667
1009 dada 58 78 27 78 68 56 365 60.83333
1010 sanju 65 96 59 89 45 58 412 68.66667
AVG RESULT
>=35 PASS
<35 FAIL



NESTED IF
SR NAME M1 M2 M3 M4 M5 M6 TOTAL AVG GRADE
1002 sanju 70 70 70 70 70 69 419 69.83333
1003 dada 45 89 99 56 65 98 452 75.33333
1004 asmita 89 58 58 89 98 98 490 81.66667
1005 kajal 59 95 59 78 35 65 391 65.16667
1006 sanju 59 78 56 48 89 98 428 71.33333
1007 mangesh 58 48 98 49 52 68 373 62.16667
1008 lina 58 49 58 45 89 68 367 61.16667
1009 dada 58 78 27 78 68 56 365 60.83333
1010 sanju 65 96 59 89 45 58 412 68.66667


IF(K27>89,"A",IF(K27>=80,"B",IF(K27>=70,"C",IF(K27>=60,"D","F"))))
>89 A
>=80 B
>=70 C
>=60 D
<60 E



AND IF
SR NAME M1 M2 M3 M4 M5 M6 TOTAL AVG GRADE RESULT
1002 sanju 70 70 70 70 34 69 383 63.8333
1003 dada 45 89 99 56 65 98 452 75.3333
1004 asmita 89 58 58 89 98 98 490 81.6667
1005 kajal 59 95 59 78 35 65 391 65.1667
1006 sanju 59 78 56 48 89 98 428 71.3333
1007 mangesh 58 48 98 49 52 68 373 62.1667
1008 lina 58 49 58 45 89 68 367 61.1667
1009 dada 58 78 27 78 68 56 365 60.8333
1010 sanju 65 96 59 89 45 58 412 68.6667



IF 
M1 >=35
M2
M3 "PASS"
M4
M5
M6



emp code last name dept region branch hire date salary tax ADDTAX BONUS
1001 raja  SALES NORTH delhi 01/01/2000 10000 1000 1000 500
1002 sanju ADMIN NORTH madras 02/01/2000 65000 0 3250 6500
1003 dada SALES SOUTH mumbai 03/01/2000 78000 0 7800 3900
1004 asmita SALES EAST cal 04/01/2000 91000 0 9100 4550
1005 kajal SALES NORTH cal 05/01/2000 104000 10400 10400 5200
1006 sanju SALES NORTH mumbai 06/01/2000 117000 11700 11700 5850
1007 mangesh SALES NORTH madras 07/01/2000 130000 13000 13000 6500
1008 lina SALES NORTH delhi 08/01/2000 143000 14300 14300 7150
1009 dada SALES NORTH mumbai 09/01/2000 156000 15600 15600 7800
1010 sanju SALES NORTH cal 10/01/2000 169000 16900 16900 8450


AND TAX=10% DEPT =SALES,REGION=NORTH
OR ADD TAX DEPT="SALES","MKT","HR"   10%  OTHER 5%
NOT bonus OTHER THAN SALE =10%

count if , sum if , average if 
Order ID Product Unit Price Quantity Discount
10248 Queso Cabrales 14 12 0
10248 Singaporean Hokkien Fried Mee 9.8 10 0
10248 Mozzarella di Giovanni 34.8 5 0
10249 Tofu 18.6 9 0
10249 Manjimup Dried Apples 42.4 40 0
10250 Jack's New England Clam Chowder 7.7 10 0
10250 Manjimup Dried Apples 42.4 35 0.15
10250 Louisiana Fiery Hot Pepper Sauce 16.8 15 0.15
10251 Gustaf's Knäckebröd 16.8 6 0.05
10251 Ravioli Angelo 15.6 15 0.05




1.     Calculate the total quantity of the product with Order ID=10251
2.     Count the products with Order ID=10250
3.     Count the products with Order ID=10250 and their quantities are greater than 30 and less than 70
4.     Count the products with their unit prices > 40 and their quantities >30
5.     Calculate the average of unit prices of products with Order ID =10250
4.     find  the average   with Order ID=10250
5.     find the sum with Order ID=10250 and their quantities are greater than 30 and less than 70
6.     Count the products with their unit prices > 40 and their quantities >30




APPLY FILTER 

ID # Type Equipment Detail Checked Out Checked In Checked Out By
3000 Camera Saris Lumina Digital Camera 12-May-13 15-May-13 Shannon Nguyen
3005 Camera Saris Zoom Z-60 Digital Camera 27-Jul-13 06-Aug-13 Sela Shepard
1021 Laptop 15" EDI SmartPad L200-3 15-Sep-13 01-Oct-13 Sofie Ragnar
1022 Laptop 15" EDI SmartPad L200-3 14-Aug-13 16-Aug-13 Hank Sorenson
1023 Laptop 15" EDI SmartPad L200-3 08-Aug-13 15-Aug-13 Jennifer Weiss
3070 Camera Omega PixL Digital Camcorder 06-Oct-13 Min Seung
1025 Laptop 15" EDI SmartPad L200-4X 26-Sep-13 04-Oct-13 Min Seung
1031 Laptop 17" Saris X-10 Laptop 04-Oct-13 Nick Ortiz
1032 Laptop 17" Saris X-10 Laptop 19-Sep-13 Stanley Geyer
1033 Laptop 17" Saris X-10 Laptop 24-Sep-13 26-Sep-13 George D'Agosta
1034 Laptop 17" Saris X-10 Laptop 25-Aug-13 27-Aug-13 Jay Peralta
2050 Other EDI SmartBoard L500-1 05-Oct-13 06-Oct-13 Anthony Liddell
2051 Other EDI SmartBoard L500-1 01-Oct-13 05-Oct-13 Sofie Ragnar
3800 Other U-Go Saris DigiCam Printer II 04-Aug-13 05-Aug-13 Hank Sorenson
3900 Other U-Go Saris Label Maker 13-Jun-13 20-Jun-13 Clint Gosse
4800 Other 7N Deluxe Camera Travel Bag 27-Jul-13 06-Aug-13 Sela Shepard
4900 Other 7N Light Rolling Laptop Case 04-Oct-13 Jay Peralta
4905 Other 7N Heavy Rolling Laptop Case 04-Oct-13 Nick Ortiz
6100 Projector Omega VisX 1.0 28-Sep-13 01-Oct-13 Win Armitage
6101 Projector Omega VisX 1.0 26-Sep-13 27-Sep-13 Michael Earley
6102 Projector Omega VisX 1.0 22-Aug-13 23-Aug-13 Jamila Kyle
6200 Projector Saris Lux T-80 01-Sep-13 04-Sep-13 Jolie Chaturvedi
6301 Projector Saris Lux T-81 Lite 10-Sep-13 Marques Herndon
6302 Projector Saris Lux T-81 Lite 08-Sep-13 15-Sep-13 Dean Sorenson
1011 Tablet Saris SlimPro 04-Oct-13 Jay Peralta
1012 Tablet Saris SlimPro 29-Sep-13 August Zorn
5020 TV 32" Paragon 440 OLED TV 11-Aug-13 13-Aug-13 Marta Lao
5022 TV 32" Paragon 440 OLED TV 17-Jul-13 17-Jul-13 Carl Langer
5023 TV 50" Paragon 490L LED TV 01-Oct-13 01-Oct-13 Margaret Lisbon

APPLY FILTER 

  1. Open an existing Excel workbook. If you want, you can use our practice workbook.
  2. Apply a filter to a column. If you are using the example, filter the Type column (column B) so it displays only laptops andcameras.
  3. Add another filter by searching. If you are using the example, search for EDI brand equipment in the Equipment Detailcolumn (column C).
  4. Clear both filters.
  5. Use an advanced text filter to view data that does not contain a certain word or phrase. If you are using the example, display data that does not contain the word saris (this should exclude all Saris brand equipment).
  6. Use an advanced date filter to view data from a certain time period. If you are using the example, display only the equipment that was checked out in September 2013.
  7. Use an advanced number filter to view numbers less than a certain amount. If you are using the example, display all items with an ID# below 3000

SORTING
Homeroom #
First Name Last Name T-Shirt Size Payment Method
220-B Malik Reynolds Small Cash
105 Melissa White Small Debit Card
220-A Juan Flores X-Large Pending
220-B Karla Nichols X-Large Money Order
105 Christiana Chen Medium Cash
105 Sidney Kelly Medium Check Bounced
220-B Windy Shaw Small Cash
105 Esther Yaron Small Check
220-B Tyrese Hanlon X-Large Debit Card
135 Lia Richards X-Large Cash
110 Kris Ackerman Large Money Order
135 Jordan Weller Large Money Order
135 Alex Yuen Large Wrong Amout
105 Nathan Albee Medium Check
110 Gabriel Del Toro Medium Cash
220-B Avery Kelly Medium Debit Card
135 James Panarello Medium Check
135 Chantal Weller Medium Cash
220-A Chevonne Means Medium Money Order
220-A Brigid Ellison Small Cash
105 Derek MacDonald Large Cash
110 Matt Benson Medium Money Order
220-B Michael Lazar Small Cash
135 Anisa Naser Small Pending
110 Regina Olivera Large Pending
220-B Samantha Bell Medium Check
220-A Christopher Peyton-Gomez Small Check Bounced

  1. Open an existing Excel workbook. If you want, you can use our practice workbook.
  2. Sort a worksheet in ascending sort ascending or descending sort descending order. If you are using the example, sort by Homeroom # (column A).
  3. Sort a cell range. If you are using the example, sort the cell range in the cell range G3:H7 from highest to lowest by Orders (column H).
  4. Add a level to the sort, and sort it by cell color, font color, or cell icon. If you are using the example, add a second level to sort by cell color in column E.
  5. Add another level, and sort it using a custom list. If you are using the example, create a custom list to sort byT-Shirt Size (column D) in the order of Small, Medium, Large, and X-Large.
  6. Change the sorting priority. If you are using the example, reorder the list to sort by T-Shirt Size (column D),Homeroom # (column A), and Last Name (column C).

GROUP
Homeroom #
First Name Last Name T-Shirt Size Payment Date
220-B Malik Reynolds Small 7-Oct
105 Melissa White Small 14-Oct
220-B Windy Shaw Small Pending
105 Esther Yaron Small 7-Oct
220-A Brigid Ellison Small 7-Oct
220-B Michael Lazar Small 7-Oct
135 Anisa Naser Small Pending
220-A Christopher Peyton-Gomez Small 14-Oct
105 Christiana Chen Medium 5-Oct
105 Sidney Kelly Medium 11-Oct
105 Nathan Albee Medium 5-Oct
110 Gabriel Del Toro Medium 11-Oct
220-B Avery Kelly Medium 13-Oct
135 James Panarello Medium 11-Oct
135 Chantal Weller Medium 11-Oct
220-A Chevonne Means Medium 13-Oct
110 Matt Benson Medium 15-Oct
220-B Samantha Bell Medium 15-Oct
110 Kris Ackerman Large Pending
135 Jordan Weller Large 1-Oct
135 Alex Yuen Large 5-Oct
105 Derek MacDonald Large 1-Oct
110 Regina Olivera Large Pending
220-A Juan Flores X-Large Pending
220-B Karla Nichols X-Large 6-Oct
220-B Tyrese Hanlon X-Large 4-Oct
135 Lia Richards X-Large 6-Oct



GROUP

  1. Open an existing Excel workbook. If you want, you can use our practice workbook.
  2. Try grouping a range of rows or columns together. If you are using the example, group columns D and E.
  3. Use the Show and Hide Detail buttons to hide and unhide the group.
  4. Try ungrouping the group. If you are using the example, ungroup columns D and E.
  5. Outline your worksheet using the Subtotal command. If you are using the example, outline by T-shirt size.
  6. Remove subtotaling from your worksheet.

  1. Format a range of cells as a table. If you are using the example, format the cell range A2:E13.
  2. Add a row or column to the table.
  3. Choose a new table style.
  4. Change the table style options. If you are using the example, add a total row.
  5. Remove the table.
 

TABLE






Menu Item Price Quantity Sales Tax Total
Empanadas: Beef Picadillo 2.99 15 $3.36 $48.21
Empanadas: Chipotle Shrimp 3.99 10 $2.99 $42.89
Empanadas:  Black Bean & Plantain 2.49 20 $3.74 $53.54
Tamales: Chicken Tinga 2.29 20 $3.44 $49.24
Tamales: Vegetable 2.29 30 $5.15 $73.85
Arepas: Carnitas 2.89 10 $2.17 $31.07
Arepas: Queso Blanco 2.49 20 $3.74 $53.54
Empanadas: Apple Cinnamon 3.19 40 $9.57 $137.17
Beverages: Horchata 1.89 25 $3.54 $50.79
Beverages: Lemonade 1.89 35 $4.96 $71.11
Beverages: Tamarindo 1.89 10 $1.42 $20.32


  1. Format a range of cells as a table. If you are using the example, format the cell range A2:E13.
  2. Add a row or column to the table.
  3. Choose a new table style.
  4. Change the table style options. If you are using the example, add a total row.
  5. Remove the table.


CHARTING

Genre 2008 2009 2010 2011 2012
Classics $18,580 $49,225 $16,326 $10,017 $26,134
Mystery $78,970 $82,262 $48,640 $49,985 $73,428
Romance $24,236 $131,390 $79,022 $71,009 $81,474
Sci-Fi & Fantasy $16,730 $19,730 $12,109 $11,355 $17,686
Young Adult $35,358 $42,685 $20,893 $16,065 $21,388
  1. Use worksheet data to create a chart. If you are using the example, use the cell range A1:F6 as the source data for the chart.
  2. Change the chart layout. If you are using the example, select Layout 8.
  3. Apply a chart style.
  4. Move the chart. If you are using the example, move the chart to 
  5. a new worksheet named Book Sales Data 2008-2012.  
http://www.gcflearnfree.org/excel2013/23
     


     







    Posted by at No comments:
    Subscribe to: Comments (Atom)