Jeanie W. Mizell

Tuesday, March 20--Lesson 3, Exercise 18

• Function-A preprogrammed Excel formula for a complex calculation
• IF( ) Function--is a logical function
• With an IF function, Excel can be told to perform one of two different calculations based on whether your data matches certain criteria
• Criteria--a value, some text, or an expression that defines the type of cells you'r looking for.
• Example:  Use an IF Function to calculate a 10% bonus if total sales are over $500,000 and just a 3% bonus if they are not
• The format for an IF funciton is:  =IF(condition,x,y)
• The condition is an Expression that is either true or false
• Expression-A sort of equation (such as B6>25) that returns a value, such as TRUE or FALSE.  Excel uses expressions to identify cells to include in certain formulas, such as IF and SUMIF
• If the condition is true, the result is x
• If the condition is false, the result is y
• =IF(B2>500000,B2*.10,B2*.03)
• This function say, "If total sales (cell B2) are greater than $500,000, then take total sales times 10% to calculate the bonus.  Otherwise, take total sales times 3%.
• Notice the in the IF function, the value, $500,000, is enterend without the dollar sign and the comma.

Wednesday, March 28--L3, Ex 18

• You can have words appear in a cell instead of the result of a calculation.

For example, you might type =IF(B2>500000, "We made it! ", "Good try. ") to display the words We made it! if total sales are over $500,000, or the words Good try. if they are not.

 Notice here that the words you want to use are surrounded by
quotation marks " ".

• IF functions may use the conditional operators below to state the condition:

     = Equals                     <> Not equal to

     > Greater than             >= Greater than or equal to

     < Less than                 <= Less than or equal to

& Used for joining text

• Like any other function, you can enter an IF func­tion manually, use AutoComplete, or use the Insert Function and/or the Function Arguments dialog boxes to help you.

Nested IF Functions

• You can nest an IF function (or any other function) as one of the arguments in another function.
• For example, the formula:

=IF(C3>92, "A" ,IF(C3>83,"B" ,IF(C3> 73,"C" ,IF(C3>6 5,"0","F"))))

 If the average score is greater than 92, then the student gets an A; if the score is less than or equal to 92 but greater than 83, the student gets a B; if the score is less than or equal to 83 but greater than 73, the student gets a C; and so on.

=SUMIF() and SUMIFS() Functions

• SUMIFO is a Math & Trig function that uses a con­dition to add certain data.

If the condition is true, then data in a corresponding cell is added to the total; if it is false, then the corre­sponding data is skipped.

• The format for a SUMIF function is

=SUMIF(range_to_test, condition, sum_range)

The range_to_test is the range of cells you want to test.

The condition is an expression that is either true or false and defines which cells should be added to the total.

If the condition is true, the corresponding cell in sumJange is added to the total.

If the condition is false, the corresponding cell in sumJange is skipped (not added to the total).

The condition is written using the same symbols (such as >,<>, etc.) as listed in the IF section. However, here, you must enclose the condition in quotation marks" ".

• For example, if you had a worksheet listing sales for several different products, you could total the sales for widgets only by using this formula: =SUM I F(02: 055, "Widget" ,G2:G55)

Assume here that column 0 contains the name of the product being sold and column G contains the total amount for that sale.

If column 0 contains the word "Widget" then the amount for that sale (located in column G) is added to the running total.

Because Widget is a text label, in the formula you must enclose it in quotation marks (" ").

• You can leave the last argument off if you want to sum the same range that you're testing.
• For exam­ple: =SUMIF(G2:G1 0,"<=500")
• This formula calculates the total of all values in the range G2 to G10 that are less than or equal to 500 .

          SUMIFSO is a function similar to SUMIFO except that it allows you to enter multiple qualifying conditions.

The format for a SUMIFS statement is

=SUMI FS( sumJange, range_to_test1 ,condition 1, range_ to_ test2,condition2, etc.)

The range_to_test1 is the first range of cells you want to test.

The condition 1 is an expression that is either true or false and defines which cells should be added to the total.

If the condition is true, the corresponding cell in sum_range is added to the total.

If the condition is false, the corresponding cell in sum_range is skipped (not added to the total).

You can add conditions and additional ranges to test as needed. You can specify the same range to test or use a different one.

All ranges, the sum_range and the range_to_test, must be the same size and shape.

• Using the earlier example, if you wanted to total all the sales of Widgets and Whatsits, you could use a formula such as:

=SUMIFS(G2:G55,D2: D55, "Widget", D2: D55,"Whatsits")

 Again, column 0 contains the name of the product being sold and column G contains the total amount for that sale.

=COUNTIF() and COUNTIFS() Functions

• COUNTIF is a Statistical function that uses a condi­tion to count the number of items in a range.

,You learned about two similar functions, COUNT and COUNT A, in Exercise 15.

• If the result of the condition is true, then the item is added to a running count; if it is false, then the item is skipped.
• The format for a COUNTIF statement is

=COU NTI F (range_ to_ test, condition).

The range_to_test is the range of cells you want to test (and count).

The condition is an expression that is either true or false and defines which cells should be counted.

The condition is written using the same symbols listed in the IF section. Like SUMIF, you must enclose the condition in quotation marks" ".

For example, if you want to count the number of individual Widget sales in the earlier example, you could use this formula:

=COUNTI F(D2: D55, "Widget")

Assume here that column D contains the name of the product being sold.

If column D contains the word "Widget" then that record is added to the running total of the num­ber of widget sales.

Because Widget is a text label, you must enclose it in quo­tation marks (" ").

To compute the number of widgets sold or the value of those sales, use SUMIF.

• You can combine functions to create complex cal­culations: =SUMIF(D3:D13,"PASS" ,C3:C13) /COUNTIF(D3:D13,"PASS")

This formula, based on the earlier example, computes the average score of all the students who passed the course.

Assume that column D contains the words "Pass" or "Fail" based on the student's final score. The final score is located in column C.

The formula sums the scores of all the students who passed and divides that by the number of students who passed, calculating an average score.

• COUNTIFSO is a function similar to COUNTIFO except that it allows you to enter multiple qualifying conditions.

The format for a COUNTIFS statement is =COUNTIFS(range_to_test1 ,condition 1, range_to_ test2, condition2, etc.)

The range_to_test1 is the range of cells you want to test.

The condition 1 is an expression that is either true or false and defines which cells should be counted.

You can add additional conditions and ranges to test as needed. You can specify the same range to test or use a different one.

All ranges, the range_to_test and the condition, must all be the same shape and size.

• Using the earlier example, if you wanted to count all Widget sales with a value over $100, you could use a formula such as:

=COUNTI FS(D2:D55, "Widget" ,G2:G55,"> 1 00")

 Again, column 0 contains the name of the product being sold, and column G contains the total amount for that sale.

Since there are 54 rows in the two ranges, the highest answer you might get is 54. A row is counted only if it contains both the word Widget in column 0 and a value greater than 100 in column G.