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

Notes #1-3

by: Dodo Notetaker

Notes #1-3 BUS 150

Dodo Notetaker

Almost Ready


These notes were just uploaded, and will be ready to view shortly.

Purchase these notes here, or revisit this page.

Either way, we'll remind you when they're ready :)

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

75 ?





Popular in Department

This 2 page Bundle was uploaded by Dodo Notetaker on Monday August 31, 2015. The Bundle belongs to BUS 150 at University of Miami taught by MARYANN BARBER in Summer 2015. Since its upload, it has received 110 views.

Similar to BUS 150 at UM


Reviews for Notes #1-3


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: 08/31/15
simple and excel tables made up records rows and records are made up of elds columns Data definition table is a documentation that lists the elds to be maintained for each record and a description about each field Rules for making a table 1 The eld names are in the first row of the table and are formatted differently than the data records 2 Each field name is descriptive of the data entered and each field name is unique 3 There is a blank row above and below the table there is a blank column to the right of the table and no need for a blank column to the left as the table begins in column A 4 There are no blank rows anywhere within the table You can rearrange or sort the records in a table by sort fields ascending order arranges text from a z numbers smallest to largest and dates from oldest to newest Descending order arranges text from Z A numbers largest to smallest and dates newest to oldest Blank fields are placed at the end in both You can filter multiple columns and each additional filter is applied to the current filtered data and further reduces the records displayed The AND condition requires that all of the criteria must be true for the record to be displayed while or must have at least one value Criteria filters enable you to specify various conditions in addition to those that are based on an equals criteria does not equal begins with contains ends with between less than last week etc You can summarize data by subtotals you need to sort the data so the records are grouped together CANNOT BE USED IN AN EXCEL TABLE NEED A RANGESimple Level 3 outline displays most detail individual art records subtotals and grand totals level 1 displays least detail pivot table is an interactive table that enables you to group and summarize ranges of data into a concise format for easy analyzing The fields that contain summary data are known as value fieldssum of average of category labels are your row labels rst column Row fields displayed as rows Column fields displayed as columns lter lter report by selecting one or more items and enabling you to display a subset of data in a pivot table report values fields you want to summarize pivot tables need to be REFRESHED DO NOT UPDATE AUTOMATICALLY You CANNOT change the values within a pivot table only change values from table but you can add remove and rearrange elds to change the layout Benefits Easier to summarize large amounts of data simple to rearrange fields to view data from different perspectives PivotTables must be refreshed You can change the chart type and format the different charts easily for a PivotChart A report lter allows you to filter the table to display summarized data for one or more field items or all eld items in the report lter area example creating a report filter for location allows you to view or print total value for all locations Add remove lter and refresh data fields in the pivot chart Adding to the table will add to the pivot more exible s will be in general form by default Pivot Chart always uses rst column when created from pivot table switch by showing field list and moving field Tables Field Names Simple Tables Rules Create fields that require the least maintenance use birth date instead of age because their values do not change yearly Store the smallest unit of data possible in a field use 3 separate fields for city state and zip rather than one filed Apply a text format to elds with numerical text data formatting fields ensure that leading zeros are stored as part of the data External Using multiple worksheets makes it easier to group and summarize data and able to place summarized data first Apply formatting enter formulas and data edit data or formulas set page layout options footers headers apply views and print Saves you time because you can perform an action once yet affect multiple worksheets but grouped worksheets must have the same organization and layout for this to work To copy a worksheet press and hold the control key as you drag and drop the sheet tab to another location in the workbook a number in parenthesis is added to the copy s sheet tab to distinguish it from the original worksheet To reference a cell or range in a different worksheet you precede the cell or range reference with the worksheet name followed by an exclamation mark If the worksheet name has spaces enclose the sheet name in single quotations SheetnameCellrange Quarter1D10 Quarter 1 E12 Quarter 2 D12 When two or more worksheets have identical row and column layouts you can enter formulas with 3D references to summarize those worksheets in another worksheet A 3D reference refers to the same cell or range in multiple worksheets in the same workbook WorksheetRangeCellRange such as SumQuarter1Quarter4E3 When copying a sheet original workbook not changed when sheet is changed If you need to reference data located in one or more other workbooks you must create a link between the workbooks A link is a connection between the files that allows data to be transferred from one le to another The source le is the workbook that contains the data and the destination file dependent le is the workbook that receives the data Because the formula will contain a reference to a cell or range in a worksheet in another workbook that reference is called an external reference WorkbooknameWorksheetNameCellRange ColoradoxlsxSummaryB6 New Mexicoxlsx Summary B6 Can display all the open workbooks on your screen at the same time so you can easily click among them to create links wo having to continually change the active workbook Tiles divides the open workbooks evenly on the screen horizontal divides into horizontal bands vertical divides into vertical bands cascade layers the screen You Ln t use SUM or other functions with external references If you move a source le to a different folder the link breaks and you need to click change source in the edit links When workbooks are linked it is important that the data in the destination le accurately re ects the contents of the source file If both the source and destination files are open when you make a change destination file is auto Updated If the destination file is closed when making a change you choose whether to update the link when you open the destination file When a link is broken all the external references are converted to their most recent values The edit links box lists all the files the destination workbook is linked to so that you can update change open or remove the links To open multiple workbooks at one time you need to create a A workspace is an excel le that saves information about all of the currently opened workbooks such as their locations sizes zoom magnifications and other settings The workspace does not contain the workbooks themselves only the information about them Excel opens the workbooks and settings in the same con guration they were in when you saved the workspace le Even if a workbook is included in a workspace file you can still open that workbook separately workspace file has extension xlw AND will return a TRUE value if all the logical conditions are true and a false value if any or all of the logical conditions are false ANDlogicall logical 2 any bronze also new and locker or plus and iforcell bronze Mcellnewcelllockermm OR is a logical function that returns a true value if ANY of the logical conditions are true a false value if all the logical conditions are false Orlogicall logical2 And function does not determine amounts only gives you true and false same with or for that you need to combine it with an IF statement DATEDIF calculates the difference between two dates and shows the results in months days or years DATEDIFolder Datel newer Date2 dmy only calculates COMPLETED years or COMPLETED months Also for days you can use newerolder VLOOKUP vertical lookup searches vertically down the lookup table and is used when the compare values are stored in the rst column of the lookup table VLOOKUPlookupvalue tablearray columnnumber rangelookup When you use a range of values you set range lookup to true and when you want to exactly match a value You can calculate the number of cells in a range that match criteria you specify using the COUNTIF Conditional count Countif range criteria Range is range of cells you want to count and criteria is an expression that de nes which cells to count Text strings are encloses in quotes while numeric criteria are not SUMIF Conditional sum SUMFrange criteria sum range Range is range of cells you want to lter before calculating a sum criteria is condition used in the range to filter the table and sum range is the range of cells you want to add sum range is optional SumFEmployeelocation Austin Employeeannual salary Average if Conditional average calculates average of values in a range that meet criteria specified AVERAGEFrange criteria average range Countifscriteria range criterial criteria range2 criteria 2 CountifsJob status FT SEX F Salary gt50000 Sumifssum range criteria range 1 criteria 1 criteria range 2 criteria 2 Sumifsannual salary location Austin Hire date gt1 12008 Averageifsaverage range criteria range 1 criteria 1 criteria range 2 criteria 2summarize data using several conditions Advanced filtering enables you to perform OR conditions across multiple fields also used to create complex criteria using functions and formulas Before ltering you need to create a criteria range an area in a worksheet used to specify the criteria for the data to be displayed after the filter is applied to the table Consists of a header row and at least one row of the specific filtering criteria for each field Thus at least 2 rows because first row is eld names Criteria on the same row are considered connected with the logical operator AND criteria on same row must be met before a record is included in the ltered table Criteria on separate rows is OR and records that meet criteria on either row are displayed advanced data tab entire table criteria range lter in place copy 0 Home is strictly home while Home retrieves records that begin with home like Homewood 0 Place criteria range above or below because if on side criteria may be hidden when advanced filtering causes rows to be hidden Functions that perform summary data analysis on a table of values based on criteria are database or Dfunctions Dfunctiontable range column to summarize criteria range DCOUNTA returns number of nonblank cells that meet criteriaDCOUNTARealtyquotListing IDquotCriCityDate DMAXRealtyquotSalesPricequotCriSqFtBedsDAVERAGERealtyquotAsking PricequotCriFootageDMlNRealtyquotAsking PricequotCriSalesPrice DSUMRealtyquotquotCriDateSold Assigning names to cells or ranges makes building an application more intuitive and easier to document A defined name is a word or string of characters associated with a single cell or range More understandable or to quickly navigate through a cell AverageD1D100 or Averagesales Rules must begin with a letter or an underscore Name can include letters numbers periods and underscores Name cannot be a cell address or reserved word and it is NOT case sensitive Sales and SALES are same name Names can be quickly de ned if the data is organized in a tabular format with labels names based on these column or row labels When pasting names as a list the list is not updated when you edit a name or add a new defined name You must paste the list again to update nameslocations Can replace cell addresses in existing formulas with their de ned names to make the formulas more understandable defined names on formula tab and apply names Names follow the cells they reference as you insert delete and move cells Defined names will not automatically be substituted for the cell addresses they reference if they are created after formulas have been entered into spreadsheet After de ning a name for a range of cells you can edit range that the name refers to Practice guizzes FIELDS ARE COLUMNS RECORDS ARE ROWS 3 advantages to using an Excel table rather than a structured range of data Filter arrows are displayed that allow you to easily establish criteria Totals Row can be displayed to easily calculate summary statistics Field names replace column letters as you scroll Duplicate records are easily removed Selecting records elds is simplified It is easy to add a new record bottom row of table andor a new field column to the right amp expand the table de nition to include the new record eld Piv0tCharts always use the rst column for category labels you can change the chart type and format a PivotChart as you would any other chart When using a 3d reference to calculate a total layout must be identical if you rename a source workbook you must edit any links in the dependent workbook to specify the new name of that source workbook deleting a worksheet cannot be undone once you have created and saved an excel template the template can be edited Embed a chart embedded all ways graphic object data linked format embedding link chart linked all aspects Multiple workbooks absolute reference so if copied need to change to relative external reference when open dependent le asks before updates Copy control and drag move just drag cant group workbooks just sheets value can have same eld twice amount sum and average SUMOakwoodRamblewoodB4 or OakwoodlB4Maplewoodl B4Ramblewood B4 AFInv nanninn Ikn In39nvlllnnnll Anna 9 an I A r I A Halliunto knn AIIAII person has Criteria 1 I Date Sold I Criteria 2 I City I Agent Sweetwater Tobin Bal Harbour Tobin Criteria 3 I City I Date Sold l Pinec rest gt912008 Criteria 4 I Date Sold 0 1 Homes Not Yet Sold 2Homes in Sweetwater or Bal Harbour where Tobin was the agent 3 Pinecrest homes sold after sebt 2008 4 Homes that have been sold Average Commission Amount averagecommission column Number of Homes where agent was both listing and selling countifs criteria ran e quotcriteriaquot Total Commissions paid to Lazarus sumifstsum range criteria range 1 criteria 1quot criteria range 2I quotcriteria 2quot OakwoodxsxOwoodB4RamblewoodxlsxRwoodlB4MaplewoodxlsxMwoodB4 they are separate workbooks and workbooks cannot be grouped After opening the 4 workbooks click Arrange All on the View tab Need VLOOKUP and HAVE NONE SAY NONE 011 health plan lF011quotNonequotquotNonequotVLOOKUPOl1O5P82FALSE 39Eullllllarp39 ltcrt CW mm rl tI39 quot i quottr1i u awnmm namnu rim 9mm gt liluarta r and I1 1 an 3 FANDH11quotFuI TimequotGl1quotSaaryquotM8K11quotN0nequot AND BECAUSE NEED TO BE FULL TIME AND SALARY FANDORH11quotFuII TimequotJ11gt5M11quotYquot M7K11quotN0nequot OR BECAUSE 2 OPTIONS AND BECAUSE NEED TO SAY YES TO LIFE INSURANCE DATEDIFEARLIEROLDER DATE TODAYSMOST RECENT DATE


Buy Material

Are you sure you want to buy this material for

75 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!"

Anthony Lee UC Santa Barbara

"I bought an awesome study guide, which helped me get an A in my Math 34B class this quarter!"

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."


"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.