IGMT 1400

To use the PMT function:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Financial button. 3.Select PMT from the list to open the Function Arguments dialog. 4.Enter the Rate argument. This argument is the interest rate. 5.Enter the Nper argument. This argument is the total number of payments over the life of the loan. 6.Enter the Pv argument. This argument is the present value of the loan—how much you owe now (the loan principal). 7.(Optional) The Fv argument is future value of the loan. 8.(Optional) The Type argument represents when payments will be made during each loan period. Click OK.

To create a formula using FV:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Financial button. Click FV to open the Function Arguments dialog. 3.Enter the arguments. In the figure at right, the interest rate is annual and the payments are monthly, so the interest rate must be divided by 12. Notice that the Pmt argument includes a hyphen before the cell reference to make it negative. The Pv and Type arguments are optional and not necessary in this example. 4.Click OK.

Planning phase

Establishes a high-level plan of the intended project and determines project goals

Function Arguments dialog box

Every function can be entered using its Function Arguments dialog. This dialog is different for each function. There are two primary methods for opening the Function Arguments dialog. 1.Select the function from the Formulas tab, Function Library group. 2.Select the function from the Insert Function dialog. To use the Insert Function dialog: 1.Functions in the Insert Function dialog are organized in the same categories as the Function Library group. 2.Click a function in the Select a function box to see a brief description of what it does and the arguments it takes. 3.Click OK to open the Function Arguments dialog for the selected function.

Using Date and Time Functions

Excel includes two functions that insert the current date or date and time. •The NOW function inserts the current date and time. •The TODAY function inserts only the current date.

Column chart

charts work best with data that are organized into rows and columns like a table. To insert a column chart: 1.Select the data you want to include in the column chart. 2.On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button. 3.Click the chart type you want to insert the chart into the worksheet.

Rapid application development methodology (RAD):

emphasizes extensive user involvement in the rapid and evolutionary construction of working prototypes of a system to accelerate the systems development process RAD is a more popular route for system development projects The fundamentals of RAD include: Focus initially on creating a prototype that looks and acts like the desired system Actively involve system users in the analysis, design, and development phases Accelerate collecting the business requirements through an interactive and iterative construction approach

The SumIf function

function totals the values only where cells meet the specified criteria. •SUMIF takes two required arguments and one optional argument: •Range—the range of cells to evaluate against the criteria •Criteria—the conditions (the criteria) the cell must meet in order to be included in the total, and optionally •Sum_range—(optional) the range of cells containing the values to be summed. Sum_range must be the same size as Range. This argument is necessary only when the values to be summed are not the same as the values to be evaluated against the criteria.

Regression testing

is the testing after modification of a system, component, or a group of related units to ensure that the modification is working correctly and is not damaging or imposing other modules to produce unexpected results. It falls under the class of black box testing.

Beta testing

is the testing which is done by end users, a team outside development, or publicly releasing full pre-version of the product which is known as beta version. The aim of beta testing is to cover unexpected errors. It falls under the class of black box testing.

present value

is the value today of a series of future payments. •For example, at today's interest rates, what is the lump sum you would need to fund a retirement account to pay an annuity (a series of constant payments) to yourself? •Present value can be calculated using the PV function when the payments are constant.

Patents

licenses that give an inventor the exclusive right to make, use, or sell an invention for a set period of time

Acting Ethically and Acting Legally Are Not Always the Same Thing

most organizations want to make decisions somewhere in quadrant I, both legal and ethical

A successful project

project is typically on time, within budget, meets the business's requirements, and fulfills the customer's needs. projects would need to be prioritized based on the business goals, which could change for each business

Waterfall methodology

sequence of phases in which the output of each phase becomes the input for the next A sequential, activity-based process in which each phase in the SDLC is performed sequentially from planning through implementation and maintenance one of the oldest software development methods and has been around for over 30 years The success rate for software development projects that follow this approach is only about 10 percent, or 1 in 10 The biggest problem with the waterfall methodology is that it assumes users can specify all business requirements in advance It also assumes that business requirements do not change over time -Stress to your students that if they ever find themselves on a software development project that is using the waterfall methodology they should do everything they can to change the methodology

As an organization's reliance on software grows

so do the business-related consequences of software successes and failures including: •Increase or decrease revenue •Repair or damage to brand reputation •Prevent or incur liabilities Increase or decrease productivity

AverageIfs

takes arguments for up to 127 pairs of criteria ranges and criteria. The result of AVERAGEIFS is the average of all the cells that meet all criteria. AVERAGEIFS takes three required arguments and multiple optional arguments: •Average_range—the range of cells to average where criteria are met •Criteria_range1—the range of cells containing the values to be evaluated against the first criteria •Criteria1—the first criteria •Criteria_range2—(optional) the range of cells containing the values to be evaluated against the second criteria •Criteria2—(optional) the second criteria continuing up to Criteria_range127 and Criteria127)

Legend

tells you which data point or data series is represented by each color in the chart

Unit testing

test individual units or pieces of code for a system

Development testing

test the system to ensure it is bug-free

Chart area

the area that encompasses the entire chart including the plot area and optional layout elements, such as title and legend.

plot area

the area where the data series are plotted.

Confidentiality

the assurance that messages and information are available only to those who are authorized to view them

Factors driving outsourcing growth include

•Core competencies •Financial savings •Rapid growth •The Internet and globalization

There are multiple ways to calculate depreciation, but most depreciation functions use the same arguments:

•Cost—the initial cost of the asset—usually the purchase price. •Salvage—the fair market value of the asset at the end of its useful life. •Life—the number of years the asset will be used. •Per or Period—the number of the accounting period, where each accounting period is one year of the asset's life. Cost, Salvage, and Life are constant values in the depreciation schedule and require an absolute cell reference or name reference. Period is usually a relative reference to the column in the depreciation schedule that displays the period number. As the depreciation formula is copied, this reference updates automatically.

The consequences of failed projects include:

•Damaged brand •Lost goodwill •Dissolution of partnerships •Lost investment opportunities •Low morale

Two primary diagrams used in project planning include:

•PERT chart -Dependency -Critical path •Gantt chart

Rounding with Functions

•Rounding refers to adjusting a number up or down to make it more appropriate to the context in which it is being used. •Numbers can be rounded up or down to the nearest whole number or the nearest factor of ten, hundred, thousand, or any limit. •The rounding functions in Excel are used specifically to control the number of digits to the right of the decimal point. •Excel uses the stored value, not the displayed value, in calculations. •In the following figure, the average unit price is 16.9387755102041 By adding one of the rounding functions to the formula, you can ensure that the stored value matches the displayed value

To sum data that meet multiple criteria, use SUMIFS.

. SUMIFS takes arguments for up to 127 pairs of criteria ranges and criteria. The result of SUMIFS is the total of all the cells that meet all criteria. SUMIFS takes three required arguments and multiple optional arguments: •Sum_range—the range of cells to sum where criteria are met •Criteria_range1—the range of cells containing the values to be evaluated against the first criteria •Criteria1—the first criteria •Criteria_range2—(optional) the range of cells containing the values to be evaluated against the second criteria •Criteria2—(optional) the second criteria (continuing up to Criteria_range127 and Criteria127)

System Implementation Methods

1) parallel implementation- uses both the legacy system and new system until all users verify that the new system functions correctly 2) plunge implementation- discards the legacy system and immediately migrates all users to the new system 3) pilot implementation- assigns a small group of people to use the new system until it is verified that it works correctly; then the remaining users migrate to the new system 4) phased implementation- installs the new system in phases until it is verified that it works correctly

To move a chart to a new sheet:

1.If necessary, select the chart. If you just created the chart, it will still be selected. 2.On the Chart Tools Design tab, in the Location group, click the Move Chart button. 3.In the Move Chart dialog, click the New sheet radio button to move the chart to its own worksheet. Click OK.

To apply a theme to a workbook:

1.On the Page Layout tab, in the Themes group, click the Themes button to expand the gallery. 2.Roll your mouse over each theme in the gallery to preview the formatting changes. 3.Click one of the themes to apply it to your workbook.

To insert a row:

1.Place your cursor in a cell in the row below where you want the new row. 2.On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Rows. 3.The new row will appear above the selected cell.

To create a formula using NPER:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Financial button. Click NPER to open the Function Arguments dialog. 3.Enter the arguments. -In our example, the payments will be monthly, so the annual interest rate (Rate) must be divided by 12. -The Pmt argument includes a hyphen before the cell reference to make it negative. -The Fv argument is omitted, but the Type argument is set to 1 because payments will be made at the beginning of every period. 4.Click OK.

survey of workplace monitoring and surveillance practices by the American Management Association (AMA) and the ePolicy Institute showed the degree to which companies are turning to monitoring

82 percent (of the 1,627 companies surveyed) acknowledged conducting some form of electronic monitoring or physical surveillance 63 percent stated that they monitor Internet connections 47 percent acknowledged storing and reviewing employee email messages

Creating a PivotChart from a PivotTable

A PivotChart is a graphic representation of a PivotTable. To create a PivotChart from a PivotTable: 1.Select any cell in the PivotTable. 2.On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart button 3.Select a chart type from the Insert Chart dialog. Click OK. If you need more room to display the PivotChart, you may want to hide the PivotTable Fields pane. On the PivotChart Tools Analyze tab, in the Show/Hide group, click the Field List button to hide or display the PivotTable Fields pane.

Rapid growth.

A company's sustainability depends on both speed to market and ability to react quickly to changes in market conditions. By taking advantage of outsourcing, an organization is able to acquire best-practices process expertise. This facilitates the design, building, training, and deployment of business processes or functions.

Project plan

A formal, approved document that manages and controls project execution A well-defined project plan should be: •Easy to understand and read •Communicated to all key participants •Appropriate to the project's size, complexity, and criticality •Prepared by the team, rather than by the individual project manager should be prepared by the team, rather than by the individual project manager -defined the timeframes they required to finish the tasks is not forced upon

Adding Headers and Footers

