Name
Grades
7.40E+01
1. Teresa A. Murray
2. Beulah B. 6.50E+01
Terry
3. Bradley C.6.10E+01
Hoffman
4. Gerardo D.6.10E+01
Garrett
6.10E+01
5. Hugo E. Barber
5.90E+01
6. Lee F. Sanders
6.30E+01
7. Ben G. Davidson
8. Martin H. 6.90E+01
Stokes
7.10E+01
9. Frank I. Cannon
7.50E+01
10. Tara J. Mcgee
11. Lynne K. 8.30E+01
Griffin
12. Willard L.6.60E+01
King
13. Gloria M.5.70E+01
Adkins
14. Gerard N.8.60E+01
Aguilar
6.90E+01
15. Ernesto O.
Dean
7.20E+01
16. Sheldon P.
Frazier
8.40E+01
17. Virgil Q. Ramos
18. Roland R.1.01E+02
Paul
7.10E+01
19. Jane S. Love
6.70E+01
20. Rachael T.
Patterson
21. Russell U.0.00E+00
White
4.60E+01
22. Luke V. Moreno
1.00E+02
23. Pauline W.
Lamb
24. Florence-2.00E+00
X. Reed
25. Sandy Y. 7.30E+01
Cunningham
Quick Cleanup - Suppose you are making a grade summary, would like sorted by last name
0) Change Grades to numeric
1) Insert additional columns
2) Select with Ctrl Shift down
3) Text to Columns
4) Delete extra
5) Turn to table, resize, sort by last name
Item
Cost
Category
1)PARKING,6.44,Government Services
10)FRYS-MKTPLACE,4.67,Supermarkets
11)SCHLOTZSKY'S,4.79,Restaurants
12)IHOP,20.7,Restaurants
13)CELL AND DATA PLAN,67.26,Services
14)ACE HARDWARE,1.56,Home Improvement
15)FRYS-MKTPLACE,27.43,Supermarkets
16)UTILITIES ,63.39,Services
17)FRYS,4.62,Supermarkets
18)PASSAGE TO INDIA,27.57,Restaurants
19)ALBERTSONS ,8.09,Supermarkets
2)LOWE'S,12.85,Home Improvement
20)WHOLEFDS,4.07,Supermarkets
21)LENOVO,182.39,Merchandise
22)WILDFLOWER BREAD,15.08,Restaurants
23)ALBERTSONS ,35.71,Supermarkets
24)WALGREENS,8.95,Merchandise
25)ZALES,257.7,Merchandise
26)BOULDERS,5.72,Restaurants
27)WHOLEFDS,14.28,Supermarkets
28)SEARS AUTO CNTR,34.24,Automotive
29)FRYS-MKTPLACE,4.66,Supermarkets
3)JASON'S DELI,15.97,Restaurants
4)WHOLEFDS,4.16,Supermarkets
5)QT,36.46,Gasoline
6)WHOLEFDS,26.26,Supermarkets
7)APPLEBEES,27.94,Restaurants
8)DSW ,50.51,Merchandise
9)CHOMPIES RESTAURANT ,9.94,Restaurants
Quick Cleanup - Suppose you are making a budget summary, would like sorted by cost
1) Insert additional columns
2) Select with Ctrl Shift down
3) Text to Columns
4) Delete extra
5) Turn to table, resize, sort by cost
(Later we'll see how to automatically group by category, using pivot tables!)
Name
Subject
State
1. Teresa Murray
2. Beulah Terry
3. Bradley Hoffman
4. Gerardo Garrett
5. Hugo Barber
6. Lee Sanders
7. Ben Davidson
8. Martin Stokes
9. Frank Cannon
10. Tara Mcgee
11. Lynne Griffin
12. Willard King
13. Gloria Adkins
14. Gerard Aguilar
15. Ernesto Dean
16. Sheldon Frazier
17. Virgil Ramos
18. Roland Paul
19. Jane Love
20. Rachael Patterson
21. Russell White
22. Luke Moreno
23. Pauline Lamb
24. Florence Reed
25. Sandy Cunningham
Grades
7.40E+01
6.50E+01
6.10E+01
6.10E+01
6.10E+01
5.90E+01
6.30E+01
6.90E+01
7.10E+01
7.50E+01
8.30E+01
6.60E+01
5.70E+01
8.60E+01
6.90E+01
7.20E+01
8.40E+01
1.01E+02
7.10E+01
6.70E+01
0.00E+00
4.60E+01
1.00E+02
-2.00E+00
7.30E+01
State Cd
State
California
Texas
New York
Florida
Illinois
Pennsylvania
Ohio
Michigan
Georgia
North Carolina
New Jersey
Virginia
Washington
Arizona
Massachusetts
Indiana
Tennessee
Missouri
Maryland
Wisconsin
Minnesota
Colorado
Alabama
South Carolina
Louisiana
Kentucky
Oregon
Oklahoma
Connecticut
Iowa
Mississippi
Arkansas
Kansas
Utah
Nevada
New Mexico
West Virginia
Nebraska
Idaho
New Hampshire
Maine
Hawaii
Rhode Island
Montana
Delaware
Subjects
MTH/208
MTH/209
POS/400
POS/410
PRG/420
South Dakota
Alaska
North Dakota
Vermont
District of Columbia
Wyoming
0)
1)
2)
3)
4)
Data Validation
Cleanup as before, then use format painter
Drop down for Subject using List
Drop down for State by defining State
Grades:
State:
http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010342173.aspx
Data Validation
Cleanup as before, then use format painter
Drop down for Subject using List
Drop down for State by defining State
Make sure only values between 0 and 100 are allowed otherwise give an error message
Must be 2 character length and TEXT
http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010342173.aspx
Item
Type
Pot
Math Book
Love
Chair
Shirt
Pants
Cat
Location
Date
Amount
Category
Clothing
Electronic
Furniture
Food
Monetary
Location
PositiveRemnants
Donation Place
Hopeful Militia
Places for People
0)
1)
2)
3)
4)
5)
Data Validation Practice
Cleanup as before, then use format painter
Drop down for Category using List
Drop down for Location by defining named list
Amount:
Date : Only allow dates for current year.
Add items and categories and locations
http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010342173.aspx
Data Validation Practice
Cleanup as before, then use format painter
Drop down for Category using List
Drop down for Location by defining named list
Make sure only values between 0 and 100 are allowed; otherwise, give an error message
Date : Only allow dates for current year.
Add items and categories and locations
http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010342173.aspx
Bonus Hurdle
Bonus Amount If Hurdle
Equaled or Passed
$20,000
$750
Logical Formula
or
logical_test
Employee
Rod Barbaria
Elinore Cromedy
Twanda Spruce
Darius Raffety
Frida Vickerson
Sales
$20,382
$19,504
$21,579
$12,117
$26,081
Bonus:
TRUE or FALSE
IF used to
IF used to put 1 of
put 1 of 2
2 numbers in cell
words in cell
Bonus:
"Yes" or
"No"
Bonus Amount
Code for IF
IF ( Something true or false, ValueIfTrue, ValueIfFalse)
Click on this link to see more about the IF function:
Excel Magic Trick 452: IF Function Formula 16 Examples
Due Date
LatePenalty
4/10/2014
35%
Logical Formula
or
logical_test
Employee Name
Rod Barbaria
Elinore Cromedy
Twanda Spruce
Darius Raffety
Frida Vickerson
Full
Semiannual
Pay
$20,382
$19,504
$21,579
$12,117
$26,081
Date
Completed
5/10/2014
3/23/2014
3/24/2015
3/25/2014
3/26/2014
Click on this link to see more about the IF function:
Excel Magic Trick 452: IF Function Formula 16 Examples
Late:
TRUE or FALSE
IF used to
put 1 of 2
words in cell
Late:
"Yes" or
"No"
IF used to put 1 of
2 numbers in cell
Code for IF
Final Pay
IF ( Something true or false, ValueIfTrue, ValueIfFalse)
Imagine you have a collection of employees, each of who has to complete a project by the due date to earn
If they are late, the LatePenalty is applied and they only earn what is left.
1) Name the Due Date and Late Penalty cells
2) Fill in the green cells with appropriate formulas
BONUS - If you finish early, try having the Date Completed turn red if it is after the Due Date.
Use a formula with Conditional Formatting.
ct by the due date to earn full pay.
IF function
Delivers 1 of 2 things to a cell or formula
You can deliver: Words, Numbers, or Formulas to a cell or formula
Deliver 1 of 2 Numbers to cell
Sales
Sales Hurdle
Bonus if Hurdle met
Bonus if Hurdle not met
Amount after Bonus
4,800.00
5,000.00
100.00
-
Deliver 1 of 2 possible Text items to the cell
Sales
Expenses
4,800
3,500
1,300.00
Deliver 1 of 2 possible Formulas to cell
Raw Score
Late Penalty
Late Penalty Amount
Final Price
80%
FALSE
10%
More than one IF (a.k.a. nested IF)
Sales
Expenses
Net
$
$
$
500.00
(500.00)
Multiple IF
Name
Job Application score
HR Decision
Jana Casey
Job Application score
>= 5
3 to 4
Purchase answer to see full
attachment