- #DATA ANALYSIS EXCEL 2011 MAC ADD ON FOR MAC#
- #DATA ANALYSIS EXCEL 2011 MAC ADD ON CODE#
- #DATA ANALYSIS EXCEL 2011 MAC ADD ON WINDOWS#
Which function can we use to change all of the instances of one character from a text string into a different character? (Type only the function name) SUBSTITUTEĥ. Our next plan is to get rid of the non-breaking spaces between the dates and the sales amounts by replacing those characters with regular spaces. How many records have the character 32 as the final character? 496Ĥ.
#DATA ANALYSIS EXCEL 2011 MAC ADD ON CODE#
In column E, write a formula just like column C that returns the ASCII code of the final character from each record in column D. Now we want to verify that the CLEAN function did, in fact, remove all of the unwanted final characters. To check if this has been done correctly, enter the value of the Question 2 checksum from cell D2.Įnter just the 5 digits (6 on a Mac), without any commas or other thousands separator. In column D, write a formula that applies the CLEAN function to the column B data.
Next, we want to remove some of these final characters with the CLEAN function.
How many records have the character 30 as the final character? (Hint: After writing your formula in column C, you can write a formula in cell C3 that uses the COUNTIFS function to count how many times the value in column C is 30.) 126Ģ. In column C (cells C5:C500) write a formula using the RIGHT and CODE functions to return the ASCII code of the final character in each record.
To begin, we want to verify what special characters might be at the end of each data entry. When we are done we will separate the data into 4 components on the Clean worksheet tab, and perform some basic analysis. All sales amounts are below $10000Some space charactersA final random character that has an ASCII value between 28 and 31.We will clean up the data one item at a time, by using formulas to make copies of the data to the right of column B, with each copy being slightly cleaner than the previous. All sales are from October 2017Some non-breaking space charactersThe amount of sales, with the prefix $. Some / charactersThe product type sold, with the prefix PR:Some “_” charactersThe Sales Person’s name, with the prefix SP:The date of the sales, in the format yyyymmd. This may look daunting, but we are told by the Data Manager that the raw data always follows a certain pattern. Our goal is to populate the columns of the Clean worksheet with the data in a form that we can easily analyse. Do not insert any rows or columns into the Data sheet during your work or the Check Sums may give different results than programmed.
#DATA ANALYSIS EXCEL 2011 MAC ADD ON FOR MAC#
Make sure the Check Sum value in Data cell B2 is 95858 (90898 on Excel for Mac 2011). Look at the Data worksheet and Clean worksheet.
#DATA ANALYSIS EXCEL 2011 MAC ADD ON WINDOWS#
If you use Excel for Windows 2010 or 2007, use this version: If you use Excel for Windows Office 365, 2016 or 2013, or Excel for Mac Office 365, 2016 or 2011, use the Unicode version: There are 2 versions of the workbook, depending on which version of Excel you are using. Follow the instructions carefully and answer the questions as you go.ĭownload and open the assignment workbook. Since your company will need to perform this analysis each month with new data, you are to use formulas to clean the data so that the workbook can be easily updated each month with new records from the database. The data is in a dirty format and you are required to clean it up in order to perform some analysis on it. You have received some sales data from your company’s database. Week 3 Final Assignment > Excel Skills for Business: Advancedġ.
0 kommentar(er)