A header is text that appears at the top of every page, just below the top margin; a footer is text that appears at the bottom of every page, just above the bottom margin. To add a header or footer to a worksheet from Page Layout view: 1.Switch to Page Layout view by clicking the Page Layout button on the status bar. 2.The header area has three sections with the text Add header in the center section. If you do not see the Add header text, move the mouse pointer to the area just above row 1 to make the header area visible. Click the header section where you want to add information (left, center, or right). 3.The Header & Footer Tools Design tab appears. (cont'd.) 4.In the Header & Footer group, click the Header button and select one of the predefined headers, or click a button in the Header & Footer Elements group to add a specific header element such as the sheet name or the current date. 5.In the Navigation group, click the Go to Footer button to switch to the footer. Add footer elements the same way you add header elements. 6.When you are finished adding your header and footer elements, click anywhere in the worksheet and then switch back to Normal view to continue working.

Finding Errors Using Trace Precedents and Trace Dependents

A precedent is the cell containing the formula or value the selected cell refers to. A dependent is the cell containing a formula that references the value or formula in the selected cell. To trace precedents: 1.Select the cell containing the formula for which you want to trace precedents. 2.On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button. 3.Tracer arrows appear pointing to the selected cell from precedent cells. (cont'd.) To trace dependents: 1.Select the cell containing the formula for which you want to trace dependents. 2.On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button. 3.Tracer arrows appear pointing from the selected cell to dependent cells.

Intellectual property

A product of the intellect, such as an expressed idea or concept, that has commercial value. Intangible creative work that is embodied in physical form •Copyright •Trademark •Patents

Filtering Table Data with Slicers

A slicer is a visual representation of filtering options. To add a slicer: 1.Click anywhere in the table to activate the Table Tools Design tab. 2.On the Table Tools Design tab, in the Tools group, click the Insert Slicer button. 3.In the Insert Slicers dialog, click the check boxes for the column(s) you want in the slicers. 4.Click OK.

Prototype

A smaller-scale representation or working model of the users' requirements or a proposed design for an information system The prototype is an essential part of the analysis phase when using a RAD methodology

Digital rights management

A technological solution that allows publishers to control their digital media to discourage, limit, or prevent illegal copying and distribution

Applying Themes

A theme is a unified color, font, and effects scheme. When you apply a theme to the workbook, you ensure that all visual elements work well together, giving the workbook a polished, professional look. When you create a new blank workbook in Excel 2016, the Office theme is applied by default.

Accelerated

Accelerated depreciation assumes that the asset is worth more at the beginning of its lifespan and uses a higher rate of depreciation for early years in the depreciation schedule. In accounting, this is called sum-of-the-years' digits (SOYD) depreciation. Use the SYD function to calculate SOYD depreciation. =SYD(Cost,Salvage,Life,Per)=SYD(Cost,Salvage,Life,A5)

Fair use doctrine

Allows a user to make a copy of all or part of a work within specific parameters of usage, even if permission has not been granted. In certain situations, it is legal to use copyrighted material

Different Forms of System Testing

Alpha testing, development testing, integration testing, system testing, user acceptance testing (UAT), and unit testing, Regression Testing, Beta Testing

Mean Absolute Deviation (MAD)

An alternative way to calculate how far values in a population deviate from the average is to calculate the mean absolute deviation (MAD). The mean absolute deviation is the average of the absolute differences between each value and the overall mean. The absolute value of a number measures how far the number is from 0. The absolute value of a positive number is the same as the number. The absolute value of a negative number is the number without the negative sign. •The absolute value of 1 is 1. •The absolute value of -1 is 1. The function to calculate absolute value, ABS, takes a single argument Number. Number is a value or reference to a cell with a value for which you want to calculate the absolute value.

Outsourcing

An arrangement by which one organization provides a service or services for another organization that chooses not to perform them in-house

Project manager

An individual who is an expert in project planning and management, defines and develops the project plan, and tracks the plan to ensure the project is completed on time and on budget

Project management office (PMO)

An internal department that oversees all organizational projects

Project deliverable

Any measurable, tangible, verifiable outcome, result, or item that is produced to complete a project or part of a project

email can easily be read by:

Anyone who works for the Internet service provider Anyone who works for the recipient's Internet service provider Anyone who operates any of the perhaps dozens of Internet routers that the data packets will pass through Anyone with physical access to the telephone switching equipment in the phone company's office

Globalization.

As markets open worldwide, competition heats up. Companies may engage outsourcing service providers to deliver international services

COUNTIF

COUNTIF takes two arguments: •Range—the range of cells to count •Criteria—the conditions (the criteria) the cell must meet in order to be counted. The criteria can be a text string or numerical value or expression

What types of problems do your students believe they will encounter with social media in the workplace?

Calling in sick and then posting vacation photos Cyberstalking Wasting time at work on Facebook Threatening other employees on Facebook

Checking Formulas for Errors

Cells that include potential errors are marked with a green triangle in the upper-left corner of the cell. When you click the cell, Excel displays a Smart Tag to help you resolve the error. To use Smart Tags to resolve errors in formulas: 1.When a Smart Tag appears, move your mouse over the icon to display a tool tip describing the possible error. 2.Click the Smart Tag to display the possible error resolutions. 3.If you want to keep the formula as it is, select Ignore Error. (cont'd.) 4.If you want to resolve the error, select one of the options: -The first option is usually a suggestion of how to resolve the error. Click it to accept Excel's suggestion. -Select Help on this error to open Microsoft Office Help. -Select Ignore Error to leave the cell unchanged. -Select Edit in Formula Bar to manually edit the formula. -Select Error Checking Options... to open the Options dialog and modify the way that Excel checks for errors. Once you have made a selection from the Smart Tag options, the Smart Tag is dismissed

Iterative development

Consists of a series of tiny projects

Information privacy policy

Contains general principles regarding information privacy

Ethical computer use policy

Contains general principles to guide computer user behavior The ethical computer user policy ensures all users are informed of the rules and, by agreeing to use the system on that basis, consent to abide by the rules

Nearshore outsourcing

Contracting an outsourcing arrangement with a company in a nearby country

Analyzing Data with Data Tables

Data tables provide a quick what-if analysis of the effects of changing one or two variables within a formula.

Declining Balance (DB)

Declining balance depreciation takes into account the declining book value of the asset. Use the DB function to calculate declining balance depreciation. DB includes one optional argument—Month. This is used when the asset was in use for only part of the first accounting period. Its value is the number of months the asset was in service. When using this argument in a depreciation schedule, be careful to use it in the cell for period 1 only. Do not copy it to the remaining cells in the column. The formula for period 1 for an asset in use for 6 months in the first year looks like this: =DB(Cost,Salvage,Life,Period,[Month])=DB(Cost,Salvage,Life,A5,6) DB includes one optional argument—Month. This is used when the asset was in use for only part of the first accounting period. Its value is the number of months the asset was in service. When using this argument in a depreciation schedule, be careful to use it in the cell for period 1 only. Do not copy it to the remaining cells in the column. The formula for period 1 for an asset in use for 6 months in the first year looks like this: =DB(Cost,Salvage,Life,Period,[Month])=DB(Cost,Salvage,Life,A5,6)

Increase or decrease productivity

Defective software accounts for 45% of computer downtime and costs U.S. businesses $100 billion

Trademarks

Designs and names, often officially registered, by which merchants or manufacturers designate and differentiate their products

Increase or decrease revenue

Discuss Nike's poorly designed SCM software that delayed orders, increased excess inventories, and caused third quarter earnings to fall 24% below expectations

few methods that an organization can follow to prevent spam include

Disguise email addresses posted in a public electronic place: instead of actually posting all of your employee emails on the corporate website, just post the name without the @xyz.com. That way spam collecting devices will not recognize the email addresses and will not be able to send email Opt-out of member directories that may place an email address online: choose not to participate in any activities that place email addresses online Use a filter: Use a spam filter to help prevent spam

Editing and Deleting Names with the Name Manager

Duplicate names can be confusing. A good practice is to rename duplicates to make them easier to identify and use. To change a name: 1.Open the Name Manager. 2.Click the name you want to modify, and then click the Edit... button. 3.The Edit Name dialog opens. 4.Type the new name in the Name box. 5.Click OK to save your changes. 6.Click the Close button to close the Name Manager. If your workbook includes names you no longer need, you should delete them. To delete a name: 1.On the Formulas tab, in the Defined Names group, click the Name Manager button. 2.Click the name you want to delete, and then click the Delete button. 3.Excel displays a message asking if you are sure you want to delete the name. Click OK.

Scaling Worksheets for Printing

Each worksheet in the workbook has its own scale settings. On the Page Layout tab, in the Scale to Fit group, select the option(s) you want. •Click the Width arrow and select the maximum number of pages you want the worksheet to print across. •Click the Height arrow and select the maximum number of pages you want the worksheet to print vertically. •Click the Scale box and enter a percentage to grow or shrink the worksheet when printed.

Onshore outsourcing

Engaging another company within the same country for services

Design phase

Establishes descriptions of the desired features and operations of the system including screen layouts, business rules, process diagrams, pseudo code, and other documentation

Modifying Column Widths and Row Heights

Excel offers an easy way to automatically set columns to the width to best fit the data in the column: To make the column automatically fit the contents: 1.Move your mouse over the right column boundary. 2.The cursor will change to a shape. Double-click the right column border You can also modify column widths manually: 1.Move your mouse over the right column boundary. 2.The cursor will change to a shape. 3.Click and drag until the column is the size you want, and then release the mouse button. To specify an exact column width for multiple columns: 1.Select the columns you want to modify. Click the column selector for the first column, press and hold Shift , and click the column selector for the last column. 2.On the Home tab, in the Cells group, click the Format button. 3.Select Column Width... 4.Enter the value you want in the Column Width dialog. 5.Click OK.

Changing the Worksheet View

Excel offers three ways to view a worksheet. 1.Normal view is the typical working view. In Normal view, Excel shows the aspects of the worksheet that are visible only on-screen. 2.Page Layout view shows all the worksheet elements as they will print, including page margins and headers and footers 3.Page Break Preview view allows you to manipulate where page breaks occur when the worksheet is printed.

Hacker

Experts in technology who use their knowledge to break into computers and computer networks, either for profit or just motivated by the challenge •Black-hat hacker •Cyberterrorist •Hactivist White-hat hacker

Employee monitoring policy

Explicitly state how, when, and where the company monitors its employees

Prevent or incur liabilities

FoxMeyer sued SAP for $500 million for an ERP failure

Repair or damage to brand reputation

H&R Block customers were furious when the company accidentally posted passwords and social security numbers to its Web site

Industry changes.

High levels of reorganization across industries have increased demand for outsourcing to better focus on core competencies. The significant increase in merger and acquisition activity created a sudden need to integrate multiple core and noncore business functions into one business, while the deregulation of the utilities and telecom industries created a need to ensure compliance with government rules and regulations. Companies in either situation turned to outsourcing so they could better focus on industry changes at hand.

Email is Stored on Multiple Computers

How many places is one email stored each time it is sent?

Finding Data with MATCH and INDEX

If the value you want to look up is in a column other than the first you can use a combination of two functions: MATCH and INDEX. •MATCH returns the position of a specific value in a single row or column array. •INDEX returns the value at the intersection of a specified row and column in an array. First use MATCH to find the row or column position of the value you want, and then use the results of MATCH as the row number or column number argument in INDEX.

Grouping Worksheets

If you have multiple worksheets with the same structure, you can make changes to all of the worksheets at the same time by grouping them. To group worksheets: 1.Click the first worksheet tab. 2.Hold down Shift and click the tab for the last worksheet you want included in the group. If you want to select noncontiguous worksheets (sheets that are not next to each other), press Ctrl instead, and then click each sheet tab. 3.Notice that the title bar now includes [Group] after the file name. 4.Make the change you want to the sheet. This same change will be made to all sheets in the group. 5.To ungroup, click any sheet tab that is not part of the group.

Filtering Data

If your worksheet has many rows of data, you may want to filter the data to show only rows that meet criteria you specify. 1.On the Home tab, in the Editing group, click the Sort & Filter button. 2.Click the Filter button to enable filtering. An arrow will appear in each cell of the heading row just as if the data were formatted as a table. You can filter for an entire grouping such as a year or month or for a specific date. Use the + and - buttons in front of each date grouping to expand or collapse the group.

Printing Titles

If your worksheet includes a large table of data that prints on more than one page, you should ensure that the column or row labels print on every page. To repeat rows and columns on every printed page: 1.On the Page Layout tab, in the Page Setup group, click the Print Titles button. 2.In the Page Setup dialog, on the Sheet tab, click in the Rows to repeat at top box, and then click and drag to select the rows to repeat. You can also type the row reference(s) using the format $1:$1. This example would repeat the first row only. (cont'd.) 3.Click in the Columns to repeat at left box, and then click and drag to select the columns to repeat. You can also type the column reference(s) using the format $A:$A. This example would repeat the first column only. $A:$B would repeat columns A and B on every printed page. 4.Click OK.

Rounddown

Ignores the rules of rounding and automatically rounds down, regardless of the value to the right of the rounding point. A formula using the ROUNDDOWN function looks like this: =ROUNDDOWN(Number,Num_digits) =ROUNDDOWN(B1/B2,2)

Roundup

Ignores the rules of rounding and automatically rounds up to the next number, regardless of the value to the right of the rounding point. A formula using the ROUNDUP function looks like this: =ROUNDUP(Number,Num_digits) =ROUNDUP(B1/B2,2)

Converting Data into Tables

In Excel, you can define a series of adjacent cells as a table. When you define data as a table, Excel provides a robust tool set for formatting and analyzing the data. To define data as a table: 1.Click any cell in the data range. You do not need to select the entire range. 2.On the Home tab, in the Styles group, click the Format as Table button to display the Table Styles gallery. 3.Click the style you want to use for your table. 4.Excel will automatically populate the Format As Table dialog with the entire data range. If the data range is not correct, you can manually enter the cell range in the dialog. 5.Be sure to check the My table has headers check box if appropriate. 6.Click OK to create the table.

Information Ethics

Individuals, not technology, form the only ethical component of MIS •Individuals copy, use , and distribute software •Search organizational databases for sensitive and personal information •Individuals create and spread viruses •Individuals hack into computer systems to steal information Employees destroy and steal information

Analysis phase

Involves analyzing end-user business requirements and refining project goals into defined functions and operations of the intended system

Testing phase

Involves bringing all the project pieces together into a special testing environment to eliminate errors and bugs, and verify that the system meets all of the business requirements defined in the analysis phase

Maintenance phase

Involves performing changes, corrections, additions, and upgrades to ensure the system continues to meet its business goals

Implementation phase

Involves placing the system into production so users can begin to perform actual business operations with it

Why is the critical path important?

It informs a project manager of the shortest timeframe the project could possibly be completed in the project

Financial savings.

It is typically cheaper to hire workers in China and India than similar workers in the United States. Technology is advancing at such an accelerated rate that companies often lack the resources, workforce, or expertise to keep up. It is close to impossible for an IT department to maintain a "best-of breed" status, especially for small and medium-sized enterprises where cost is a critical factor.

Finding the Middle Value with MEDIAN

MEDIAN is a simple function to use. Most of the time you may find it easiest to type the formula directly in the cell or the formula bar. •Type =MEDIAN (and then enter the cell range or named range for the Number1 argument. Type ) and press [Enter]. •If you prefer using the Function Arguments dialog, on the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and then click MEDIAN to open the Function Arguments dialog. •MEDIAN takes up to 255 Number arguments.

MODE.SNGL

MODE.SNGL is a simple function to use. Most of the time you may find it easiest to type the formula directly in the cell or the formula bar. •If you prefer using the Function Arguments dialog, on the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and then click MODE.SNGL to open the Function Arguments dialog. You may enter up to 255 cell ranges or range names as arguments. If your dataset includes more than one mode, you can use the MODE.MULT function in an array formula to return multiple values. An array formula is capable of conducting multiple calculations, in this case, producing multiple results.

Core competencies.

Many companies have recently begun to consider outsourcing as a means to fuel revenue growth rather than just a cost-cutting measure. Outsourcing enables an organization to maintain an up-to-date technology infrastructure while freeing it to focus on revenue growth goals by reinvesting cash and human capital in areas offering the greatest return on investment.

types of technologies they use to manage projects

Microsoft Project Primavera Excel

The INDEX function in this example has three required arguments:

Now that you have identified the row position, you can use INDEX to find the value at the intersection of the row and column. The INDEX function in this example has three required arguments: •Array—the range of cells containing the entire data array. If your data include a header row, do not include it in the cell range or named range used for the Array argument. •Row_num—the row position in the array for the value you want to look up. •Column_num—the column position in the array for the value you want to look up. =INDEX(Array, Row_num,Column_num)=INDEX(Inventory,B2,3)

Intellectual Assets

Organizational information is intellectual capital - it must be protected

Email Privacy Policy

Organizations can mitigate the risks of email and instant messaging communication tools by implementing and adhering to an email privacy policy Details the extent to which email messages may be read by others

Epolicies

Organizations strive to build a corporate culture based on ethical principles that employees can understand and implement Organizations should develop written policies establishing employee guidelines, personnel procedures, and organizational rules These policies set employee expectations about the organization's practices and standards and protect the organization from misuse of computer systems and IT resources

Social media policy

Outlines the corporate guidelines or principles governing employee online communications Organizations must protect their online reputations and continuously monitor blogs, message boards, social networking sites, and media sharing sites

the difference between privacy and confidentiality

Privacy is the condition of being free from unauthorized observation or intrusion Confidentiality - is the ethical principle that workers should not share information provided by a client or about a client unless they have the client's explicit permission to do so.

What is the relationship between project deliverables, project milestones, and project managers?

Project deliverables are usually project milestones Project milestones and project deliverables defined and monitored by the project manager

Applying Quick Styles and Colors to Charts

Quick Styles apply combinations of fonts, line styles, fills, and shape effects. To change the chart style: 1.Select the chart. 2.On the Chart Tools Design tab, in the Chart Styles group, click the style you want to use, or click the More button to see all of the Quick Styles available. 3.To change the color scheme, on the Chart Tools Design tab, in the Chart Styles group, click the Change Colors button and select the color scheme you want.

Downtime

Refers to a period of time when a system is unavailable

Project milestone

Represents key dates when a certain group of activities must be performed

Column Widths and Row Heights

Rows in Excel are automatically sized to fit the font size. However, if you change the font size or apply a new theme, you may need to modify row heights. Use the same techniques you use for resizing columns:

Anti-spam policy

Simply states that email users will not send unsolicited emails (or spam)

Counterfeit software

Software that is manufactured to look like the real thing and sold as such

Sorting Data

Sorting rearranges the rows in your worksheet by the data in a column or columns. To sort data: 1.Click any cell in the column you want to sort by. 2.On the Data tab, in the Sort & Filter group, click the button for the sort order you want: -sorts text in alphabetical order from A to Z, numbers from smallest to largest, and dates from oldest to newest. -sorts text in reverse alphabetical order from Z to A, numbers from largest to smallest, and dates from newest to oldest.

Calculating the Standard Deviation

Standard deviation measures how far values in a dataset (or "population") are spread out from the mean (average). Excel offers two functions to calculate standard deviation. •STDEV.S calculates standard deviation for a set of values based on a sample from the population •STDEV.P calculates standard deviation using the entire set of values as the argument. In the figure below, the calculation in cell H4 uses the STDEV.S function while the calculation in cell H5 uses the STDEV.P function. When calculating standard deviation, be sure to select the correct function for your dataset.

Straight Line

Straight-line depreciation is the simplest depreciation to figure: (the cost of the asset minus the salvage value) divided by the life of the asset (in years). Because straight-line depreciation is the same for every period in the depreciation schedule, there is no need for a period argument. To calculate straight-line depreciation, use the SLN function. =SLN(Cost,Salvage,Life)=SLN(Cost,Salvage,Life)

Digital Privacy Task Force

Tasked with creating legislation around information privacy: •Individuals •Education •Business •Law Enforcement

Project

Temporary activities undertaken to create a unique product or service

Formatting Text Using Functions

Text functions are useful for ensuring that text data have a consistent appearance. In functions, text is referred to as a string or text string. Commonly used text functions are: •PROPER—Converts the text string to proper case (the first letter in each word is capitalized). •UPPER—Converts the text string to all uppercase letters. •LOWER—Converts the text string to all lowercase letters.

Calculating Averages

The AVERAGE statistical function is used to calculate the average value of a group of values. Average is calculated by adding the values, and then dividing the sum by the number of values. A formula using the AVERAGE function looks like this: =AVERAGE(B12:D12) The result of this formula is the sum of the values in cells B12:D12 divided by the number of values in that cell range.

Using the Logical Function IF

The IF logical function returns one value if a condition is true and another value if the condition is false. The IF function can return a numerical value or display a text string.

Using the IFS Function

The IFS function allows you to evaluate multiple logical tests in a single function. In the figure at right, the IFS function used in cell H2 has three logical tests. •If the first logical test is true, the cell will display Order Closed. If not, then the second logical test will be evaluated. •If true, then the cell will display Order Short. If not, then the third logical test will be evaluated. •If true, the cell will display Invoice Due. =IFS(F2=TRUE,"Order Closed",D2<C2,"Order Short",E2="no","Invoice Due")

Updating Named Ranges with the Name Manager

The Name Manager lists all the named ranges used in your workbook, the current value for each, the cells to which the name refers (including the sheet name), the scope of the name (whether it is limited to a specific worksheet or applies to the entire workbook), and comments (if there are any). To open the Name Manager, on the Formulas tab, in the Defined Names group, click the Name Manager button.

Project management

The application of knowledge, skills, tools, and techniques to project activities to meet project requirements management offers a strategic framework for coordinating the numerous activities associated with organizational projects

Creating Formulas Using Counting Functions

The counting functions are useful when you need to know how many numbers or items are in a list or how many rows are missing data for a particular column. COUNT—Counts the number of cells that contain numbers within a specified range of cells. COUNTA—Counts the number of cells that are not blank within a specified range of cells. Use COUNTA if your cell range includes text data or a mix of text and numbers.

The Cost of Fixing Errors

The later in the SDLC an error is found the more expensive it is to fix! An error found during the analysis and design phase is relatively inexpensive to fix All that is typically required is a change to a Word document However, exactly the same error found during the testing or implementation phase is going to cost the organization an enormous amount to fix because it has to change the actual system

Copyright

The legal protection afforded an expression of an idea, such as a song, video game, and some types of proprietary documents

Exploring More Logical Functions: AND and OR

The logical functions designed specifically to return a TRUE or FALSE response are the AND and OR functions. There are multiple correct approaches to asking questions about your data. How you pose the question determines which function you should use to answer it—AND or OR? •If you are looking for a true value to multiple conditions, use AND. •If you are looking for a true value to any one of multiple conditions, use OR. The logical function AND returns TRUE if all the arguments are true, and FALSE if at least one of the arguments is false.

Systems development life cycle (SDLC)

The overall process for developing information systems from planning and analysis through implementation and maintenance 1) Planning 2) Analysis 3) Design 4) Development 5) Testing 6) Implementation 7) Maintenance

