BUS150 notes for the final(last) exam
BUS150 notes for the final(last) exam BUS150
Popular in Business Analytics
Popular in Business Administration
This 2 page Test Prep (MCAT, SAT...) was uploaded by Nuyev on Thursday February 5, 2015. The Test Prep (MCAT, SAT...) belongs to BUS150 at University of Miami taught by Maryann Barber in Fall2014. Since its upload, it has received 52 views. For similar materials see Business Analytics in Business Administration at University of Miami.
Reviews for BUS150 notes for the final(last) exam
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 02/05/15
Macros Purpose Macro Security Macros are programs therefore they can contain viruese Shortcut Keystrokes Used to eliminate doing the process over and over again Can be given to people who don t know a lot about excel and they can still create spreadsheets allow to use advanced commands for newbies enabling macros saving macro enabled workbooks Shortcuts to make the macro run Naming the Macro Macro can be named whatever you want however it must begin with a letter or underscore Space or other invalid characters are not allowed The name con icts with excel builtin name or the name of A AAJ ALIAAA 2 LL A 17LAA17 IF a letter that is already used in another basic shortcut it will be replaced when the macro is opened Storing the macro Personal Macro Workbook vs This Workbook If saved in personal macros workbook a new workbook personalxlsb is created and all personal macros are added to this workbook Saved in the folder XLSTART This means whenever excel is running macros are turned on Personalxlsb is hidden If the macro is useable in all workbooks put it into the personal macro workbook If is specific it should be saved in This Workbook Recording the macro New module sheet vs existing module sheet AltF11 open the modules sheet Record a new module starts with Sub When a new macro is created whilist the worksheet is still opened it will record it under the modulel under the previous macro However if the worksheet is closed and reopend new module is created Relative recording vs absolute recording cell addresses vs offsets Absolute recording By default recorded macros use absolute cell referencing which means that exact cell locations are recorded into the macro The macro works only with the exact cells addresses you use when you record the macro Relative Recording When you record a macro with relative cell referencing the actions recorded in the macro are relative to the starting cell location For example absolute record will always perform the macro in Cells A A1 ABSOLUTE to the selected cellselected cell doesn t affect Relative cell has always offsets 1 0 move down one 0 1 move right one 1 0 move up one 0 1 move left one 5 4 up 5 right 4 5 4 5d 4L Offsets show where macro is written RELATIVE to the selected cell Understanding the macro code Selecting a cellrange Entering data Formatting the font Worksheet Protection Purpose Locked vs Unlocked Cells Hidden property commands vs comments Comments start with an apostrophe can be deleted or edited without affecting the macro usually green Commands show what happens For example ActiveCellFormulaR1C1 writing something on the workbook SubEnd Sub Sub macro starts End Sub macro ends Range A2 select select a cellabsolute macro ActivecellOffset1 0 Range A1 select select relative cell to the previous one ActiveCellFormulaR1C1 TEXT With SelectionFont Name size So cant be edited Lock certain parts Formulas cant be stolen Accidents cant be made allows you to lock cells prevents modification of data validation and formats Locked cells cant be changed relatively to the criteria Widening a column ColumnsquotBB ColumnWidth 1944 Refiningediting recorded macros Can be edited by just retyping or changing in the body Renaming macros Names can be edited in the module sheet Reassigning shortcut keys Can not be changed in the module sheet must be changed macrooptions Copyingmoving macros The macro can be copied from the module sheet pasted edited InputBox statement Displays a dialog box for user input Returns the information entered in the dialog box Assigning macros to a Button Insert choose button type Debugging macros Protecting Worksheet Passwords Password can be set to unprotect Workbook Protection Workbook protection prevent deletion moving and hiding unhiding of worksheets Allows to set password to open or delete a workbook Passwords Open and view the file this password is encrypted to help protect your data from unauthorized access Modify the file this password is not encrypted and is only meant to give speci c users permission to edit workbook data and save changes to the le Encryption Next level of protection doesn t allow the text to be read unless its an authorized user Comments Purpose can help you make a worksheet easier to understand by providing additional context for the data it con tains AddingEditingDeletingShowHide Review tab to add Red triangle indicated a comment delete comment by selecting the cell To print click the show comments hide to not print What If Analysis Goal Seek Set a value to a certain value by changing another value Requires formula to work Scenario Manager Scenario Scenario can show different outcomes by changing different variables Click on the scenario manager choose the cells to be edited Write a name for each scenario click Show to show Miscellaneous Data Validation Choose data to be validated Click data validation set the constraints Types of data that can be validated Make a list of the entries that restricts the values allowed in a cell Create a prompt message explaining the kind of data allowed in a cell Create messages that appear when incorrect data has been entered Check for incorrect entries by using the Auditing toolbar Set a range of numeric values that can be entered in a cell Determine if an entry is valid based on calculation in another cell Input Message Shows when you select the cell it s a box with a message Error Alerts Stop Warning Information amp messages Stop doesn t let you do it enter message to be shown Warning you can continue but you can also not conitinue Information just letting you know something doesn t restrict Validating existing data andor results of formulas Circle Invalid Data If data validation is made AFTER the data was entemed errors not nnnnih n 11 Scenario summary report shows all the scenarios their outcomes and what they effect Shows the cell which is changed and the result cell Solver Objective Cell Objective cell the one we want to editsolve for Variable cell isare the ones we want to manipulate Binding constraints Constraints that took place nonbinding didn t take place Slack value the difference between the nal value and the lower or upper bound imposed by that constraint SUMPRODUCTO Sum of one value on the table multiplied by the parallel Formula Auditing Logic errors vs Syntax errors Syntax error when the formula doesn t work shwos an error Logical errors are not shown by excel Tracing Precedents amp Dependents Display the relationships of the formula Precendents show the values which made up the selected cell Dependents show the values which are EFFECTED by the selected cell Evaluate Formula Evaluate the formulas to see where you made a mistake if you did step by step showing Watch Window A window which shows the cells you selected you can move around the spreadsheet and see a speci c result Paste Values Paste values instead of formulas Special Formats SSN Phone Number Zipcode Zip Code Retains any leading zeros in the value important for zip codes and of absolutely no importance in arithmetic computations Example 05497 Zip Code 4 Automatically separates the rst ve digits from the last four digits with a hyphen and retains any leading zeros Example 054975555 Phone Number Automatically encloses the rst three digits of the number in parentheses and separates the last four digits from the previous three with a hyphen Example 999 5551111 Social Security Number Automatically puts hyphens in the value to separate its digits into groups of three two and four Example 555009999 Text to Columns command Text Functions amp operator Copy a text to another destanation To dim use this code Months as 1 12 m Months as 3931 3912 mm CONCATENATEO Join various texts in one cell FIND Find in the text to find how many characters are between two words Month a tent mmm LEFI39 LeftThe text number of charactersposition1 Length Of the text Months as January December mmmm LOWER converts all the text to lower case PROPERO Proper case one letter upper case the rest lower case RIGHTO Used to find the right side from the space RightTEXT lentext position of Months as the first letter of the month mmmmm Space Days as 1 31 d TRIMO Removes duplicate spaces and spaces at the start and end of a text string D 0 3quot dd TEXT Converts a supplied value into text using a userspecified was 39 39 formatTEXTA1 quot000quot M as 5mm ddd UPPER All the text to UPPER case quot 39 Days as Sunday Saturday ddcld Years 00 99 yy Years as 1900 9999 5