Tag: Financial Institutions and Market b.com notes

If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

Here’s an example. To add the January numbers in this Entertainment budget, select cell B7, the cell immediately below the column of numbers. Then click AutoSum. A formula appears in cell B7, and Excel highlights the cells you’re totalling.

Press Enter to display the result (95.94) in cell B7. You can also see the formula in the formula bar at the top of the Excel window.

AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF function syntax has the following arguments:

• Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
• Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
• Average_range Optional. The actual set of cells to average. If omitted, range is used.

Remarks

• Cells in range that contain TRUE or FALSE are ignored.
• If a cell in average_range is an empty cell, AVERAGEIF ignores it.
• If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
• If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
• If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:

Average: Which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Median: Which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.

Mode: Which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

Example

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(C2=” Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).

Common problems

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

Criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included – a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

For example, criteria can be expressed as 32, “>32”, B5, “3?”, “apple*”, “*~?”, or TODAY().

sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

Sum_range should be the same size and shape as range. If it isn’t, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range. For example:

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

• =COUNTIF(A2:A5,”London”)
• =COUNTIF(A2:A5,A4)

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Auto completion of series

Enter the first number in the top cell. Hover until you see the Fill Handle and with the RIGHT mouse button, drag the Fill Handle until you have selected the cells to autofill.

Autofilling Via the Excel Ribbon

The Series window can be reached via the ribbon in Excel version 2007 and newer.

• Enter the first number of the series. Then select that cell and the cells that you want to auto fill.
• Click the Fill button located on the Editing section of the ribbon’s Home tab as shown in the first image.
• A drop-down menu appears as shown in the second image. Click Series and the Series window appears as shown below.
• Enter your step value and any other values on the Series window and click OK.

Through Drag

• Select one or more cells you want to use as a basis for filling additional cells.
• For a series like 1, 2, 3, 4, 5…, type 1 and 2 in the first two cells. For the series 2, 4, 6, 8…, type 2 and 4.
• For the series 2, 2, 2, 2…, type 2 in first cell only.
• Drag the fill handle Fill handle.
• If needed, click Auto Fill Options Button image and choose the option you want.

Sort and filter

Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

Sort text

1. Select a cell in the column you want to sort.
2. On the Data tab, in the Sort & Filter group, do one of the following:

• To quick sort in ascending order, click A to Z command in Excel that sorts A to Z or smallest number to largest (Sort A to Z).
• To quick sort in descending order, click Z to A command in Excel that sorts Z to A or largest number to smallest (Sort Z to A).

Filtering Data

The filter feature applies a drop-down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

• Go to the Data tab on Excel ribbon
• Select the Filter tool
• Select Eastern Company from the dropdown menu
• Select Dylan Rogers from the Salesperson dropdown menu

Charts

Create a chart

• Select the data for which you want to create a chart.
• Click INSERT > Recommended Charts.
• On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look.
• If you don’t see a chart you like, click All Charts to see all the available chart types.
• When you find the chart you like, click it > OK.
• Use the Chart Elements, Chart Styles, and Chart Filters buttons, next to the upper-right corner of the chart to add chart elements like axis titles or data labels, customize the look of your chart, or change the data that is shown in the chart.
• To access additional design and formatting features, click anywhere in the chart to add the CHART TOOLS to the ribbon, and then click the options you want on the DESIGN and FORMAT tabs.

Formulas are the backbone of most advanced calculations within Excel. Think of a formula as an equation; it’s a series of steps that lead to a final output. You can tell Excel that you’re entering a formula in Excel by starting your input with the equals sign (=). For example, try entering the following into a cell:

Formulas in Excel use the standard mathematical order of operations to evaluate. As such, operations within formulas are processed in this order:

• Parentheses
• Exponents (^)
• Multiplication (*)
• Division (/)
• Addition (+)
• Subtraction (-)

All Excel formulas begin with an equal sign (=).

After the equal symbol, you enter either a calculation or function. For example, to add up values in cells B1 through B5, you can either:

• Type the entire equation: =B1+B2+B3+B4+B5
• Use the SUM function: =SUM(B1:B5)

Press the Enter key to complete the formula. Done.

Elements of Microsoft Excel formulas

When you make a formula in Excel, you can use different elements to supply the source data to the formula and indicate what operators should be performed on those data. Depending on the formula type that you create, it can include any or all of the following parts:

Constants: numbers or text values that you enter directly in a formula, like =2*3.

Cell references: reference to a cell containing the value you want to use in your Excel formula, e.g. =SUM(A1, A2, B5).

To refer to data in two or more contiguous cells, use a range reference like A1:A5. For example, to sum values in all cell between A1 and A5, inclusive, use this formula:

=SUM(A1:A5).

Names: defined name for a cell range, constant, table, or function, for example =SUM(my_name).

Functions: predefined formulas in Excel that perform calculations using the values supplied in their arguments.

Operators: special symbols that specify the type of operation or calculation to be performed.

Special paste

Paste menu options

• Select the cells that contain the data or other attributes that you want to copy.
• On the Home tab, click Copy Copy icon.
• Click the first cell in the area where you want to paste what you copied.
• On the Home tab, click the arrow next to Paste, and then do any of the following.

Paste options

Operation options

The Operation options mathematically combine values between the copy and paste areas.

Freeze pane

To keep an area of a worksheet visible while you scroll to another area of the worksheet, go to the View tab, where you can Freeze Panes to lock specific rows and columns in place, or you can Split panes to create separate windows of the same worksheet.

Freeze the first column

• Select View > Freeze Panes > Freeze First Column.

            The faint line that appears between Column A and B shows that the first column is frozen.

Freeze the first two columns

1. Select the third column.
2. Select View >Freeze Panes > Freeze Panes.

Freeze columns and rows

1. Select the cell below the rows and to the right of the columns you want to keep visible when you scroll.
2. Select View > Freeze Panes> Freeze Panes.

Unfreeze rows or columns

• On the View tab > Window > Unfreeze Panes.