The Internet.

The pervasive nature of the Internet as an effective sales channel has allowed clients to become more comfortable with outsourcing. Barriers to entry, such as lack of capital, are dramatically reduced in the world of ebusiness due to the Internet. New competitors enter the market daily.

Information security

The protection of information from accidental or intentional misuse by persons inside or outside an organization

Privacy

The right to be left alone when you want to be, to have control over your own personal possessions, and not to be observed without your consent a major ethical issue Without privacy, there will not be any trust

Pirated software

The unauthorized use, duplication, distribution, or sale of copyrighted software

Using CONCAT to Combine Text

To concatenate means to link items together. You can use the CONCAT function to combine the text values of cells or cell ranges. The formula looks like this: =CONCAT(B2," ", C2) The argument in the middle (" ") places a one-space text string between the values of cells B2 and C2.

Converting Tables to Ranges

To convert a table to a normal range: 1.On the Table Tools Design tab, in the Tools group, click the Convert to Range button. 2.Excel displays a message box asking if you want to convert the table to a normal range. Click Yes When you convert from a table to a normal range, Excel removes any filtering that may have been applied. Any table formatting such as banding and the Total row are still applied to the range.

Printing Selections, Worksheets, and Workbooks

To print only part of a worksheet or the entire workbook at once. 1.Click the File tab to open Backstage view. 2.Click Print. 3.In the Settings section, the first button displays which part of the workbook will print. By default, Print Active Sheets is selected. To change the print selection, click the button, and then click one of the other options: -Print Entire Workbook—Prints all the sheets in the workbook. -Print Selection—Prints only the selected cells in the active worksheet, overriding any print area definitions in the active worksheet. -Print Selected Table—Prints the table only (available only if the current selection is within a defined table). 4.If you want to ignore the defined print area, click Ignore Print Area at the bottom of the list. Click the Print button to print. Click OK.

