New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

Data Analytics Final Exam Notes

by: EricKoeller

Data Analytics Final Exam Notes BUS 150

Marketplace > University of Miami > BUS 150 > Data Analytics Final Exam Notes

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

These Notes will Cover the information taught from the end of the 2nd Exam Until the Final
Study Guide
50 ?





Popular in Department

This 12 page Study Guide was uploaded by EricKoeller on Thursday June 30, 2016. The Study Guide belongs to BUS 150 at University of Miami taught by MARYANN BARBER in Summer 2016. Since its upload, it has received 5 views.

Similar to BUS 150 at UM


Reviews for Data Analytics Final Exam Notes


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: 06/30/16
Data Analytics Final Exam March 24, 2016 Very Often Data your working with comes from another source Data comes from some kind of format that’s not really an appropriate format so you need to change it up Find Function- allows you to look for a character, such as a blank space =Find(“ “,A7) Looks for a particular character and tells you what position iti si n Finds blank space in A7 Any chacter and the cell it is in Two Spaces- Gives me the condition of the first occurance of that character Find= “ann”, A7- comes back as a value error Why? Case Sensative- match upper and lower case ANN brings back 5 First occurance of the first character in the character string Note- actually type in blank space LEFT- Gives us the left most characters in a cell =LEFT (cell, # of characters) =LEFT (A7, 7) How do you find out name Blank space is position after first name ends Don’t want position of the space Right- Gives us evertsthing starting in the right most cell =Right(cell, #of characters)’ MARYANN_BARBER To find last name, we need the length of the entire character string and substaact the space and the first name Len- length of the entire character string =LEN(CELL) =RIGHT(A7,len(A7)-B7) Don’t Want the Name in all caps PROPER(CELL) Will take the name and capitalize ontly the first letter of each word UPPER(cell)- Capitalizes everthing LOWER(cell)- makes everything lowerclase Concatenate- to join something Use & ampersand Concatenate function- too long Use ampersand Join First and last name- problem- no space Use blank space Don’t need to delete cells Just hide them Place Values Copy the final column Paste values- pastes the valees, not the actual formula Paste values- in top of or on top of a different range Once you past values, you can delete the columns without breaking the functions Table 2 SS secutite numbers need to be formatted corrected Lead 0 disappeasrs Special Formats for SS Click ok- it puts in the leading zeros and dashes where appropate it It does not change the entry Formatting characters In the dialogue box, it’s the same number as befre Zipcode or Zipplus 4 Phone Number Ect Insert 2 new columns Put the First Name in one and Last name in the other =PROPER(LEFT(B2,FIND(“ “, B2)-1)) =PROPER(RIGHT(B2,LEN(B2)-FIND(“ “B2))) Highlight column- click on Text to Colums Dilimeted and Fixed Width Dilimted- something that dimilites or seperates the fields It can be a comma, space Something tells you where one field ends and another begins Bob,Jones,Com Jane,Allen,BUS Dylan,Fitzgerald,EDU You know where one begins because of commas Fized Width- they begin in the same characted position First name starts in position 1 Last anem in 10 Zip in 15 ect This example is delimited Specifiy the dilimeter0 Theres already data here- d you want to replace it Problem- theres a blank space in front of Arizona =trim() removes Plank Spaces leading blank spaces, trailing blank spaces and consecutivce blank spaces put it in new columns and pasta values, and deltate And then use text to colums to further spate Don’t like the date I just want the month and the year Custom Formats (only for dates) Lots of different ones you can do, but were only doing date Format Cell0 Custom Type in M M- Number MM-two didet number MMM- Abreviation MMMM-Spells out the Month D- One or 2 didger day DD- Two Didget Day always DDD- ABreviated Day of week DDDD- Full Day YY- two didget year YYYY- 4 didger year Can combine Cannot Show SS number Make a unique ID LEFT MOST OF LAST NAME AND LAST 4 DIDGETS OF SOCIAL HOW to test for duplicates? Conditional Formating Longer- More likely that its unique =text(B2,”000-00-0000” tells us how we want the format of the number to look April 5, 2016 Lab Practicum 4 statistics 3 formulas You will read the case and read the variables in the assumption area You will need to complete VLOOKUP tables In the countifs, sumifs, average if You will use liteals =COuntifs(A1:A30,”>2”, B1:B30”Ranch”) =IF(AND(A4=”Ranch”,B4=3 Goal Seek- Vary one imput into a formula to get a desired result. Goal seek automates, allowing you to vary one imput to ge the result that you want. Automobile Loan Odutput had the formulas Say: I don’t want a payment above 300 hundred dollars. What needs to change in the assumptioj n area Lower Interest Rate Decrease Price of the Car Higher Down Payment Longer Term Click on formula for the Payment function What if analysis- Goal Seek Set cell- Has to be one with a formula Set that cell to value 300 (We want an answer of 300) By changing cells- Down Oayment (only changes one input) Click Ok We could look at different inputs but we need to use the Goal seek command each time We can use it to force the input to be each time Scenario-hypothetical set of circumstance Add Scenario- Scenario Name- Optomistic Changing Cells- what are the cells that you want to put numbers into to obtain a desired result Enter Values for each of the changing cells Mich easier if you defined names Add rd 3 What IF- Solver Solver- Will let you search for an optimal sitatuon to the problem by manipulating value of seval valuables Optimal solutions by mulipaing the value of seval valuables within the restictions you impose You can look for the maximum value- whats the maximum number of products I can product and still make a profit Can Look for a minum- whats the fewest number of workers I need to product x amount for a product Or you can look for an exact value- how can I make my monthly payment be exactly 300 dollars To install solver- File Options Add-Ins Under Ad Ins- Manage, drop down and go to excel AdIns and click Go Set Objective- Objective cell is the fell who has the forumula you want to modify It’s the cell you want to find the max, or the min fore It will be reference either directly or indirectly by the changing cells Our Objective cell is the monthly payment We are trying to optimize the monthly payment with a value of 300 dollars Objective- $B$12 By changing variable cost B2:B5 April 12, 2016 Macors Click in A1 and type in name Typo in name- not a problem because entry is not recorded until you record the entrt (hit the enter key) What happens when you hit the enter key- different things can happen Can be set to different cell Don’t use enter because it records two things Hit the check mark at the top of the screen Hit the itallac button And enter Arial (even if Arial is the default, still select it) Widen colum Select A3 Stop Recording Hit A3 and Control N and the Macro is run Executes a lot faster then can be done by hand Insert Macro Same thing Control 11-opens code for Macbook Visual Editor of Aplications Visual Basic of APplcications Project Explorer VBA project for every open workbook Difference between VBA for book 1 and the VBA project for book 2? Book one has a modules folder Modules folder- indicative that the thing has Macros If there are Macros associated with a workbook, when you get to modules folder you will seethe Macros Module- also called a module sheet, stores Macros First line in the Marco is always a substatment- begins the marco End Sub- ends the Macro Next 5 lines- what is different- each one begins with a ‘- the comments Lines of documentation- meant for humans to read- not to be programmed- could give you important information about the marco You can start and comments as long as it starts with a single quote Not required Indentations are for humans to help them see the logic- Active cell command- puts the name in whatever the active cell happens to be. Why is it written that way, because the code demands it When you drop down the formatting, it took a snapshot of the dialogue boz Deletes commans you do not need because they take time to excute- unessary ones can be gotten rid of Debug- check for errors When a macro isint running correctly SYNTAX IS WRONG- no period when you need a period; mispledded words Logic is incorrect- that’s where debug commant can help Debug command- syntax is correct but there is a logic error somewhere Yellow line- about to be executeds in a deub Skips comments Changes in a comment- no effect on anything How do you change a shotcut key Must be done in excel commant- excel optoons for that particular macro What do I go when I want to add something and I do not what the command is. Create a sepeerate Marco where the only difference is Font is red and copy code Dummy Macros- macro you only write to you can get the code Sace the workbook because it has Macros associated with it File- Save As- Give Name My Macros N Control N- opens Macro Regardless of whtiehr it is the original Macros document As long as “My Macros N” is open Write a new Macro Goes to D1 and enteres date- = today Enter Date- Control D D1-= today Enter it Stop record Where is new macro Open VBA prodct New Macro- lovated in Module 2 Why did it record Macros recored in same session- go on same module sheet Different session- goes on spate module sheet Key difference- you closed the workbook Record a new Macro Put it in a personal macro workbook Go back to devolper tab and record a maco Store macro in Personal Macro Folder Creating personal.xlsb creating it in a folder named excel start Offses (r,c) 1,0 April 14, 2016 Shotcut keys are availbile in all active workbooks So be care not to open marco in a workbook you don’t wantg Check under disabled items if the personal macro workbook isint working. When the marco is avalible (and in memory), it will override any other commands Absolute Record vs Relative Absolute- Range (a1) select If the selet statement has the word offset, it’s a relative record. In an absolute record- slect A1- enter name Select and put name ect Whtever is selected, it will do that to the font The coordinates next to the offset- (how many rows moved, how many columns moved to left or right) Rows- positive is down, negative is up Columns- positive is right, negative is left Offset- where to move (a1 as the reference point), and what to put in the cell When you have offset, it tells you what the selection looks like What to change the name of the marco- done in the sub stantment Only place where it could be changed Where do I chage the shortcut key- go to Marcos- select the Marco you want and hit options- only place it can be changed You can make changes directly to the code of the macro New session- goes on new module sheet What is it called when you want to make the dialogue box appear Hit the imput box Take out the exact entry and insert an input box If I can the debug command, I can watch the debug execute the command Data Validation- talked a lot about data validation Lot of different ways of validating data --make sure there are no duplicates in unique fields -- Validate data as it is entered into the spreadsheet If someone enteres data that is not valid, it will not be register Data validation types of tools, We will be able to limit to fields such as whole number, or decimal, or list, ir data or time Date or time- before, after or between Text- = to, greater then , less then, between length- Can be used with numbers such as zipcode Custom rule Imput message- the message that is displaced when theres an error- optional Error alert- not optional- default is a stop Stop- if you break the validation the rule, it stops and it wont let you Warning- you make an entry and fail the rule, exfell will stop you and ask you if you want to continue- gives tou managerial discressions to break the rule Information- does nto stop or warn, it simply putsup some information Cannot validate every piece of information- too much to process Last name- cannot test for every name Branch- Accounting, Operations ect- Yes you can because tis fininae Data Validation Allow: List SOuces- (permissible enteres) Accounting, marketing ,ect Input message Title- Department Unput Messages- “Entries can be Accounting, Administation, Marketing” Error Message- same as Input message- need to tell the computer what to do right Stop slected When I go to cell, it tells me when I highlight it Gives you a drop down and all you need to do is select If you try typing something in and it doesent give the thing and gives the error message Will not let you enter it Hours Setting- Whole Number Beteeen 0 and 40 “Max imput hours are 40” Not going to make this one a stop- going to make it a Warning “Max Regular Hours are 40” you can continue if you wanr Overtime \Custom- Formula---Stop---messge- must work 40 regular hours Hurricane Payroll Data Validation List- Remember that data is validated as its entered If the data is alredy there- it wont give an error Ciricle invalid data0 circles entire data and finds data that is wrong You can fix it and then clear the validation circles Results of fomulas are not tested- there forumals- not entries So if it’s the result of a calculation or datas already been entered prior to entering the rule Circle invalid data Only one data validation rule is allowed per one cell One invalidation rule replaces the other Employee ID should be text of a certain length Worksheet Protection We can validate data as it is being entered We can make it as perfect as possible But someone can still come in and screw up our spreadsheet All it takes is someone to imput the wrong fomula in the wrong cell All cells have a property called a locked property Go to protection cell Every cell has the locked property on Has not effect until you select Protext sheet If the sheet is protected and locked- it cannot be edited- cannot have any enteries You don’t want every cell proetected Highlight cells and turn off lock cell Either through format cell or through dialogue box Go to a cell with a formula- you make a change Can only make enteries in cells that are unlocked on a protexted sheet Cannot do what you want if you unprotext sheet


Buy Material

Are you sure you want to buy this material for

50 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


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'

Why people love StudySoup

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

Kyle Maynard Purdue

"When you're taking detailed notes and trying to help everyone else out in the class, it really helps you learn and understand the I made $280 on my first study guide!"

Bentley McCaw University of Florida

"I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"


"Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.