BUS 150 WEEKLY NOTE
BUS 150 WEEKLY NOTE MAS 211
Popular in INTRODUCTION TO BUSINESS STATISTICS
Popular in Business
verified elite notetaker
Test Prep (MCAT, SAT...)
verified elite notetaker
One Day of Notes
verified elite notetaker
verified elite notetaker
One Day of Notes
verified elite notetaker
verified elite notetaker
This 14 page Class Notes was uploaded by YALIN ZHANG on Friday March 20, 2015. The Class Notes belongs to MAS 211 at University of Miami taught by Mary McKenry in Winter2015. Since its upload, it has received 182 views. For similar materials see INTRODUCTION TO BUSINESS STATISTICS in Business at University of Miami.
Reviews for BUS 150 WEEKLY NOTE
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 03/20/15
BUS 150 R Professor Barber 22415 02242015 Data Management Data vs Information Data numbers text result of calculation dates and time Row fact Information is data rearrange processed Data input Information output Base on information we made decision 3 techniques to produce information from Data Sorting Sorting ll Filtering ll Calculating H In alphabetical ljljljlj numerical or chronological lljlljlljlljlj order When you sort can put things in ascending or descending order Ascending starts with smallest to largest at bottom Descending largest at the top smallest at bottom When you have textaphabetica text goes in ascending order Numbers put in either order Dates in either 0 Dates in ascending order is the longest to go smallest number at top 0 Dates in descending order is the most recent at the top Filter selecting Base on the criteria can have many criteria as you like 0 Ex only shows female Calculating Formulas and functions Table o Is a planned organized where you store your data in rows and columns 0 Simple table after you create a simple table turn into excel table you go to insert tab and insert table 0 Store data on a single entity type if you need to store data 0 Table stores data on a speci c occurrence of the entity 0 Ex Erica slater is an entity 0 Record speci c occurrence 0 Field are the characters or attribute of the entity 0 Record lay out are the same for every record Planning 0 Start with output 0 What report do I need Make a list of output 0 What question customer ask me List facts 0 gure input Constants and calculation formulas for the calculations Keep 3 things in mind 0 Data must store in smallest useful unit Ex we don t put number in the same column put last name and rst name in separate cmumn We store data in the form of the least amount of make Ex store birthday instead of age so you don t have to update every year 0 Store number as text if you don t need for calculate Ex phone numbers zip code put 0 before the number make it text before enter the data select column go to format change to text format Unique vs Non Unique 0 Unique one of the kind cannot be duplicated o If a field is unique they can not be duplicated within that led o If can be duplicated that is non Unique 0 Ex Student ID 0 You should have one eld is unique for one table It is the best to have one table for one sheet Field names 0 First row in the table 0 Descript of th data of the column 0 Must be unique 0 Can be letter text numbers special characters 0 Should be formatted differently than the data recordunderine bold Row containing led names is called the header row 0 Subsequent rows are the data records 0 Every record to go separate row and every record should be unique 0 No complete blank rows or columns 0 You can have blank cell not entire rows or column 0 Do not start with blank cell and no blank at the end 0 Data need to be consistent in its formatting through out the table 0 You must have one blank row above the header row 0 You must have one blank row beneath the last row 0 You need a blank column of left and right of the table 0 You don t need a blank column is left is begins at Column A same as rows View 0 Freeze Panes must select the correct cell 1 when you freeze panes go to cell it will freeze above and left of the column and rows Just for viewing the spread sheet 2 You can unfreeze 3 Its for big spread sheet Simple Table vs Excel Table 0 Simple and Excel both can lter and sort 0 Excel table contains rows name and column names for formulas Sequencing of data 0 Select one cell of the column for last name 0 According to the column sort or lter 0 Only need to select one single cell of the column and you can sort and lter 0 Don t select the entire column or row to sort and lter 22616 Primary Secondary Treasury Major Name Alphabetic order Ascending Sort two or three No more than three usually Financial Aid A to Z GPA descending order Custom Sort 1 custom sort custom list 2 Ex Geographic order North Central South From keyboard Do treasury Secondary Primary from least important to most important From dialog Do Primary Secondary Treasury from most important to Least important Filter 1 Filter icon do not print 2 The blue number 3 Ex The 13 of 33 records founded 1 Ex FL or GA from one or other One person cannot from FL and GA Understand the language In the same le you can do or 2 Different le when have condition with two different le the command will be AND Ex People from FL who are Male 3 You can clear the single lter or clear all the le 4 Number Filters Greater than equal to a Dates Year Month Week You can lter dates b Text Begins with equal to c Number between with and Use the AND 5 You need blank row above the table at least one row And then you need a blank row on the bottom 6 We don t put some beside the table Because when you lter or hide the thing will hide or disappear 7 Put the Assumption above or below the table 8 Date Descending order 9 As well as means or 10 Subtotal done quickly give you quick information Sum min max average countno different between count and counta Subtotal for groups a sequence the major rst sequence by the group b What s the group c For the group you can do sum or min or count count anything 11 Level 1 grand total a Level 2 subtotal b Level 3 everything 12 Average for GPA You can remove for the subtotal Pivot Table a best use for very large table b useful for the business c to summaries the table d Allows to change the data very quickly in order to make decision e Drawback when you want to change the data in the main table the pivot table does not automatically update You must update it you must refresh it manually Create Pivot table 1 New worksheet or exist worksheet 2 If you check the text le go to the rows area 3 If you check the date le go the rows area 4 If you check the numerical go to the value area and it will be sum 5 Drag the le out or Drag where you want them to govalue columns rows and it will be sum 6 Filter area will be above the pivot table 7 Drag a text le to value area you will get count 8 Drag a numerical le to value you will get sum 9 When you have multiple value le its easier to read if the le is In row area 10 You will not get format number automatically you need to format by manually Do it from the HOME TAB 11 You can refresh the data manually 12 If you pivot the table once you change the table you have to change the label by yourself Label will not switch as you change the le 13 You can group data Ex You can group the dates to Months 14 You don t have to have label if you don t want them 15 You can have multiple pivot table in the same spreadsheet 16 If you are doing the interval for dollar you have to type the dollar sign manually 17 Filed setting show the item with no data 18 We need to show 0 Pivot option for empty cell shows 0 19 Doubleclick the data you can see the detail goes along with the table 3315 mFP P FWN you can bring the same le twice or you can bring the different le to the value area When you drug a text and dates value area will count them When you drug numerical value area will sum When you have multiple value les drug one value le to the rows You can lter either column and rows Pivot table needs to be format type different title When you change the title when you pivot the table you need to x them by yourself 9 Group when the amount or values are too much you can create the group Ex set up interval ranges 0999 1000099999 10 11 12 13 14 15 1 After grouping you need to format by typing type sign Analysis Fied Setting Show 0 for empty cell Create the customer sort before the pivot table You can move the pivot table order just select and drug Select your pivot table select a cell type the title in When you group it if its over lab you need to x it by yourself type 999 instead of 1000 x the upper boundary lnsert Slicer When you multiple sections Makes your lter very clear when you have multiple lters Also slicer allow you to le if the le is not in lters you can connect the slicer to the multiple table Report connections pomI I 1 if you select the pivot table only print the pivot table If you want to print the slicer you need to select the slicer and the pivot table Pivot chart 1 you need a pivot table to create the pivot chart 2 The pivot chart xaxis will set the rst column for categorical labels 3 If you want to change it you can SWITCH ROWCOLUMN When you switch it the pivot table and pivot chart are connect table will change 4 When you change chart table change change table chart change it They are connected 5 You can format the chart Formulas Error checking 1 IF formula Advanced Filter 1 you can have only the le you need 2 allow you to copy only the sheet you permit easy to print easy to interpret you can have multiple report side by side can remind on the spread sheet 5 Need more works at front must build criteria range before the advance lter IgtUU Steps for Advanced Filter 1 Copy the le name copy to the criteria range copy and paste 2 Specify criteria 3 Advanced will include the list range e name and rest data then you select the criteria range le name and criteria row Do NOT TAKE ANY BLANK ROW 4 Any thing begin with the criteria will match Ex Smith will get Smith and Smithson 5 Or you can do quotSmithquot means you must match Smith 6 You can have multiple criteria Ex Victorian or Ranch 7 When the criteria on the same row it means quotandquot It must meet both conditions Ex Victorian or gt1500 8 When the criteria on the separate row it means or 9 If you have quot quot equal blank you will get all the record which there is no entry 10 If you have ltgt not equal blank you will get all the record which there is entry 11 If you haveltgtCondon not equal to condo you will get all the records without Condo 12 If you want include both date sold you need to add on more DATE SOLD on the criteria le name then you will get quotandquot Ex everything sold between 312014 and 3312014 Drawback for Advanced lter 1 You have to clear every time when you want another advanced lter Solution 1 you need to gure what report you need for make the advanced lter 2 Just copy the le name which you need for each reports 3 Set up different criteria name that you need for your reports 4 So You can create separate criteria range 5 You can add criteria ranges whenever you want 6 Criteria range can be at the same sheet or different work sheet When the report is too big set up a separate criteria list report 1 Only copy paste the le name you need for you reports 2 Use the COPY TO another new location 3 IF the data changes it does not update Copy to Ranges 1 When you create a COPY TO RANGES The table has to be on next to the other cannot be below or above Side by side 2 The copy to range must be the same work sheet with the data table 3 Copy to a new location 4 you can have multiple reports at the same spread sheet 1 Criteria ranges can be anywhere 2 Copy to ranges must be on the same sheet 3 The copy to ranges have to be side by side Have to be next to each other 4 If you change the data In the main table does not update the copy to range You have to rerun the report 31915 0 De ned Names its easier to create formulas and function Formula is easier to understand when you use de ned name Faster less error simply Easier to remember Names will follow the cells and the formulas If you insert or delect the range the name will follow the cell w erever they go 7 The de ned name must start bash act or letter underscore 8 Only contain a letter a underscore and period symbol 9 The best way to use capital letter for the beginning of the word no period no underscore Ex HourlyWage O P FP N 10 Excel always convey to HourlyWage 11 If you use combination letter and number stay way of the cell address name Ex ABlOO stay way 12 Under the formula command gt De ne name 13 Scope can be workbook or worksheet if the name is workbook the name has to be unique of the entire workbook Worksheet the name has to be unique the entire worksheet Recommend use WorkBook 14 Use the tab key to enter the de ned name 15 If the formula is in the table but the de ned name is outside of the table the reference of the cell will be absolute oCreate from Selection 1 Top row its going to take the entry of the top row on each column and it will sign to de ned names for each column has a de ned name and the name will be the text of top row 2 You have to select from the rst row that has text Formulas include de ned name 1 if the formula is in the table and the range within the de ned name also in the table they both in the table Excel will only use the cell within that range that39s on the same row and its use as relative address 2 If the name was de ned after the formula was built they are not automatic substituted 3 De ned you name and then build your formulas Name manager 1 add name edit name delete name 1 Certain commend create the de ned name for you Ex print area 2 NAME MISS SPELL or does not exist 3 You can get the list of the de ned name USING FORMULA PASTE NAME PAST LIST 4 The list will not update if you add or delete names
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'