Creating Formulas Referencing Data from Other Worksheets

To specify a cell location in another worksheet, begin the reference with the name of the worksheet followed by an exclamation point ! and then the cell reference. To include a reference to a cell from another sheet in your workbook: 1.Click the cell where you want the formula. 2.Type: = 3.Navigate to the cell you want to reference by clicking the sheet tab and then clicking the cell. Notice Excel adds the worksheet name to the cell reference for you. Press Enter to complete the formula.

Information technology monitoring

Tracks people's activities by such measures as number of keystrokes, error rate, and number of transactions processed

Spam

Unsolicited email

In-sourcing (in-house-development)

Uses the professional expertise within an organization to develop and maintain its information technology systems has been instrumental in creating a viable supply of IT professionals and in fact in creating a better quality workforce combining both technical and business skills

Offshore outsourcing

Using organizations from developing countries to write code and develop systems

Inserting Worksheets

When you create a new workbook, it contains a single worksheet named Sheet1. If you need more than one worksheet, you can add more. •To add a new worksheet to the right of the active worksheet, click the New sheet button to the right of the last worksheet tab. •To add a worksheet to the left of the active worksheet, on the Home tab, in the Cells group, click the Insert button arrow, and select Insert Sheet.

Hiding and Unhiding Rows and Columns

When you hide a row or column, the data still remain in your workbook, but they are no longer displayed on-screen and are not part of the printed workbook. To hide a row or column: 1.Select any cell in the row or column you want to hide. 2.On the Home tab, in the Cells group, click the Format button. 3.Point to Hide & Unhide, and click Hide Rows or Hide Columns. To unhide a row or column: 1.Select the rows or columns on either side of the row or column you want to unhide. 2.On the Home tab, in the Cells group, click the Format button. 3.Point to Hide & Unhide, and click Unhide Rows or Unhide Columns.

Chart Sheet

When you use the Move Chart dialog to move a chart to its own sheet, Excel creates a special type of worksheet called a chart sheet. The chart sheet does not include the columns, rows, and cells you are used to seeing in Excel; it contains only the chart object.

Naming Ranges of Cells

You can assign names to cells or ranges of cells to give your cell references names that are more user-friendly. These names (also called range names or named ranges) act as a list of shortcuts to the cell locations. To create a named range: 1.Select the cell or range of cells to which you want to assign a name. 2.Type the name in the Name box to the left of the formula bar. 3.Press Enter to apply the name to the cell(s).

Moving and Copying Worksheets

You can move worksheets around in a workbook, rearranging them into the most logical order. To move a worksheet within a workbook: 1.Click the worksheet tab and hold down the mouse button. 2.Notice that the mouse pointer changes to the shape. 3.Drag the mouse cursor to the position where you want to move the sheet, and release the mouse button. Excel places a small black triangle to let you know where the sheet will be placed.

To manually insert a new page break:

You may want to control where page breaks happen so your worksheet prints in a more logical order. 1.From Normal view or Page Break Preview view, begin by selecting the cell below and to the right of where you want the new page break. 2.On the Page Layout tab, in the Page Setup group, click the Breaks button. 3.Click Insert Page Break. 4.A new page break is inserted to the left of the selected column or above the selected row.

Workplace monitoring

a concern for many employees Organizations can be held financially responsible for their employees' actions The dilemma surrounding employee monitoring in the workplace is that an organization is placing itself at risk if it fails to monitor its employees, however, some people feel that monitoring employees is unethical It is difficult to determine when employee monitoring crosses the ethical lines

Critical path

a path from the start to the finish that passes through all the tasks that are critical to completing the project in the shortest amount of time

Gantt chart

a simple bar chart that depicts project tasks against a calendar is one of the most common tools used by project managers MS Project offers many additional tracking components such as Actual Start date, Budget, and Percent Complete the three interdependent project management tasks? Resources Timeframe Scope, tasks

Often, you will think to add IFERROR only after you notice that the initial formula results in errors. In these cases, it is easier to edit the formula directly in the formula bar or the cell

adding IFERROR and the Value_if_error argument without going through the Function Arguments dialog. 1.Click in the formula bar or double-click the cell to enter Edit mode. 2.Move the cursor to the beginning of the formula after the = and type: IFERROR( 3.The existing formula is the Value argument. Move the cursor to the end of the formula, type a comma, and then type the text string, value, or formula to use as the Value_if_error argument. If the Value_if_error argument is a text string, remember to enclose it in quotation marks. 4.Type the closing parenthesis )and press [Enter].

Margins

are the blank spaces at the top, bottom, left, and right of a printed page. •Normal—Uses Excel's default margins: 0.75 inch for the top and bottom and 0.7 inch for the left and right. •Wide—Adds more space at the top, bottom, left, and right sides. •Narrow—Reduces the amount of space at the top, bottom, left, and right sides, so more of your worksheet fits on each printed page.

Alpha Testing

assess if the entire system meets the design requirements of the users

Black-hat hackers

break into other peoples' computer systems and may just look around or may steal and destroy information

Extreme programming (XP) methodology

breaks a project into tiny phases, and developers cannot continue on to the next phase until the first phase is complete The primary difference between the waterfall and XP methodologies is that XP divides its phases into iterations with user feedback

project manager

bring enormous benefits to an organization such as reduced project expense, high company morale, and quicker time to market. A competent project manager sets the correct expectations early in the project with achievable milestones

Benjamin Franklin's timeless advice applies to software development projects

by failing to plan, you plan to fail

Line Charts

charts feature a line connecting each data point—showing the movement of values over time. Line charts work best when data trends over time are important. To add a line chart: 1.Select the data you want to include in the line chart. Be sure to include both values and the related cells that represent time segments (dates, calendar quarters, etc.). 2.On the Insert tab, in the Charts group, click the Insert Line Chart button. 3.Click the chart type you want to insert the chart into the worksheet. 4.If Excel does not display the time categories along the x axis, switch the data series and the categories. On the Chart Tools Design tab, in the Data group, click the Switch Row/Column button.

To change the title text

click the box to select it and edit the text directly in the text box. You can also type new text in the formula bar and then press Enter

user acceptance testing

determine if the system satisfies the user and business requirements

The Project Management Institute (PMI)

develops procedures and concepts necessary to support the profession of project management (www.pmi.org) and has three areas of focus: 1.The distinguishing characteristics of a practicing professional (ethics) 2.The content and structure of the profession's body of knowledge (standards) 3.Recognition of professional attainment (accreditation)

Hactivists

have philosophical and political reasons for breaking into systems and will often deface the website as a protest

PERT (Program Evaluation and Review Technique) chart

is a graphical network model that depicts a project's tasks and the relationships between those tasks (Dependencies and Critical paths are found in PERT charts)

Formula Auto complete

is a shortcut for entering functions. When you type = and then a letter, Formula AutoComplete displays a list of potential matches (functions and other valid reference names). This is a good method to use if you prefer typing the function arguments, but you need a reminder of what the arguments are or the order in which they should be entered.

Chart Titlr

is a text box above or overlaying the chart.

theme

is a unified color, font, and effects scheme. When you create a new blank workbook in Excel 2016, the Office theme is applied by default.

Analysts predict investment in MIS projects worldwide

is over $1 trillion 70 percent will be lost due to failed projects According to the Standish Group, just 29 percent of IT projects were completed on time, within budget, and with features and functions originally specified by the customer to deliver business value. Every day, organizations adopt projects that do not align with mission-critical initiatives; they overcommit financial and human capital; they sign off on low-value projects that consume valuable and scarce resources; and they agree to support projects that are poorly defined from requirements to planning.

