Excel notes for SAM cengage exam for tutorials PDF

Preview

Summary

Instructions on excel tutorials for SAM cengage exams and trainings, all trainings included in PDF document shared. Explanations and instructions...

Description

*EXCEL FUNCTIONS* Use the keyboard to enter a formula that uses a function. Type ‘=’ sign then start typing the function you want You can either type if entirely or select your desired function when it pops up This will give to the required arguments(inputs) you need to enter

Create a formula using the Quick Analysis Tool / Create a conditional formatting rule using the Quick Analysis tool. Select range and click the quick analysis icon that appears The quick analysis toolbar will appear

This can be used to add data bars, calculate totals, make suggested charts etc…

Create a nested formula. More than 1 function can be used at a time- simply type the other function within the brackets of the first Eg. ROUND(AVERAGE(range);2) this will return the average of the specified range rounded to 2 values

Use the Fill handle to copy a formula. Hover curser over corner of the cell containing formula until ‘+’ appears Click and drag selecting all relevant cells to be filled

Enter a formula by selecting cells. Instead of typing in the cell name (A1) simply select the cell For a range (A1:A5) click and drag

Copy and paste a formula. Select cell to copy

Copy: ctrl+c / copy icon on ribbon Cut: ctrl+x / cut icon Select cell to paste into Paste: ctrl+v / paste icon/ enter

Create a formula using arithmetic operators. Plus: + Minus: Multiply: * Divide: /

Create a formula using order of operations. * & / happen depending on which comes first + & - happen depending on which comes first (* & /) happen before (+ & -) Use brackets to dictate order if needed Eg. 1+2*3=7 vs (1+2)*3= 9

Create a formula using absolute cell references The cell reference doesn’t change if you copy and paste the formula Eg. $A$1 Either type the ‘$’ in where needed or after selecting the relevant cell press the fn key then press f4

Create a formula using relative cell references. When you copy a formula to a new cell Excel changes the references Eg. In A3 (=A2-A1) if I copied that formula to B3 it would change to (=B2-B1)

Create a formula using mixed cell references. Uses both relative and absolute referencing Eg. $A1 -> the column will not change when the formula is copied but the row will Eg. B1*$A$1 -> B1 uses relative referencing and $A$1 uses absolute referencing

Create a formula using structured references. You can use column names instead of cell names to make formulas easier to understand

Type the [column name] in brackets as it appears on the data sheet

Create a formula using 3D references. Use to perform the calculations you want on the same cell or range on multiple worksheets without the need for copying or rekeying the data Select the worksheets you want to work with (click the first one, hold shift and click the last one) Format example : =SUM('October:December'!B6) October:December -> the worksheets B6 -> the cell being referenced SUM()-> the result will be the sum of the values in B6 on each worksheet

Create a formula using external references. Use external references to enter data from a different workbook into your formula Eg. From SAM Cengage: In the Branch Sales workbook, click cell B8. You want to calculate the January, February, and March Sales for the San Diego branch office by using references to cells in the Monthly Sales workbook. Type an=(equal sign) in cell B8. You begin the formula with an equal sign, then you will use the mouse to enter the external cell references. Click the Monthly Sales workbook. The Monthly Sales workbook is activated. In the Monthly Sales workbook, click cell B8. The external reference to cell B8 appears in the formula bar and in cell B8 in the Branch Sales workbook. In the Monthly Sales workbook, type a+(plus sign) in cell B8. In the Monthly Sales workbook, click the February worksheet tab. The February worksheet is displayed. In the Monthly Sales workbook, click cell B8. In the Monthly Sales workbook, type a+(plus sign) in cell B8. In the Monthly Sales workbook, click the March worksheet tab. The March worksheet is displayed. In the Monthly Sales workbook, click cell B8. On the formula bar, click the Enter button. The formula, with the three external references, is entered in the Sales by Branch worksheet, and the total is displayed in cell B8.

Create a formula using defined names. Use defined names rather than cell references so users can understand the inputs and outputs easily

Create a formula using a worksheet reference. Use so that your worksheet automatically updates if the source data (in another workbook) changes Eg. From SAM Cengage: Click cell B8. You want to enter a formula here to calculate Net Income. You will use the Gross Profit value from this worksheet, and the Total Expenses value from another worksheet. Type an=(equal sign) in cell B8 to start the formula. The first argument you need to enter is the Gross Profit value included in the current worksheet. Click cell B6. Type-(minus sign) in cell B8. Next, you want to subtract the operating expenses. This data resides in another workbook, so you need to switch to that workbook in order to create the external cell reference. Click the View tab on the Ribbon. In the Window group, click the Switch Windows button. Excel displays a list of all workbooks currently open. Click Rockland Expenses.xlsx on the list. The Rockland Expenses.xlsx file appears in the Excel window. The Operating Expenses worksheet is active. On the Operating Expenses worksheet, click cell B14. Excel enters the external cell reference in the formula, noting the workbook name, the worksheet name, and then the cell address. Press ENTER. Excel switches back to the original workbook and performs the calculation, returning the result in cell B8. Excel has created a link between the two workbooks; changes made to the source workbook will be automatically updated in the destination workbook.

