ITM 215 Class 20 Apr 8

# ITM 215 Class 20 Apr 8 - ITM 215 Computer Applications in...

This preview shows pages 1–15. Sign up to view the full content.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
7/11/2007  New Perspectives  IF function AND function Nesting IF functions OR function VLOOKUP function  2 Objectives
7/11/2007  New Perspectives  IFERROR function COUNTIF, SUMIF, and  AVERAGEIF functions COUNTIFS, SUMIFS, and  AVERAGEIFS functions Use advanced filters 3 Objectives cont.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
7/11/2007  New Perspectives  Create fields that require the least  maintenance;   i.e. Hire Date, Birth Date  require no  maintenance because they  will never change Store the smallest unit of data possible;   i.e.  use separate fields for address data  instead of  one that contains everything Apply text formats to fields with numerical text  data, those numerical fields that will  not be  used in a calculation;   i.e. telephone  4 Creating Fields in a Table
7/11/2007  New Perspectives  IF Function IF( logical_test, value_if_true, [value_if_false]) AND Function =IF(AND(G2="FT",M2>=1),K2*0.03,0) Structured References You can replace the specific cell or range    address with a  structured reference , the  actual table name or column header 5 Working with Logical Functions

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
7/11/2007  New Perspectives  Used with respect to data table Must specifically name a data table If the data table ranges change the structured references will remain valid and adjust as  the  table changes.   If you add or delete columns/rows, the cell  references may no longer be valid but the  structured references are  The structured reference is easier to  6 Structured References
7/11/2007  New Perspectives  Structured references are easier to  understand: =SUM(DeptSales[SaleAmt]) =SUM(C2:C7) 7 Structured References

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
7/11/2007  New Perspectives  Qualified means the table is explicitly  identified, you use the table name  in the formula If you use a structured reference  within a table it can be unqualified If you use a structured reference  outside a table you need to use a  8 Unqualified versus Qualified  References
7/11/2007  New Perspectives  IF AND  OR NOT IFERROR TRUE 9 Logical Functions

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
7/11/2007  New Perspectives  10 The IF function Allows for different results,  based on a condition Uses three arguments The value if true and value if  false may contain additional  (nested) IF functions for  more
7/11/2007  New Perspectives  11 IF Function =IF( logical_test , value_if_true , value_if_false )

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
7/11/2007  New Perspectives  12 Nested IF Flowchart
7/11/2007  New Perspectives  A nested IF function is when one  IF  function is placed inside another  IF  function to test an additional  condition =IF( [Pay Grade]= 1,2500,IF([Pay  Grade]=2,5000, IF([Pay Grade]=3,  7500,"Invalid pay grade" ))) 13 Working with Logical Functions

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
7/11/2007  New Perspectives  14 Show Examples of IF
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 49

ITM 215 Class 20 Apr 8 - ITM 215 Computer Applications in...

This preview shows document pages 1 - 15. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online