Bar charts

like column charts turned on their side. The categories are displayed on the vertical axis, and the data point values are plotted along the horizontal axis. To insert a bar chart: 1.Select the data you want to include in the bar chart. 2.On the Insert tab, in the Charts group, click the Insert Column or Bar Chart button. 3.Click the chart type you want to insert the chart into the worksheet. If you don't like the way Excel grouped the data series, you can switch them, making the categories the data series and the data series the categories. On the Chart Tools Design tab, in the Data group, click the Switch Row/Column button.

Headings

numbers at the left of rows and the letters at the top of columns.

Feature creep

occurs when extra features are added example of feature creep) We would like a new logo placed on the top corner of every screen and it should play a song when clicked

Scope creep

occurs when the scope increases example of scope creep) We added a new type of discount to our marketing plan

To clear the filter

on the Data tab, in the Sort & Filter group, click the Clear button. You can also click the funnel icon at the top of the filtered column and select Clear Filter From "Name of Column".

Outsourcing Models

onshore, nearshore, offshore

Ranking

refers to the sort order of a value relative to all the other values in the dataset. Excel includes two functions to calculate rank. If the dataset includes duplicate values, and you want the lowest ranking for that value, use the function RANK.EQ. If you want the average ranking for that value, use RANK.AVG instead. •RANK.EQ and RANK.AVG both take two required arguments and one optional argument. •Number—the number, cell reference, or formula for which you want to find the ranking. •Ref—the cell range of the list within which the Number argument should be ranked. Order (optional)—tells Excel which way to sort the list to determine ranking.

Pie Charts

represent data as parts of a whole. Pie charts work best when you want to evaluate values as they relate to a total value. To add a pie chart: 1.Select the data you want to include in the pie chart. 2.On the Insert tab, in the Charts group, click the Insert Pie Chart button. 3.Click the chart type you want to insert the chart into the worksheet.

Cyberterrorist

seek to cause harm to people or to destroy critical systems or information and use the Internet as a weapon of mass destruction

SCRUM

software development is broken into small individual pieces with different teams of no more than 10 developers taking on different parts. Periodic SCRUM meetings brings all teams together. Uses small teams to produce small pieces of deliverable software using sprints, or 30-day intervals, to achieve an appointed goal Under this methodology, each day ends or begins with a stand-up meeting to monitor and control the development effort Scrum is a type of Agile Development

unethical use of information

typically occurs "unintentionally" when it is used for new purposes

Integration Testing

verify that separate systems can work together passing data back and forth correctly

system testing

verify that the units or pieces of code function correctly when integrated

White-hat hackers

work at the request of the system owners to find system vulnerabilities and plug the holes

Pivot Tables

•A PivotTable is a special report view that summarizes data and calculates the intersecting totals. •PivotTables do not contain any data themselves—they summarize data from a cell range or a table in another part of your workbook.

All the database functions require the same arguments:

•Database—the data arranged in rows and columns with column labels. •Field—the column to use in the calculation. The column can be identified by the column number in the database array or by the column label enclosed in quotation marks. •Criteria—the cell range defining the conditions the data must meet in order to be included in the calculation. -The criteria range must have at least two rows. -The first row includes labels that match the column labels in the database range. You need to include only the columns for which you will enter criteria. -The second row is where you enter the criteria. Criteria can be numeric values, expressions, or text.

nested formulas or nested functions

•Rounding functions are often used in conjunction with another function. •These formulas are referred to as nested formulas or nested functions because one function is "nested" within the other function as shown in the figure at right. Notice in the function in cell B10, the Num_digits argument is set to 0 to round to the nearest whole number. =ROUND(AVERAGE(G1:G13),0) When ROUND is used in a nested formula, the Number argument contains the formula you want to calculate.

Primary reasons for project failure:

•Unclear or missing business requirements •Skipping SDLC phases •Failure to manage project scope -Scope creep -Feature creep •Failure to manage project plan •Changing technology

To freeze part of the worksheet:

1.Arrange the worksheet so the row you want to be visible is the top row or the column you want is the first column visible at the left. 2.On the View tab, in the Window group, click the Freeze Panes button. -If you want the first row to always be visible, click Freeze Top Row. -If you want the first column to always be visible, click Freeze First Column.

To hide or show chart elements:

1.Click any empty area of the chart area to select the chart. 2.Click the Chart Elements button that appears near the upper right corner of the chart. 3.Click the check boxes to show or hide chart elements. 4.To select a specific option for a chart element, click the arrow that appears at the right side of the element name, and then click an option.

To change the calculation type for a field:

1.Click anywhere in the field you want to change. 2.On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. 3.In the Value Field Settings dialog, Summarize Values By tab, select the function for the type of calculation you want. Click OK.

The easiest way to add ROUND to an existing formula is to edit the formula directly in the formula bar or the cell, adding ROUND and the Num_digits argument without going through the Function Arguments dialog.

1.Click in the formula bar or double-click the cell to enter Edit mode. 2.Move the cursor to the beginning of the formula after the = and type: ROUND( 3.The existing formula is the Number argument. Move the cursor to the end of the formula, type a comma, and then type the number to use as the Num_digits argument. 4.Type the closing parenthesis ) and press [Enter].

To filter data:

1.Click the arrow at the top of the column that contains the data you want to filter for. 2.At first, all the filter options are checked. Click the (Select All) check box to remove all the checkmarks. 3.Click the check box or check boxes in front of the values you want to filter by. 4.Click OK. Excel displays only the rows that include the values you specified.

To split the worksheet view:

1.Click the cell in the worksheet where you would like to split the view. You can select an entire row or column as the split point. -If you want to split the worksheet into two horizontal panes, click a cell in column A. -If you want to split the worksheet into two vertical panes, click a cell in row 1. -If you want to split the worksheet into four panes, click any cell in the worksheet. The cell you selected will be the top left cell in the pane in the lower-right quadrant. 2.On the View tab, in the Window group, click the Split button.

To create a copy of a worksheet:

1.Click the worksheet tab, hold down the mouse button, and press and hold Ctrl . 2.Notice that the mouse pointer changes to the shape. 3.Drag the mouse cursor to the position where you want to insert a copy of the selected sheet, and release the mouse button.

To calculate the mean absolute deviation:

1.First calculate the absolute value for each item in the dataset. Type = ABS( and then enter the cell reference or formula for the Number argument. Type ) and press [Enter]. 2.Copy the formula as necessary to generate all the absolute values. 3.Once you have a list of absolute values for the dataset, create a formula using the AVERAGE function. Use the list of absolute values as the Number1 argument.

To create a formula using one of the database functions:

1.First, set up the criteria range. If you place the criteria range above the database range, include a blank row between the two ranges. 2.Enter the criteria in the blank row below the appropriate column heading. 3.After setting up the criteria range, select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the Insert Function button to open the Insert Function dialog. (Database functions are not accessible from any of the Ribbon buttons.) 4.If necessary, expand the Or select a category list and select Database. 5.Select the function you want, and then click OK to open the Function Arguments dialog. (continued) 6.In the Database box, enter the range of cells or the name for the data array. Include the heading row. 7.In the Field box, enter the column number or the column label of the column containing the data to use in the calculation. 8.In the Criteria box, enter the range of cells or the name for the criteria range. Click OK.

To create a depreciation schedule using one of the depreciation functions:

1.First, set up the values for the arguments. Create cells containing the cost, salvage, and life values. Ideally, name the cells the same as the arguments—Cost, Salvage, and Life. This will make it easier to copy the formulas to the remaining cells in the schedule. 2.List the periods (year numbers) in a column. The depreciation formula will be entered in the column to the right of the period column. 3.Click the cell immediately to the right of the period 1 cell. On the Formulas tab, in the Function Library group, click the Financial button, and then click the depreciation function you want to use. 4.Enter absolute cell references or names for the Cost, Salvage, and Life arguments. If you selected SLN as the depreciation function, skip to step 9. 5.Enter a relative cell reference for the Per or Period argument. Use the first cell in the list of period numbers. (continued) 6.If you selected the DB function, and period 1 is a partial period, enter the number of months the item was in use during period 1 in the Month argument. 7.If you are using DDB, and the multiplier is something other than 2, enter the multiplier in the Factor argument. 8.Click OK. 9.Copy the formula to the remaining cells in the depreciation schedule. -If you are using DB, copy the formula to the cell for period 2, remove the Month argument, and then copy the formula to the remaining cells in the schedule. -If you are using SLN, the depreciation schedule will have the same value for every period.

To create a one-variable data table with a column input format:

1.In a column, type the series of values you want to substitute for the variable in your formula. 2.In the cell above and to the right of the first value, type the formula that references the cell you want to replace with the new values. 3.Select the cell range for the data table, beginning with the empty cell above the first value you entered. 4.On the Data tab, in the Forecast group, click the What-If Analysis button, and click Data Table... 5.In the Data Table dialog, the input cell is the cell that contains the original value for which you want to substitute the data table values. In this data table, the output values will be listed in a column, so enter the cell reference in the Column input cell box. 6.Click OK.

To find a value using Goal Seek:

1.On the Data tab, in the Forecast group, click the What-If Analysis button, and then click Goal Seek... 2.Enter the outcome cell in the Set cell box. This cell must contain a formula. 3.Enter the outcome value you want in the To value box. 4.Enter the input cell (the cell that contains the value to be changed) in the By changing cell box. This cell must be referenced in the formula in the outcome cell either directly or indirectly and must contain a value, not a formula. 5.Click OK. 6.The Goal Seek Status box appears, letting you know if Goal Seek was able to find a solution. Click OK to accept the solution and change the value in the input cell or click Cancel to return the input cell to its original value.

To use error checking to find errors in your worksheet:

1.On the Formulas tab in the Formula Auditing group, click the Error Checking button. 2.The Error Checking dialog displays information about the first error. The buttons available in the dialog will differ, depending on the type of error found. -If Excel is able to offer a solution to the error, the dialog will include a button to accept the suggested fix. -Click the Help on this error button to open Microsoft Office Help. -Click Ignore Error to dismiss the error. Excel will ignore this error until you manually reset ignored errors through Excel Options. -Click Edit in Formula Bar to fix the error manually. 3.Click the Next button to see the next error in your worksheet. 4.When you have reviewed all errors, Excel displays a message that the error check is complete. Click OK to dismiss the message box.

To create a formula using IFS:

