basic excel functions
Hurmat1
Hhameed_Module01_07102023.xlsxStart here
| My Practice Data | |||||||||||||||||||
| Hurmat hameed | |||||||||||||||||||
| Instructions: | |||||||||||||||||||
| Location | January | February | March | Total Sales | Average Sales | Total Percentage | 1 | Merge and center the titles over the data columns | |||||||||||
| North | 100 | 75 | 250 | $ 425 | 141.67 | 23% | 2 | Format the titles as 14 pt bold | |||||||||||
| South | 200 | 80 | 245 | $ 525 | 175.00 | 29% | 3 | Row 4 data should be 12 pt and a different color | |||||||||||
| East | 300 | 70 | 145 | $ 515 | 171.67 | 28% | 4 | Calculate Total Sales | |||||||||||
| West | 200 | 55 | 90 | $ 345 | 115.00 | 19% | 5 | Calculate Average Sales | |||||||||||
| Grand Total | $ 1,810 | 603.33 | 6 | Calculate the grand total | |||||||||||||||
| 7 | Calculate percent of total (total sales divided by grand total; be sure to use absolute addressing) | Absolute addressing was not used (-.5) $E$9 | |||||||||||||||||
| 8 | From the Number panel, format column E as Currency, no decimal places | You have it set at Accounting and not Currency (-1) | |||||||||||||||||
| 9 | Format Column F with two decimal places | ||||||||||||||||||
| 10 | Format column G as Percentage, no decimal places | ||||||||||||||||||
| 11 | Create a bar or column chart from the uncalculated columns | ||||||||||||||||||
| 12 | Move the chart to a new sheet entitled my chart | ||||||||||||||||||
| 13 | Add a relevant clipart (online picture) | ||||||||||||||||||
| 14 | Move the clip art under the data | ||||||||||||||||||
My chart
Different location sales
January North South East West 100 200 300 200 February North South East West 75 80 70 55 March North South East West 250 245 145 90
Calculations
| Calculation | Data | ||||
| 5 | Instructions: | ||||
| 10 | Calculate the total, minimum, maximum, and average using Excel functions | ||||
| 15 | |||||
| 20 | |||||
| Total | 50 | ||||
| Minimum | 5 | ||||
| Maximum | 20 | ||||
| Average | 12.5 |
Functions
| Categories | My Data | |||
| Car loan | $ 15,000 | Instructions: | ||
| Interest Rate | 4.50% | Using the PMT function and cell addresses (where possible), | ||
| Loan | 60 | months | calculate the monthly payment for the car loan | |
| Monthly Payment | ($67,500.00) | |||
| Incorrect. | ||||
| Take a look at the class recording as I walk you through this. | ||||
| You want to use cell references. | ||||
| Make sure to divide your cell B4/12 | ||||
| -1 | ||||
Sort
| Topic | Sub Topic | ||
| Aging and Death | Sociology of Aging | Instructions: | |
| Aging and Death | Death and Dying | Sort the data on Topic and, within Topic, sort on Sub Topic | |
| Aging and Death | Sociology of Aging | ||
| Aging and Death | Sociology of Aging | Topic is sorted but then the sub topic is not sorted | |
| Aging and Death | Death and Dying | -1 | |
| Aging and Death | Death and Dying | ||
| Aging and Death | Sociology of Aging | ||
| Aging and Death | Sociology of Aging | ||
| Criminal Justice | Deviance | ||
| Criminal Justice | Juvenile Delinquency | ||
| Criminal Justice | Deviance | ||
| Criminal Justice | Deviance | ||
| Criminal Justice | Juvenile Delinquency | ||
| Criminal Justice | Juvenile Delinquency | ||
| Introduction | Conflict Approach | ||
| Introduction | Conflict Approach | ||
| Introduction | General | ||
| Introduction | General | ||
| Introduction | Macrosociology Approach | ||
| Introduction | Social Sciences | ||
| Introduction | Macrosociology Approach | ||
| Introduction | Social Sciences | ||
| Introduction | Social Sciences | ||
| Introduction | General | ||
| Introduction | General | ||
| Marriage and Family | Marriage and Marriage and Family | ||
| Marriage and Family | Marriage and Marriage and Family | ||
| Marriage and Family | Marriage and Family Interaction | ||
| Marriage and Family | Marriage and Family Interaction | ||
| Marriage and Family | Marriage and Marriage and Family | ||
| Marriage and Family | Marriage and Marriage and Family | ||
| Race/Class/Gender | Race/Ethnicity | ||
| Race/Class/Gender | Human Sexuality | ||
| Race/Class/Gender | Race/Ethnicity | ||
| Race/Class/Gender | Race/Ethnicity | ||
| Race/Class/Gender | Gender Issues | ||
| Race/Class/Gender | Gender Issues | ||
| Race/Class/Gender | Human Sexuality | ||
| Race/Class/Gender | Gender Issues | ||
| Race/Class/Gender | Human Sexuality | ||
| Race/Class/Gender | Human Sexuality | ||
| Race/Class/Gender | Gender Issues | ||
| Race/Class/Gender | Gender Issues | ||
| Research/Statistics | Data Analysis | ||
| Research/Statistics | Social Statistics | ||
| Research/Statistics | Data Analysis | ||
| Research/Statistics | Research Methods | ||
| Research/Statistics | Social Statistics | ||
| Research/Statistics | Social Statistics | ||
| Research/Statistics | Research Methods | ||
| Research/Statistics | Research Methods | ||
| Research/Statistics | Research Methods | ||
| Research/Statistics | Social Statistics | ||
| Research/Statistics | Data Analysis | ||
| Research/Statistics | Social Statistics | ||
| Research/Statistics | Social Statistics |
Tables
| Note: instructions for this tab start on row 49 | ||||||
| Order Date | Region | Rep | Item | Units | Unit Cost | Total |
| 1/6/18 | East | Jones | Pencil | 95 | 1.99 | $ 189.05 |
| 1/23/18 | Central | Kivell | Binder | 50 | 19.99 | $ 999.50 |
| 2/9/18 | Central | Jardine | Pencil | 36 | 4.99 | $ 179.64 |
| 2/26/18 | Central | Gill | Pen | 27 | 19.99 | $ 539.73 |
| 3/15/18 | West | Sorvino | Pencil | 56 | 2.99 | $ 167.44 |
| 4/1/18 | East | Jones | Binder | 60 | 4.99 | $ 299.40 |
| 4/18/18 | Central | Andrews | Pencil | 75 | 1.99 | $ 149.25 |
| 5/5/18 | Central | Jardine | Pencil | 90 | 4.99 | $ 449.10 |
| 5/22/18 | West | Thompson | Pencil | 32 | 1.99 | $ 63.68 |
| 6/8/18 | East | Jones | Binder | 60 | 8.99 | $ 539.40 |
| 6/25/18 | Central | Morgan | Pencil | 90 | 4.99 | $ 449.10 |
| 7/12/18 | East | Howard | Binder | 29 | 1.99 | $ 57.71 |
| 7/29/18 | East | Parent | Binder | 81 | 19.99 | $ 1,619.19 |
| 8/15/18 | East | Jones | Pencil | 35 | 4.99 | $ 174.65 |
| 9/1/18 | Central | Smith | Desk | 2 | 125.00 | $ 250.00 |
| 9/18/18 | East | Jones | Pen Set | 16 | 15.99 | $ 255.84 |
| 10/5/18 | Central | Morgan | Binder | 28 | 8.99 | $ 251.72 |
| 10/22/18 | East | Jones | Pen | 64 | 8.99 | $ 575.36 |
| 11/8/18 | East | Parent | Pen | 15 | 19.99 | $ 299.85 |
| 11/25/18 | Central | Kivell | Pen Set | 96 | 4.99 | $ 479.04 |
| 12/12/18 | Central | Smith | Pencil | 67 | 1.29 | $ 86.43 |
| 12/29/18 | East | Parent | Pen Set | 74 | 15.99 | $ 1,183.26 |
| 1/15/19 | Central | Gill | Binder | 46 | 8.99 | $ 413.54 |
| 2/1/19 | Central | Smith | Binder | 87 | 15.00 | $ 1,305.00 |
| 2/18/19 | East | Jones | Binder | 4 | 4.99 | $ 19.96 |
| 3/7/19 | West | Sorvino | Binder | 7 | 19.99 | $ 139.93 |
| 3/24/19 | Central | Jardine | Pen Set | 50 | 4.99 | $ 249.50 |
| 4/10/19 | Central | Andrews | Pencil | 66 | 1.99 | $ 131.34 |
| 4/27/19 | East | Howard | Pen | 96 | 4.99 | $ 479.04 |
| 5/14/19 | Central | Gill | Pencil | 53 | 1.29 | $ 68.37 |
| 5/31/19 | Central | Gill | Binder | 80 | 8.99 | $ 719.20 |
| 6/17/19 | Central | Kivell | Desk | 5 | 125.00 | $ 625.00 |
| 7/4/19 | East | Jones | Pen Set | 62 | 4.99 | $ 309.38 |
| 7/21/19 | Central | Morgan | Pen Set | 55 | 12.49 | $ 686.95 |
| 8/7/19 | Central | Kivell | Pen Set | 42 | 23.95 | $ 1,005.90 |
| 8/24/19 | West | Sorvino | Desk | 3 | 275.00 | $ 825.00 |
| 9/10/19 | Central | Gill | Pencil | 7 | 1.29 | $ 9.03 |
| 9/27/19 | West | Sorvino | Pen | 76 | 1.99 | $ 151.24 |
| 10/14/19 | West | Thompson | Binder | 57 | 19.99 | $ 1,139.43 |
| 10/31/19 | Central | Andrews | Pencil | 14 | 1.29 | $ 18.06 |
| 11/17/19 | Central | Jardine | Binder | 11 | 4.99 | $ 54.89 |
| 12/4/19 | Central | Jardine | Binder | 94 | 19.99 | $ 1,879.06 |
| 12/21/19 | Central | Andrews | Binder | 28 | 4.99 | $ 139.72 |
| Total | $ 19,627.88 | |||||
| Instructions: | ||||||
| 1 | Create a table from the data | Data is not in an Excel Table (-1) | ||||
| 2 | Add a total row | Total ROW was not added (-1), you have a column | ||||
| 3 | Widen column G so that all the total numbers are visible | |||||
| 4 | Filter the data to see how times Jones ordered binders | |||||
| 5 | Format the total as Currency | You have it formatted as Accounting and not Currency (-1) |