Trace formula precedents and dependents.

Go to the formula tab on the ribbon and select what you want to do

Trace errors in a formula. Use to find the source of a formula error Select the cell where the error occurs Click on trace error, trace arrows will appear showing where the error originates

Create a formula using the AVERAGE function. Use to calculate the average of selected arguments (values) Select cell range of values to be used

Create a formula using the AVERAGEIF function. Use to find the average of cells that meet a certain condition (criteria) (range; criteria; average range) The range refers to the values to be considered to meet the criteria while the average range is the values to be averaged

Create a formula using the AVERAGEIFS function. Similar to AVERAGEIFS but for more than one condition (average range; criteria range 1; criteria1;…) The average range is the values to be averaged and criteria range 1 is the values to be considered for criteria 1 etc…

Create a formula using the MEDIAN function.

Use to get the median of a set of values Select value range

Create a formula using the COUNT function. Use to count the number of cells in a range that contain numbers Select range to be counted

Create a formula using the COUNTIF function. Use to count the number of cells in a range that meet a given condition (range; criteria)

Create a formula using the COUNTA function. Use to count the number of cells in a range that aren’t empty Select range

Create a formula using the COUNTBLANK function. Use to count the number of blank cells in a range Select range

Create a formula using the IF function. Use to check whether one condition is met and return a value if TRUE, and a different value if FALSE Enter (logical test; value if TRUE; value if FALSE)

Create a formula using the IFS function. Use to check if one or more conditions are met and returns the first true value (Logical test 1; value if true 1)

Add the IFERROR function to an existing formula. Use in a data or lookup table to flag a formula error or invalid entry and create a customized error message to help users identify and fix problems easily (value; value if error) Insert customised error message where for value if error

Create a formula using the OR function. Use to check if any of the arguments are true Returns TRUE if at least one is true and FALSE if none

Create a formula using the AND function. Use to check if all values arguments are true Returns TRUE if all conditions are met (logical 1; logical 2;…)

Create a formula using the MAX function. Use to find the largest value in a selected range Select range

Create a formula using the MAXIFS function. Use to return the max in a range of values that meet certain criteria (max range; criteria range 1; criteria 1; …)

Create a formula using the MIN function. Use to find the smallest value in a selected range Select range

Create a formula using the MINIFS function. Use to return the min in a range of values that meet certain criteria (min range; criteria range 1; criteria 1; …)

Create a formula using the SUM function. Adds all the numbers in a range of cells Select range

Create a formula using the SUMIF function. Adds the cells that meet the specified criteria (range; criteria; sum range) The range refers to the values to be considered to meet the criteria while the sum range is the values to be added

Create a formula using the TODAY function. Use to get the current date Returns the date in the date format

Create a formula using the ROUND function.

Use to round a number to a specified number of digits Enter (number; number of digits)

Create a formula using the PROPER function. Use to convert a string to a format where the first letter of each word is capitalised, and all other letters are in lowercase

Create a formula using the CONCAT function. Use to concatenate (link together) a list or range of text strings (text 1; text 2; …)

Create a formula using the LEFT function. Use to return a specified number of characters from the start of a text string (text; number of characters) Can be used with CONCAT if you want to link parts of a text string with other text string (eg. First letter of a name with a surname to go from name{SARAH} & surname{BRADY} to get initial and surname{SBRADY})

Create a formula using the RIGHT function. Use to return a specified number of characters from the end of a text string (text; number of characters)

Create a formula using the HLOOKUP function. Use to quickly and accurately assign values to worksheet data from a lookup list. Looks for a value in the top row of a table and then returns a value in the same column from a row you specify Enter (lookup value (select cell); table array; row index number)

Create a formula using the VLOOKUP function. Use to quickly find the value that corresponds to specified cell content Looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify Enter (lookup value (select cell); table array; column index number)

Transpose data using the TRANSPOSE function.

Use to convert a vertical cell range to a horizontal cell range (or vice versa), if you find that the rows and columns of data in your worksheet do not suit your needs

Create a formula using the OFFSET function. Use to return a value that is a given number of rows and columns from a given reference (reference; rows; columns)

Create a formula using the SWITCH function. Use to evaluate an expression (eg. Numeric value) and return the corresponding matching value Numeric value in table, numeric value with corresponding value may be shown in a separate key but you will typle these values into the function (expression/cell to be matched; value 1; matching 1; default result/ value 2; matching 2; …)...