1.On the Formulas tab, in the Function Library group, click the Logical button and select IFS. 2.In the Logical_test1 argument box, enter the first condition for which you want to test. 3.In the Value_if_true1 argument box, enter the text string or value that will be displayed or the formula that will be calculated if the Logical_test1 argument is true. 4.In the Logical_test2 argument box, enter the condition to evaluate if the Logical_test1 argument is false. 5.In the Value_if_true2 argument box, enter the text string or value that will be displayed or the formula that will be calculated if the Logical_test2 argument is true. 6.Repeat steps 4-5 until you have covered all the conditions for which you want to test. 7.Click OK to complete the formula.

To use the Function Arguments dialog to create a formula using IFERROR:

1.On the Formulas tab, in the Function Library group, click the Logical button, and select IFERROR. 2.In the Value argument box, enter the formula you want to calculate. 3.In the Value_if_error argument box, enter the text string, value, or formula the IFERROR formula should use if Excel is unable to compute a value for the formula in the Value argument. 4.Click OK

To create a formula using one of the rounding functions:

1.On the Formulas tab, in the Function Library group, click the Math & Trig button. 2.Select the rounding function you want to open the Function Arguments dialog. 3.In the Number argument box, enter the number, cell reference, or formula you want rounded. 4.In the Num_digits argument box, enter the number of digits you want to the right of the decimal. Click OK.

To create a formula using SUMPRODUCT:

1.On the Formulas tab, in the Function Library group, click the Math & Trig button. Select SUMPRODUCT to open the Function Arguments dialog. 2.In the Array1 argument box, enter the first cell range or named range. 3.In the Array2 argument box, enter the cell range or named range you want to multiply the values in Array1 by. 4.Continue entering arrays as necessary. 5.Click OK.

To calculate the rank of a value in a list of values:

1.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select RANK.EQ or RANK.AVG. 2.In the Number argument box, enter the cell reference or number for which you want to find the ranking. 3.In the Ref argument box, enter the cell range or named range for the array of values. 4.The Order argument is optional. By default, Excel will calculate the ranking based on sorting all the values in descending order (so the highest value = 1). If you want to find the rank in the opposite order, enter 1 in the Order argument. 5.Click OK.

To calculate standard deviation using the entire data population:

1.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select STDEV.P. 2.In the Number1 argument box, enter the cell range or named range for the data. 3.If you want the calculation to use multiple noncontiguous cell ranges, enter additional arguments. Click OK.

To calculate standard deviation when only a sample of the data is available:

1.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select STDEV.S. 2.In the Number1 argument box, enter the cell range or named range for the sample data. 3.If you want the calculation to use multiple noncontiguous cell ranges, enter additional arguments. 4.Click OK.

You can set the worksheet orientation from the Page Layout tab on the Ribbon:

1.On the Page Layout tab, in the Page Setup group, click the Orientation button. 2.Click the Portrait or Landscape option. You can also change the worksheet orientation when you print: 1.Click the File tab to open Backstage view. 2.Click Print. 3.In the Settings section, click the button displaying the current orientation setting, and then click the orientation setting you want.

To add a header or footer to a worksheet using the Page Setup dialog:

1.On the Page Layout tab, in the Page Setup group, click the Page Setup Dialog Launcher to open the Page Setup dialog. 2.Click the Header/Footer tab. 3.Click the arrow beneath the Header or Footer area to expand the list of predefined header/footer options. Click the option you want to use. Click OK.

To insert a column:

1.Place your cursor in a cell in the column to the right of where you want the new column. 2.On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Columns. The new column will appear to the left of the selected cell

To delete a column:

1.Place your cursor in a cell in the column you want to delete. 2.On the Home tab, in the Cells group, click the Delete button arrow and select Delete Sheet Columns. 3.The column will be deleted, and columns to the right of the deleted column will shift left.

To delete a row:

1.Place your cursor in a cell in the row you want to delete. 2.On the Home tab, in the Cells group, click the Delete button arrow and select Delete Sheet Rows. 3.The row will be deleted and the rows below it will shift up.

To move or copy a worksheet to another workbook or to a new workbook:

1.Right-click the sheet tab, and select Move or Copy... to open the Move or Copy dialog. 2.In the Move or Copy dialog, expand the To book list at the top of the dialog. The To book list shows all the Excel workbooks you have open. Click the workbook you want. To move or copy the sheet to a new blank workbook, select (new book). 3.The list of sheets in the Before sheet box will update to show the sheets available in the workbook you selected. Click the name of the sheet you want to move the selected sheet before. If you want to move the sheet to the end of the workbook, select (move to end) in the Before sheet box. 4.If you want to create a copy of the selected sheet, instead of moving the original, click the Create a copy check box. Click OK.

To remove duplicate rows from a table:

1.Select any cell in the table. 2.On Table Tools Design tab, in the Tools group, click the Remove Duplicates button. 3.By default, all the columns are selected in the Remove Duplicates dialog and Excel will remove duplicates only where the data in the rows are 100 percent identical. To identify duplicate rows where only some of the columns have duplicate data, click the check boxes to uncheck column names. 4.Click OK to remove duplicate rows from the table. 5.Excel displays a message box, telling you how many duplicate rows were found and removed and how many unique values remain in the table. Click OK to dismiss the message box.

The easiest way to begin a new PivotTable is to use one of the recommended PivotTables:

1.Select any cell within the table or cell range you want to use for your PivotTable. 2.On the Insert tab, in the Tables group, click the Recommended PivotTables button. 3.Preview each of the options in the Recommended PivotTables dialog. Click the one that is closest to how you want your final PivotTable to look. 4.Click OK to create the PivotTable in a new worksheet. Use the PivotTable Fields pane to review and modify the structure of the PivotTable. In the figure at right, the recommended PivotTable added the PO # field to the Rows section and the Received field to the Values section. The PivotTable summarizes values in the Received field for each value in the PO # field. In the PivotTable Fields pane, check the boxes in from of each column name to add or remove fields from the PivotTable.

To use the NPV function:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Financial button. Click NPV to open the Function Arguments dialog. 3.In the Rate box, enter the interest rate. The NPV Function Arguments dialog calls this the discount rate. (continued) 4.The remaining arguments are the payments (negative values) and income (positive values). -NPV accepts up to 254 Value arguments. -You can use cell ranges, named ranges, or individual cell references. -If you use a cell range or named range, verify that they do not include blank cells—they will be ignored and the NPV calculation will be wrong. 5.Click OK.

To create a formula using PV:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Financial button. Click PV to open the Function Arguments dialog. 3.Enter the arguments. -In our example, the payments will be monthly, so the annual interest rate (Rate) must be divided by 12 and the number of payout years (Nper) must be multiplied by 12 as shown in the following figure. -The Pmt argument includes a hyphen before the cell reference to make it negative. -The Fv argument is omitted, but payments will be made at the beginning of every period, so the Type argument must be set to 1. If this argument is omitted, Excel assumes a value of 0. 4.Click OK.

To create a formula using the IF function

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Logical button. . I 3.Select IF to open the Function Arguments dialog. 4.Enter the Logical_test argument. 5.Enter the Value_if_true argument. 6.Enter the Value_if_false argument. 7.Click OK.

To create a formula using the AND function:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Logical button. Select AND to open the Function Arguments dialog. 3.Enter the first comparison expression in the Logical1 box. Enter additional arguments as necessary. The AND function can take up to 255 arguments. Remember, all the arguments must be true in order for the AND function to return TRUE. 4.Click OK.

To create a formula using the OR function:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Logical button. Select OR to open the Function Arguments dialog. 3.Enter the first comparison expression in the Logical1 box. Enter additional arguments as necessary. The OR function can take up to 255 arguments. Click OK

To create a formula using INDEX:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select INDEX to open the Function Arguments dialog. 3.There are two possible arguments lists for the INDEX function. Select the array,row_num,column_num option. Click OK. (continued) 4.In the Array box, enter the range of cells or the name for the entire data array. 5.In the Row_num box, enter the reference to the cell that contains the row position you want to look up in the array. In this example, that is the cell containing the MATCH formula. 6.In the Column_num box, enter the number of the column that contains the data you want displayed in the formula results. Click OK.

To create a formula using MATCH:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select MATCH to open the Function Arguments dialog. 3.Enter the Lookup_value argument. The preceding figure allows the workbook user to enter an item name in cell B1. The Lookup_value argument then references that cell as shown in the next figure, which shows the Function Arguments dialog. (continued) 4.Enter the Lookup_array argument. In the next figure, this argument uses a named range that refers to a vertical range of cells. 5.If necessary, enter the Match_type argument box. In this example, we want to ensure an exact match, so 0 is entered as the Match_type argument. Click OK.

AverageIf

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Math & Trig button and select SUMIFS to open the Function Arguments dialog. 3.In the Sum_range argument box, enter the cell range or range name containing the values to be summed if all the criteria are met. 4.In the Criteria_range1 box, enter the cell range or range name containing the values to be evaluated against the first criteria. Text strings and expressions must be enclosed in quotation marks. 5.In the Criteria1 argument box, enter the text string, number, expression, or cell reference for the first criteria. 6.In the Criteria_range2 box, enter the cell range or range name containing the values to be evaluated against the second criteria. (continued)

To create a formula using SUMIFS:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Math & Trig button, and select SUMIF to open the Function Arguments dialog. 3.In the Range argument box, enter the cell range or named range to evaluate against the criteria. 4.In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. Text strings and expressions must be enclosed in quotation marks. 5.In the Sum_range argument box, enter the cell range or named range containing the values you want to add together. If you omit this argument, SUMIF will total the values in the Range argument instead. 6.Click OK.

To create a formula using AVERAGEIF:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select AVERAGEIF to open the Function Arguments dialog. 3.In the Range argument box, enter the cell range or named range to evaluate against the criteria. 4.In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. 5.In the Average_range argument box, enter the cell range or named range containing the values you want to average. If you omit this argument, AVERAGEIF will average the values in the Range argument instead. 6.Click OK.

To create a formula using AVERAGEIFS:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select AVERAGEIFS to open the Function Arguments dialog. 3.In the Average_range argument box, enter the cell range or range name containing the values to be averaged if all the criteria are met. 4.In the Criteria_range1 box, enter the cell range or range name containing the values to be evaluated against the first criteria. 5.In the Criteria1 argument box, enter the text string, number, expression, or cell reference for the first criteria. Remember, text strings and expressions must be enclosed in quotation marks. 6.In the Criteria_range2 box, enter the cell range or range name containing the values to be evaluated against the second criteria. 7.In the Criteria2 argument box, enter the text string, number, expression, or cell reference for the second criteria. Remember, text strings and expressions must be enclosed in quotation marks. 8.Continue entering the criteria range and criteria pairs until you are finished. 9.Click OK.

