After completing this course, you will know how to:
• Use the IF and SUMIF functions to calculate a value based on specified criteria; use a nested IF function to evaluate complex conditions; and use the ROUND function to round off numbers.
• Use the PMT function to calculate periodic payments for a loan; use Date and Time functions to calculate duration in years, months, and days or time; display, print, and hide formulas; create array formulas to perform multiple calculations on multiple sets of data at one time; and change calculation options and iteration limits.
• Use the VLOOKUP and HLOOKUP functions to find values in worksheet data; use the MATCH function to find the relative position of a value in a range; use the INDEX function to find the value of a cell at a given position in a range; and use data tables to project values.
• Use the Data Validation feature to validate data entered in cells; and use advanced filter options to display the data you specify.
• Export data from Excel to other formats, and import data from a text file into an Excel workbook; import XML data into a workbook, and export data from a workbook to an XML data file; and use Microsoft Query and the Web query feature to import data from external databases.
• Use the Goal Seek utility to meet a target output for a formula by adjusting the values in the input cells; use the Analysis ToolPak to perform statistical analysis; and create scenarios to save various sets of input values that produce different results.
• Run a macro to perform tasks automatically; record macros; assign a macro to a command button and a button in the worksheet; use a button to run a macro; create an Auto_Open macro; edit a macro by editing VBA code; and create a custom function to perform calculations when built-in functions are not available.