Introduction to Computer Applications Presentation by Aiera Khan

. Pivot tables

What are pivot tables? Pivot tables are a tool that can be found in Microsoft excel. A pivot table allows you to extract the significance from a large, detailed data set. It helps you build good-looking reports for large data sets. You can group data into categories, break down data into years and months, filter data to include or exclude categories, and even build charts. Uses of pivot tables: Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns. It allows grouping by any data field. Pivot tables are the perfect solution when you need to summarize and analyze large amounts of data.

• How to add pivot tables? Step 1: Click any single cell inside the data set. Step 2: On the Ribbon, click the Insert tab. Step 3: In the Tables group, click Recommended Pivot. Tables. Step 4: In the Recommended Pivot. Tables window, scroll down the list, to see the suggested layouts. Click on a layout, to see a larger view. Step 5: Click on the layout that you want to use, then click OK. .

. Goal seek

About goal seek Goal Seek is a process of calculating a value by performing what-if analysis on a given set of values. Excel's Goal Seek feature lets you adjust a value used in a formula to achieve a specific goal. Or, put another way, Goal Seek determines input values needed to achieve a specific goal. Use Goal Seek when you don't have an exact value to use. The function essentially uses a trial and error approach to back-solving the problem by plugging in guesses until it arrives at the answer. The function is extremely useful for performing sensitivity analysis. Sensitivity Analysis is a tool used in financial modeling to analyze how the different values for a set of independent variables affect a dependent variable in financial modeling.

Important terms in goal seek • Set cell: This refers to the cell that contains your target figure. • To value: This is the fixed target number. • By changing cell: This refers to the cell that you want to change in order to achieve the target.

Example of goal seek • The Goal Seek function is found in the Data tab under Forecast group. • Click on What-if Analysis. • The Goal Seek window will ask you to define the cells you need to manipulate: • The set cell is B 5. Write 2500 in the To value tab as the target revenue it 2500. • You want to figure out how many units need to be sold to achieve a revenue of 2500. So set by changing cell to $B$3. • After clicking OK, the Goal Seek Status window will indicate that a solution has been found. • Otherwise, it displays the closest value it has come up with. • For this example, in order to achieve a 2500 revenue, 658 units have to be sold.

Goal Seek Precision Goal seek returns approximate solutions. You can change the iteration settings in Excel to find a more precise solution. 1. The formula in cell B 1 calculates the square of the value in cell A 1. 2. Use goal seek to find the input value that produces a formula result of 25. 3. Result. Excel returns an approximate solution • 3. On the File tab, click Options, Formulas.

4. Under Calculation options, decrease the Maximum Change value by inserting some zeros. The default value is 0. 001. 5. Click OK. 6. Use Goal Seek again. Excel returns a more precise solution.

. Excel formulas

How to enter a formula? To enter a formula, execute the following steps. 1. Select a cell. 2. To let Excel know that you want to enter a formula, type an equal sign (=). 3. For example, type the formula A 1+A 2. Tip: instead of typing A 1 and A 2, simply select cell A 1 and cell A 2. 4. Change the value of cell A 1 to 3. Excel automatically recalculates the value of cell A 3. This is one of Excel's most powerful features!

If formula • The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. • So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False. • For example, =IF(C 2=”Yes”, 1, 2) says IF(C 2 = Yes, then return a 1, otherwise return a 2). • For example, to "pass" scores above 70: =IF(A 1>70, "Pass", "Fail").

If formula: syntax and usage • Return value • The values you supply for TRUE or FALSE • Syntax • =IF (logical_test, [value_if_true], [value_if_false]) • Arguments • logical_test - A value or logical expression that can be evaluated as TRUE or FALSE. • value_if_true - [optional] The value to return when logical_test evaluates to TRUE. • value_if_false - [optional] The value to return when logical_test evaluates to FALSE. • Usage notes • Use the IF function to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.

Nested if • The IF function can be "nested". A "nested IF" refers to a formula where at least one IF function is nested inside another in order to test for more conditions and return more possible results. Each IF statement needs to be carefully "nested" inside another so that the logic is correct. • For example, take a look at the nested IF formula in cell C 2 • Explanation: if the score equals 1, the nested IF formula returns Bad, if the score equals 2, the nested IF formula returns Good, if the score equals 3, the nested IF formula returns Excellent, else it returns Not Valid. If you have Excel 2016, simply use the IFS function.

Count function • The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A 1: A 20: =COUNT(A 1: A 20). • Syntax • COUNT(value 1, [value 2], . . . ) • The COUNT function syntax has the following arguments: • value 1 Required. The first item, cell reference, or range within which you want to count numbers. • value 2, . . . Optional. Up to 255 additional items, cell references, or ranges within which you want to count numbers.