To create a formula using COUNTIF

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select COUNTIF to open the Function Arguments dialog. 3.In the Range argument box, enter the cell range or named range to evaluate against the criteria. 4.In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. Remember, text strings and expressions must be enclosed in quotation marks. Click OK.

To create a formula using COUNTIFS:

1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select COUNTIFS to open the Function Arguments dialog. 3.In the Criteria_range1 box, enter the cell range or range name containing the values to be evaluated against the first criteria. 4.In the Criteria1 argument box, enter the text string, number, expression, or cell reference for the first criteria. (continued) 5.In the Criteria_range2 box, enter the cell range or range name containing the values to be evaluated against the second criteria. 6.In the Criteria2 argument box, enter the text string, number, expression, or cell reference for the second criteria. 7.Continue entering the criteria range and criteria pairs until you are finished. Click OK.

To move a chart to a new position on the worksheet:

1.Select the chart. Be careful not to click in the plot area or you will move just the plot area instead of the entire chart. Your mouse cursor will change to the move cursor. 2.With your left mouse button depressed, drag the chart to the new location on the worksheet, and then release the mouse button to "drop" the chart at the new location.

If none of the recommended charts presented in the Quick Analysis tool are precisely what you want, use the Insert Chart dialog instead:

1.Select the data for the chart. 2.Click the Quick Analysis Tool button. 3.Click the Charts tab, and then click the More Charts button to open the Insert Chart dialog. 4.The first tab in the Insert Chart dialog, Recommended Charts, displays the same chart options as the Charts tab in the Quick Analysis tool, plus a few more. When you select a chart type, a preview of the chart appears in the right pane of the dialog. 5.Click OK to insert the selected chart into the worksheet.

To add a recommended chart to a worksheet using the Quick Analysis tool:

1.Select the data you want to visualize as a chart. 2.The Quick Analysis Tool button appears near the lower right corner of the selected range. Click the Quick Analysis Tool button, and then click the Charts tab. 3.Hover the mouse cursor over each chart type to see a live preview of the chart. Click the button for the chart type you want.

To use the Evaluate Formula feature:

1.To evaluate a specific formula, click the cell that contains the formula you want to review. 2.On the Formulas tab, in the Formula Auditing group, click the Evaluate Formula button to open the Evaluate Formula dialog for the selected cell. -In the Evaluate Formula dialog, the current cell is identified at the left side of the dialog under Reference and the formula is listed in the Evaluation box. -In the Evaluation box, the first element to evaluate is underlined. This may be a cell reference, a named range, or a nested function. (continued) 3.If the underlined part of the formula references a cell, named range, or another formula, click the Step In button to review the details. (continued) 4.Click the Step Out button to return to the original formula. Notice that when you click Step Out, Evaluate Formula moves on to the next part of the formula for which you can click Evaluate or Step In. (continued) 5.Click the Evaluate button to replace the underlined part of the formula with its current value. -Once you click the Evaluate button, you cannot go back to review the previous part of the formula. If you find that you wanted to "step in" instead of evaluating that part of the formula, you'll need to click the Close button and reopen the Evaluate Formula dialog to start over. 6.Continue clicking Step Out and Evaluate until you have examined all parts of the formula, at which point you can click the Start Over button to start the evaluation process from the beginning. 7.Click the Close button to dismiss the Evaluate Formula dialog.

Filtering Chart Data

Excel includes a feature that allows you to hide and show data in a chart without having to modify the chart data source. To filter chart data: 1.Select the chart. 2.Click the Chart Filters button that appears near the upper right corner of the chart. 3.Notice that when you hover the mouse pointer over the chart filter options, the live preview does not take into account the options that are checked or unchecked. The preview shows what the chart would look like if the highlighted option were the only one checked by dimming all the other series or categories. 4.Click the check boxes to add or remove data series or categories from the chart. 5.Click the Apply button to apply the changes.

Working with Named Ranges

Rather than using a range of cells in your formulas, you can use a named range. The name will always refer to the cells, even if their position in the worksheet changes. To use a named range in a formula: 1.Click the cell where you want to enter the new formula. 2.Type the formula, substituting the range name for the cell references. 3.As you type alphabetical characters, Formula AutoComplete will offer name suggestions. If you see the name you want, double-click it and Excel will insert the name into the formula. Press Enter to accept the formula.

The Systems Development Life Cycle

Software that is built correctly can transform as the organization and its business transforms Software that effectively meets employee needs will help an organization become more productive and enhance decision making Software that does not meet employee needs may have a damaging effect on productivity and can even cause a business to fail Organizations must learn how to build and implement disruptive technologies, such as software for wireless devices, to remain competitive Software that is built correctly can support agile organizations and can transform as the organization and its business transforms Software that effectively meets employee needs will help an organization become more productive and enhance decision making Software that does not meet employee needs may have a damaging effect on productivity and can even cause a business to fail

Calculating Future Value with the FV Function

The FV (future value) function calculates the future value of an investment. For example, if you deposit the same amount of money every period at a steady interest rate, use FV to calculate the future value of your savings. The FV function has three required arguments and two optional arguments: •Rate—the interest rate. If the interest rate is an annual interest rate, it must be divided by the number of payment periods per year. •Nper—the total number of payments. •Pmt—the amount of each payment. As the payment is money going out, it is usually expressed as a negative number. •Pv—(optional) the present value or how much the investment is worth today. If there is no money in the investment vehicle prior to the first payment, you can omit this argument. When the Pv argument is empty, Excel assumes a value of 0. •Type—(optional) when the Type argument is empty, Excel assumes a value of 0 and calculates the result based on payments at the end of each period. Enter 1 for this argument if funds are added to the investment the beginning of each period.

MODE.MULT

The MODE.MULT function, used in the figure at right, populates cells H8:H9 (the results array) with the two mode values: 22 and 25. =MODE.MULT(Number1,[Number2]...)=MODE.MULT(C2:C22) •Notice that the formula is the same as the formula for MODE.SNGL except it is surrounded by braces. •The braces tell Excel that this is an array formula and that the selected cells should be populated with multiple values. Use one of these methods to enter the formula: A.Enter the formula by typing it directly in the formula bar. After typing the formula, press [Ctrl]+[Shift]+[Enter]. Using this keystroke combination instructs Excel to enter an array formula. Notice that Excel places braces around the formula. You cannot type the braces. You must use [Ctrl]+[Shift]+[Enter] after typing the formula. B.Enter the formula using the Function Arguments dialog. 1.On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and then click MODE.MULT. 2.Enter the cell range or range name to evaluate in the argument box. 3.Click OK. 4.Place the cursor in the formula bar and press [Ctrl]+[Shift]+[Enter] before clicking another cell or pressing any other keyboard key.

Calculating the Number of Payments with NPER

The NPER function calculates the number of payments available from a retirement account or due on a loan given a constant interest rate and payment amount. The NPER function has three required arguments and two optional arguments: •Rate—the interest rate. If the interest rate is an annual interest rate, it must be divided by the number of payment periods per year. •Pmt—the amount of each payment. As the payment is money going out, it is usually expressed as a negative number. •Pv—the value of the investment or loan today. (continued) •Fv—(optional) the amount of money left after the last payment is made (the future value). If the final balance in the account is expected to be zero, you can omit this argument. When the Fv argument is empty, Excel assumes a value of 0. •Type—(optional) enter 1 for this argument if the payment is made at the beginning of each period. When the Type argument is empty, Excel assumes a value of 0 and calculates the result based on payments at the end of each period. When paying yourself from a retirement account, the payment is usually made at the beginning of each period, so set Type to 1.

Using NPV to Calculate Present Value When Payments Are Variable

The NPV function is similar to PV, but it allows for variable payment amounts when calculating the present value of future payments. NPV can be especially useful for comparing two investment or payment options. The NPV function has two required arguments: •Rate—the discount rate—the interest rate that would be reasonably expected if the money were invested elsewhere. •Value—the payment amount both incoming (positive) and outgoing (negative). NPV takes up to 254 Value arguments (Value1, Value2, etc.). •NPV assumes payments are made over consistent periods of time and each payment comes at the end of the period. NPV ignores cells containing text and blank cells. If your argument range includes periods with no values, be sure to enter 0 instead of leaving the cells blank.

Finding Data Using the VLOOKUP Function

The VLOOKUP function finds a value or cell reference in a cell range and returns another value from the same row. To use the VLOOKUP function: 1.Select the cell where you want to enter the formula. 2.On the Formulas tab, in the Function Library group, click the Lookup & Reference button. 3.Select VLOOKUP from the list to open the Function Arguments dialog. Enter the Lookup_valueargument. Enter the cell reference for which you want to find a corresponding value. 5.Enter the Table_array argument. Enter the range of cells (or the range name) that contains the lookup data. 6.Enter the Col_index_num argument. This argument is the position of the column in the Table_array from which the function should return a matching value. 7.(optional) Enter the Range_lookup argument. Type 𝙵𝙰𝙻𝚂𝙴 if you want to find only an exact match for the value entered in the Lookup_value box. Click OK.

Finding the Most Common Value(s) with MODE.SNGL and MODE.MULT

The statistical mode is the value that appears most often in a group of values. In figure at right, two values appear six times in the cell range C2:C22: 22 and 25. The MODE.SNGL function returns a single mode value (the first mode value it finds). If there are no duplicate values, MODE.SNGL will return the error #N/A. =MODE.SNGL(Number1,[Number2]...)=MODE.SNGL(C2:C22)

Adding Total Rows to Tables

To add a Total row to a table: 1.On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box. 2.In the Total row at the bottom of the table, click the cell where you want to add the calculation. 3.Click the arrow, and select a function. Excel inserts the formula for you.

Changing the Color of Sheet Tabs

To change a worksheet tab color: 1.Right-click the sheet tab and point to Tab Color to display the color palette. 2.Hover the mouse pointer over each color to preview how the color will look when the worksheet is active. 3.Click the color you want.

Updating Named Ranges with the Name Manager

To change the cell or range of cells to which a name refers: 1.Open the Name Manager and select the name you want to modify. 2.Edit the cell references in the Refers to box. You can also click the Collapse Dialog button to hide the Name Manager, and then click and drag to select the new cell range. When you are finished, click the Expand Dialog button to display the Name Manager again. 3.Click the checkmark icon to the left of the Refers to box to accept the change. Click Close to close the Name Manager.

Changing the Chart Type

To change the chart type: 1.On the Chart Tools Design tab, in the Type group, click the Change Chart Type button. 2.In the Change Chart Type dialog, click a chart type category to display that category in the right pane. 3.Click one of the chart types along the top of the right pane to see previews of the options available, and then click the chart type you want. Click OK.

COUNTIFS

