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  or 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