Computer Technology Excel Concept Quiz 2

Formulas and Functions

Functions, just like formulas, always begin with an equal sign (=). After the equal sign, you enter in the name of the function. Following the name of the function, you provide the arguments of the function. Arguments are always enclosed parentheses. ex. Formula: =5+2 Answer:7 Function: =SUM(5,2) Answer: 7

Location of Format Painter

Location: Home Tab -> Clipboard group -> (click on Format Painter -> The pointer will change to a paint brush)

What can the Format Painter copy?

Number Format (General, %, $, etc.) Font face, size, color Font characteristics (bold, italic, underline) Fill color (background) Cell Borders Text alignment, direction, orientation

Hints to Remember When Starting to Put Functions in Spreadsheets

When typing a function into a cell, don't insert spaces between the equal sign, function name, and arguments If you're adding more than one value, separate each function with a comma

Advantage of the IF Function

What is really the most powerful thing about the 'IF' function is that it can be 'nested' within and combined with other functions to create an incredible amount of flexibility to meet different requirements.

Format Painter

When it comes to copying formatting in Excel, Format Painter is one of the most helpful underused features. It works by copying the formatting of one cell and applying it to others

AVERAGE Function Syntax Arguments

#1 Required. The first number, cell reference, or range for which you want the average. #2 Optional. Additional numbers, cell references, or ranges for which you want the average, up to a maximum of 255

Syntax or Arguments of the IF Funtion

1st Argument =IF(condition 2nd Argument =IF(condition,value if true 3rd Argument =IF(condition,value if true,value if false)

Examples of IF Function

=IF(A1>10,"Larger","Smaller" =IF(J4>40,"Yes","No") Note: This IF Function taken from MP2 Project 7 of 7, Task 3. The reason for the IF Function in that worksheet was to have a quick visual of which employees had worked more that 40 hours during the week

AVERAGEIF Function Third Argument

Average Range Are the actual cells to be used to find the average. If omitted, the cells in the range are used. ex. =AVERAGEIF(A1:A20,"apples",C1:C20)

AVERAGE

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,4,5,7, and 10 is 30 divided by 6, which is 5

AVERAGEIF Function Second Argument

Criteria Is the condition or criteria in the form of a number, expression, or text that defines which cells will be used to find this average. ex. Numbers: 32 or "32" =AVERAGEIF(A1:A20,32) Expression: ">32" =AVERAGEIF(A1:A20,">320") Text: "apples" =AVERAGEIF(A1:A20,"apples")

AVERAGEIF Function Arguments

Range: Range of cells you want evaluated

AVERAGE Function

Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula AVERAGE(A1:A20) returns the average of those numbers

Max Function

Returns the largest value in a set of values - Arguments can either be numbers or names, arrays, or references that contain numbers. - Number 1 is required, subsequent numbers are optional ex. =MAX(A1:A25)

Scenario for the AVERAGE Function

Scenario: Select cell A9 and insert a function to determine the average for the Range of A1:A6. What would you type in? =AVERAGE(A1:A6) What you are telling the computer to do: to add the numbers within cells A1 thru A6, count the number of cells and divide by the number of cells to get the average. (Just like what you would be doing on a regular math problem to find the average>

Examples of UPPER, LOWER, and PROPER Functions

Scenrio: Cells D4:D18 are cells with text =UPPER(D4:D18) Note: Everything in all caps =LOWER(D4:D18) Note: Everything in all lowercase =PROPER(D4:D18) Note:

Min Function

The Excel Min function returns the smallest value from a supplied set of numeric values. ex. =MIN(A1:A16)

IF Function

The Microsoft Excel IF function returns one value if the condition is FALSE. The IF Function is a built in function in Excel that is categorized as Logical function. It is one of the most commonly used functions, because of its simplicity. It can also be nested within other functions