To count data that meet multiple criteria, use COUNTIFS. COUNTIFS takes arguments for up to 127 pairs of criteria ranges and criteria. The result of COUNTIFS is the average of all the cells that meet all criteria. COUNTIFS takes two required arguments and multiple optional arguments: •Criteria_range1—the range of cells containing the values to be evaluated against the first criteria •Criteria1—the first criteria •Criteria_range2—(optional) the range of cells containing the values to be evaluated against the second criteria •Criteria2—(optional) the second criteria (continuing up to Criteria_range127 and Criteria127)

Deleting Worksheets

To delete a worksheet: 1.Select the sheet you want to delete by clicking the worksheet tab. 2.On the Home tab, in the Cells group, click the Delete button arrow, and select Delete Sheet. 3.If you try to delete a sheet that contains data, Excel will display a warning that the sheet may contain data and ask if you are sure you want to permanently remove it from your workbook. Click the Delete button to continue and delete the worksheet. Be careful—you cannot undo the Delete Sheet command.

Naming Worksheets

To rename a worksheet: 1.Double-click the sheet tab or right-click the worksheet tab and select Rename. 2.Excel highlights the sheet name, allowing you to replace it as you type. 3.Type the new sheet name, and press Enter .

Finding the Middle Value with MEDIAN

When analyzing a list of values, calculating the statistical average may not always provide the information you need. The statistical median is the middle value of a set of values. If you were to sort the list of values from highest to lowest, you would find the value that is right in the middle. The formula in the following figure uses MEDIAN to find the middle value for the range C2:C22. =MEDIAN(Number1,[Number2]...)=MEDIAN(C2:C22)

Agile methodology

a form of XP, aims for customer satisfaction through early and continuous delivery of useful software components developed by an iterative process using the bare minimum requirements The Aglie Alliance is a group of software developers whose mission is to improve software development processes and whose manifesto includes the following: Satisfy the customer through early and continuous delivery of valuable software Welcome changing requirements, even late in development Business people and developers must work together daily throughout the project Build projects around motivated individuals The best architectures, requirements, and designs emerge from self-organizing teams At regular intervals, the team reflects on how to become more effective, then tunes and adjusts its behavior accordingly

Charts

a graphic that represents numeric data visually. •In a chart, the values selected in the worksheet, the data points, are transformed into graphic data markers. •Data markers can be columns, bars, pie pieces, lines, or other visual elements. •Related data points, usually in the same row or column, are grouped into a data series. Some chart types allow you to plot multiple data series.

Dependency

a logical relationship between the project tasks, or between a project task and a milestone Dependencies inform the project manager of tasks associated with or affected by another task

The mother of all functions

•=VLOOKUP($A2, BudgetedExpenses, MONTH(B$1)+1,FALSE)-SUMIFS(TransactionAmount, TransactionMonth, MONTH(B$1), TransactionCategory, $A2) •VLOOKUP -looks up what you budgeted for given budget category and month •SUMIFS -sums up all your transactions for that category and month •Subtracts actual spending from budget to see if you are over or under budget

Using Database Functions

•Database functions allow you to perform statistical analysis on data that meet specific criteria by building queries similar to those used when working with a database. •Excel includes database versions of many statistical functions you are already familiar with. They have the same names, with a D added to indicate database: DSUM, DAVERAGE, DMIN, DMAX, DCOUNT, DCOUNTA, and a few more.

Information Policy Examples

•Health Insurance Portability and Accountability Act of 1996 (HIPAA) •The Family Educational Rights and Privacy Act of 1974 (FERPA)

Calculating Totals with SUMPRODUCT

•In mathematics, a product is the result of multiplying two or more numbers. •The SUMPRODUCT function allows you to multiply the corresponding cells in two or more ranges and then sum (add together) the products. -This function is very useful when totaling columns of data such as hours worked and hourly rates or unit prices and number of units ordered. -To use SUMPRODUCT, arrays do not need to be next to each other, but they must have the same number of rows or columns. If the arrays are different sizes, the formula will return an error. The formula at right first multiplies each value in the range C4:C15 by its corresponding value in the range D4:D15 and then sums the products: =SUMPRODUCT(Array1,[Array2],[Array3]...) =SUMPRODUCT(C4:C15,D4:D15)

Digital property rights

•Licensing Agreements •Subscription Agreements •Digital Rights Management

The MATCH function has two required arguments and one optional argument:

•Lookup_value—the text, number, or logical value you want to match. •Lookup_array—the range of cells grouped in a single row or column that contains the value you want to look up. If your data are organized as a table, be careful not to include the header row or label column in the Lookup_array range. •Match_type—(optional) If you want to return only an exact match, you must enter 0 as the argument value. -If the lookup array is sorted from smallest to largest, enter 1 (or omit the argument) to find the first position where the value is less than or equal to the lookup value. -If the array is sorted from largest to smallest, enter -1 to find the first position where the value is greater than or equal to the lookup value.

common cyber attacks

•Malware •Phishing •Man-in-the-middle attack •Denial-of-service attack •SQL Injection

Managing Errors with the IFERROR Function

•Most of Excel's functions return an error when a value cannot be found or when the formula cannot be calculated. •Rather than displaying an error message to users, it may be more useful to display a text message or a specific value that you define. The IFERROR function performs this task. we can make the error message more user-friendly, as shown in the next figure. The IFERROR function takes two arguments. •Value—the formula to calculate. •Value_if_error—the value, text string, or formula to use if the formula in the Value argument results in an error. If you use a text string, enclose it in quotation marks. =IFERROR(Value,Value_if_error)=IFERROR(VLOOKUP(B1,Stock,3,FALSE),"item not found")

There are three main rounding functions. Each of these functions takes two arguments:

•Number—the number or formula to round. •Num_digits—the number of digits to the right of the decimal to round to. ROUND—Rounds the number up or down to the number of decimal places specified in the Num_digits argument. The rules of rounding state that if the number immediately to the right of the rounding point is 0, 1, 2, 3, or 4, the trailing digits are cut off. If the number immediately to the right of the rounding point is 5, 6, 7, 8, or 9, the number is rounded up. A formula using the ROUND function looks like this: =ROUND(Number,Num_digits) =ROUND(B1/B2,2)

Changing Worksheet Orientation

•Orientation refers to the direction the worksheet prints. It doesn't affect the way the worksheet looks on your computer screen. •The default print setting is for portrait orientation—when the height of the page is greater than the width (like a portrait hanging on a wall). •If your workbook is wide, you may want to use landscape orientation instead, where the width of the page is greater than the height.

Three Areas of Information Security

•People -Authentication -Authorization •Data -Prevention -Resistance •Systems -Detection -Response

Cybersecurity business precautions

•Pro-Active Cybersecurity Plan •Employee Training •Regularly Monitor and Update all Devices •Two-Factor Authentication •Password Locker •Anti-Spam Protection •Anti-Virus Protection •Backup Strategy •Move to the Cloud

The PV function has three required arguments and two optional arguments:

•Rate—the interest rate. If the interest rate is an annual interest rate, it must be divided by the number of payment periods per year. •Nper—the total number of payments. •Pmt—the amount of each payment. As the payment is money going out, it is usually expressed as a negative number. •Fv—(optional) the amount of money left after the last payment is made (the future value of the investment). If the final balance in the account is expected to be zero, you can omit this argument. When the Fv argument is empty, Excel assumes a value of 0. •Type—(optional) enter 1 for this argument if the payment is made at the beginning of each period. When the Type argument is empty, Excel assumes a value of 0 and calculates the result based on payments at the end of each period. When using PV to calculate the amount needed for annuity payments, the payment would generally be at the beginning of the period, so Type must be set to 1.

Finding Minimum and Maximum Values

•The MAX (maximum) statistical function will give you the largest value in a range of values. A formula using the MAX function looks like this: =MAX(A3:A6) •The MIN (minimum) statistical function will give you the smallest value in a range of values. A formula using the MIN function looks like this: =MIN(A3:A6)

Calculating Loan Payments Using the PMT Function

•The PMT function is based on constant payments and a constant interest rate. •To calculate a payment using PMT, you need three pieces of information: the interest rate (the Rate argument), the number of payments (the Nper argument), and the amount of the loan (the Pv argument). =PMT(Rate,Nper,Pv) •Because the result of the formula is a payment (money going out), it is expressed as a negative number. If you want the result expressed as a positive number instead, add a negative symbol before the loan amount (the Pv argument).

IFERROR is used in a nested formula.

•The first argument contains the formula you want to calculate. •The second argument is the text string, value, or formula to display if there is an error.

The Hackett Group analyzed 2,000 companies and discovered:

•Three in 10 major IT projects fail •About 20 percent of the companies state that they cannot adjust rapidly to market changes

To remove the tracer arrows from your worksheet, on the Formulas tab, in the Formula Auditing group, click the Remove Arrows button arrow and select the option you want:

•To remove the precedent tracer arrows, select Remove Precedent Arrows. •To remove the dependent tracer arrows, select Remove Dependent Arrows. •To remove all tracer arrows at once, select Remove Arrows. Clicking the Remove Arrows button instead of the button arrow will also remove all the tracer arrows.

Types of malware

•Virus •Adware •Spyware •Worms •Trojan Horse Ransomware

Number of Common Software Development Methodologies

•Waterfall •-Extreme Programming •Rapid Application Development (RAD) •Agile -SCRUM

Creating a Depreciation Schedule

•When a business purchases a significant asset such as equipment or computer software, generally accepted accounting principles (GAAP) state that the expense must be spread over the asset's useful lifetime. This is referred to as depreciation. •Depreciation over the life of an asset is figured in a depreciation schedule. •Depreciation is important because the value of the asset must be stated accurately on financial statements like the business's balance sheet. •At the end of each accounting period, the asset's book value is recalculated using the initial cost minus the accumulated depreciation.

Analyzing Complex Formulas Using Evaluate Formula

•When working with a workbook that contains complex formulas, it can be very helpful to evaluate the formulas step by step before attempting to make any changes. •The Evaluate Formula feature allows you to view and analyze the current value of each part of a formula.

To change the margins, on the Page Layout tab, in the Page Setup group

•click the Margins button, and click one of the preset margins options: Normal, Wide, or Narrow •or click Custom Margins... to specify your own values.

Excel's Goal Seek function

•lets you enter a desired value (outcome) for a formula and specify an input cell that can be modified in order to reach that goal. •Goal Seek changes the value of the input cell incrementally until the target outcome is reached. •In the figure at right, the outcome cell is cell B5, the loan payment; the desired outcome value is 750; and the input cell is cell B2, the loan amount.

Gridlines

•lines that appear on the worksheet defining the rows and columns.