Docstoc

excel-exposure-master-lesson-4-4-12-file

Document Sample
excel-exposure-master-lesson-4-4-12-file Powered By Docstoc
					Back to Table of Contents


           Information about this Workbook

           Author:           Ben Currier
           Website:          ExcelExposure.com

           Relevant Links:   University of Reddit Class Page
                             University of Reddit Post re: Class

           Contact:          Ben@ExcelExposure.com
ExcelExposure.com - Lesson Workbook (version 4-4-2012)
  Note: This workbook is a work-in-progress and will be updated as new lessons and information is added.
  Check back at ExcelExposure.com for the latest version.


Excel Lesson Workbook - Table of Contents
Sheet Ref.    Description
        1     Lesson Outline
          2   List of All Excel Functions
          3   List of All Excel Shortcuts
          4   Function Examples
          5   Pivot Table Examples - Underlying Data
          6   Pivot Table Example 1
          7   Pivot Table Example 2
          8   AutoFill Demonstration
          9   Referencing Related Example
        10    Data Validation
        11    Advanced Filtering


              Workbook Info
Back to Table of Contents


     This is the current plan for the types of topics I will be covering. It will not necessarily be in this order.

     Function Related Lessons

       Experience Level 1 - Date & Time Functions
       Experience Level 1 - Text Functions
       Experience Level 1 - Lookup / Reference Functions
       Experience Level 1 - Logical Functions
       Experience Level 1 - Math & Statistical Functions
       Experience Level 1 - Information Function
       How to use the 'Insert Function' feature and explanation of how formulas are written
       How to write complex custom formulas using multiple (i.e. nested) functions
       Error and data checking (ISERROR, IFERROR, etc.)

     Workbook / Reference Related Lessons

       Absolute/Relative cell referencing
       Naming ranges/cells - Importance & Proper Usage
       Drop-down lists and data validation
       Sheet manipulation (coloring tabs, re naming, copying/moving, etc.)
       Protecting and hiding information & formulas
       Advice on setting up models, worksheets, and intelligent data structure
       How to write complex custom formulas using multiple (i.e. nested) functions
       Data filtering, AutoFilter, and advanced filters
       Setting up useful templates to help automate work

     Formatting Related Lessons

       Formatting Shortcuts
       Presentation options for Data/Information
       Format Painter / Auto-fill Drag
       Conditional Formatting
       Copying/pasting functionality (values, transpose, Formatting, constants, links, etc.)
       Advanced sorting methods
       Data filtering, AutoFilter, and advanced filters
       Group / ungroup
       Freeze / unfreeze panes
       Printing options / print area setup
       Gridlines and other Formatting options

     Pivot Table Lessons

       Explanation of Pivot Tables and what they do
       How to set up Pivot Tables properly
       Additional functionality / options with Pivot Tables
       How to make better use of Pivot Table / common issues

     Macros / VBA Lessons

       How to record a macro and interpret/modify results
       Introduction to VBA programming
       Writing macros including advanced training in VBA programming
       Creating user defined functions in Excel
Excel Functions                                                                                                                                                                             Level 1


Back to Table of Contents

Ref.     Category           Level   Function      Description                                                                 Syntax -                                                 Favorites
FC-1     Date & Time           1    DATE          Returns the serial number of a particular date                              =DATE(year,month,day)
FC-2     Date & Time           1    DATEVALUE     Converts a date in the form of text to a serial number                      =DATEVALUE(date_text)
FC-3     Date & Time           1    DAY           Converts a serial number to a day of the month                              =DAY(serial_number)
FC-4     Date & Time           1    HOUR          Converts a serial number to an hour                                         =HOUR(serial_number)
FC-5     Date & Time           1    MINUTE        Converts a serial number to a minute                                        =MINUTE(serial_number)
FC-6     Date & Time           1    MONTH         Converts a serial number to a month                                         =MONTH(serial_number)
FC-7     Date & Time           1    NOW           Returns the serial number of the current date and time                      =NOW()
FC-8     Date & Time           1    SECOND        Converts a serial number to a second                                        =SECOND(serial_number)
FC-9     Date & Time           1    TIME          Returns the serial number of a particular time                              =TIME(hour,minute,second)
FC-10    Date & Time           1    TIMEVALUE     Converts a time in the form of text to a serial number                      =TIMEVALUE(time_text)
FC-11    Date & Time           1    TODAY         Returns the serial number of today's date                                   =TODAY()
FC-12    Date & Time           1    YEAR          Converts a serial number to a year                                          =YEAR(serial_number)
FC-13    Information           1    CELL          Returns information about the formatting, location, or contents of a        =CELL(info_type, [reference])
                                                  cell
FC-14    Information           1    ISBLANK       Returns TRUE if the value is blank                                          =ISBLANK(value)
FC-15    Information           1    ISERROR       Returns TRUE if the value is any error value                                =ISERROR(value)
FC-16    Information           1    ISNONTEXT     Returns TRUE if the value is not text                                       =ISNONTEXT(value)
FC-17    Information           1    ISNUMBER      Returns TRUE if the value is a number                                       =ISNUMBER(value)
FC-18    Information           1    ISTEXT        Returns TRUE if the value is text                                           =ISTEXT(value)
FC-19    Logical               1    AND           Returns TRUE if all of its arguments are TRUE                               =AND(logical1,logical2,...)
FC-20    Logical               1    FALSE         Returns the logical value FALSE                                             =FALSE
FC-21    Logical               1    IF            Specifies a logical test to perform                                         =IF(logical_test, [value_if_true], [value_if_false])
FC-22    Logical               1    IFERROR       Returns a value you specify if a formula evaluates to an error;             =IFERROR(value, value_if_error)
                                                  otherwise, returns the result of the formula
FC-23    Logical               1    NOT           Reverses the logic of its argument                                          =NOT(logical)
FC-24    Logical               1    OR            Returns TRUE if any argument is TRUE                                        =OR(logical1,logical2,...)
FC-25    Logical               1    TRUE          Returns the logical value TRUE                                              =TRUE
FC-26    Lookup/Ref.           1    ADDRESS       Returns a reference as text to a single cell in a worksheet                 =ADDRESS(row_num, column_num, [abs_num], [a1],
                                                                                                                              [sheet_text])
FC-27    Lookup/Ref.           1    COLUMN        Returns the column number of a reference                                    =COLUMN([reference])
FC-28    Lookup/Ref.           1    COLUMNS       Returns the number of columns in a reference                                =COLUMNS(array)
FC-29    Lookup/Ref.           1    HLOOKUP       Looks in the top row of an array and returns the value of the indicated     =HLOOKUP(lookup_value,table_array,row_index_num,[rang
                                                  cell                                                                        e_lookup])
FC-30    Lookup/Ref.           1    INDEX         Uses an index to choose a value from a reference or array                   =INDEX(array,row_num,[column_num]) - 2 types
FC-31    Lookup/Ref.           1    INDIRECT      Returns a reference indicated by a text value                               =INDIRECT(ref_text,a1)
FC-32    Lookup/Ref.           1    LOOKUP        Looks up values in a vector or array                                        =LOOKUP(lookup_value, array) - 2 types
FC-33    Lookup/Ref.           1    MATCH         Looks up values in a reference or array                                     =MATCH(lookup_value,lookup_array,match_type)
FC-34    Lookup/Ref.           1    OFFSET        Returns a reference offset from a given reference                           =OFFSET(reference,rows,cols,height,width)
FC-35    Lookup/Ref.           1    ROW           Returns the row number of a reference                                       =ROW([reference])
FC-36    Lookup/Ref.           1    ROWS          Returns the number of rows in a reference                                   =ROWS(array)
FC-37    Lookup/Ref.           1    VLOOKUP       Looks in the first column of an array and moves across the row to           =VLOOKUP(lookup_value,table_array,col_index_num,[range
                                                  return the value of a cell                                                  _lookup])
FC-38    Math & Trig           1    ABS           Returns the absolute value of a number                                      =ABS(number)
FC-39    Math & Trig           1    PRODUCT       Multiplies its arguments                                                    =PRODUCT(number1,number2,...)
FC-40    Math & Trig           1    RAND          Returns a random number between 0 and 1                                     =RAND()
FC-41    Math & Trig           1    RANDBETWEEN   Returns a random number between the numbers you specify                     =RANDBETWEEN(bottom,top)
FC-42    Math & Trig           1    ROUND         Rounds a number to a specified number of digits                             =ROUND(number,num_digits)
FC-43    Math & Trig           1    ROUNDDOWN     Rounds a number down, toward zero                                           =ROUNDDOWN(number,num_digits)
FC-44    Math & Trig           1    ROUNDUP       Rounds a number up, away from zero                                          =ROUNDUP(number,num_digits)
FC-45    Math & Trig           1    SUBTOTAL      Returns a subtotal in a list or database                                    =SUBTOTAL(function_num,ref1,...)
FC-46    Math & Trig           1    SUM           Adds its arguments                                                          =SUM(number1,number2,...)
FC-47    Math & Trig           1    SUMIF         Adds the cells specified by a given criteria                                =SUMIF(range,criteria,[sum_range])
FC-48    Math & Trig           1    SUMIFS        Adds the cells in a range that meet multiple criteria                       =SUMIFS(sum_range,criteria_range,criteria,...)
FC-49    Math & Trig           1    SUMPRODUCT    Returns the sum of the products of corresponding array components           =SUMPRODUCT(array1,array2,[array3],...)

FC-50    Statistical           1    AVERAGE       Returns the average of its arguments                                        =AVERAGE(number1,number2,...)
FC-51    Statistical           1    AVERAGEIF     Returns the average (arithmetic mean) of all the cells in a range that      =AVERAGEIF(range,criteria,[average_range])
                                                  meet a given criteria
FC-52    Statistical           1    COUNT         Counts how many numbers are in the list of arguments                        =COUNT(value1,value2,...)
FC-53    Statistical           1    COUNTA        Counts how many values are in the list of arguments                         =COUNTA(value1,value2,...)
FC-54    Statistical           1    COUNTBLANK    Counts the number of blank cells within a range                             =COUNTBLANK(range)
FC-55    Statistical           1    COUNTIF       Counts the number of cells within a range that meet the given criteria      =COUNTIF(range,criteria)

FC-56    Statistical           1    COUNTIFS      Counts the number of cells within a range that meet multiple criteria       =COUNTIFS(criteria_range,criteria,...)

FC-57    Statistical           1    MAX           Returns the maximum value in a list of arguments                            =MAX(number1,number2,...)
FC-58    Statistical           1    MEDIAN        Returns the median of the given numbers                                     =MEDIAN(number1,number2,...)
FC-59    Statistical           1    MIN           Returns the minimum value in a list of arguments                            =MIN(number1,number2,...)
FC-60    Text                  1    CONCATENATE   Joins several text items into one text item. Easier to use '&' instead of   =CONCATENATE(text1,text2,...)
                                                  the function usually.
FC-61    Text                  1    EXACT         Checks to see if two text values are identical                              =EXACT(text1,text2)
FC-62    Text                  1    FIND          Finds one text value within another (case-sensitive)                        =FIND(find_text,within_text,start_num)
FC-63    Text                  1    LEFT          Returns the leftmost characters from a text value                           =LEFT(text,num_chars)
FC-64    Text                  1    LEN           Returns the number of characters in a text string                           =LEN(text)
FC-65    Text                  1    LOWER         Converts text to lowercase                                                  =LOWER(text)
FC-66    Text                  1    MID           Returns a specific number of characters from a text string starting at      =MID(text,start_num,num_chars)
                                                  the position you specify
FC-67    Text                  1    PROPER        Capitalizes the first letter in each word of a text value                   =PROPER(text)
FC-68    Text                  1    REPLACE       Replaces characters within text                                             =REPLACE(old_text,start_num,num_chars,new_text)
FC-69    Text                  1    RIGHT         Returns the rightmost characters from a text value                          =RIGHT(text,num_chars)
FC-70    Text                  1    SEARCH        Finds one text value within another (not case-sensitive)                    =SEARCH(find_text,within_text,start_num)
FC-71    Text                  1    TEXT          Formats a number and converts it to text                                    =TEXT(value,format_text)
FC-72    Text                  1    TRIM          Removes spaces from text                                                    =TRIM(text)
FC-73    Text                  1    UPPER         Converts text to uppercase                                                  =UPPER(text)
FC-74    Database              2    DGET          Extracts from a database a single record that matches the specified         =DGET(database,field,criteria)
                                                  criteria




                                                                                        Page 4 of 53
Excel Functions                                                                                                                                                                               Level 1


Ref.     Category      Level   Function           Description                                                                 Syntax -                                                   Favorites
FC-75    Database         2    DSUM               Adds the numbers in the field column of records in the database that        =DSUM(database,field,criteria)
                                                  match the criteria
FC-76    Date & Time      2    DAYS360            Calculates the number of days between two dates based on a 360-day          =DAYS360(start_date,end_date,method)
                                                  year
FC-77    Date & Time      2    EDATE              Returns the serial number of the date that is the indicated number of       =EDATE(start_date,months)
                                                  months before or after the start date
FC-78    Date & Time      2    EOMONTH            Returns the serial number of the last day of the month before or after a    =EOMONTH(start_date,months)
                                                  specified number of months
FC-79    Date & Time      2    NETWORKDAYS        Returns the number of whole workdays between two dates                      =NETWORKDAYS(start_date,end_date,[holidays])
FC-80    Date & Time      2    NETWORKDAYS.INTL   Returns the number of whole workdays between two dates using                =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holid
                                                  parameters to indicate which and how many days are weekend days             ays])

FC-81    Date & Time      2    WEEKDAY            Converts a serial number to a day of the week                               =WEEKDAY(serial_number,[return_type])
FC-82    Date & Time      2    WEEKNUM            Converts a serial number to a number representing where the week            =WEEKNUM(serial_number,[return_type])
                                                  falls numerically with a year
FC-83    Date & Time      2    WORKDAY            Returns the serial number of the date before or after a specified           =WORKDAY(start_date, days, [holidays])
                                                  number of workdays
FC-84    Date & Time      2    WORKDAY.INTL       Returns the serial number of the date before or after a specified           =WORKDAY.INTL(start_date,days,weekend,holidays)
                                                  number of workdays using parameters to indicate which and how many
                                                  days are weekend days
FC-85    Date & Time      2    YEARFRAC           Returns the year fraction representing the number of whole days             =YEARFRAC(start_date,end_date,basis)
                                                  between start_date and end_date
FC-86    Engineering      2    CONVERT            Converts a number from one measurement system to another                    =CONVERT(number,from_unit,to_unit)
FC-87    Engineering      2    DELTA              Tests whether two values are equal                                          =DELTA(number1,number2)
FC-88    Engineering      2    ERF                Returns the error function                                                  =ERF(lower_limit,upper_limit)
FC-89    Engineering      2    ERFC               Returns the complementary error function                                    =ERFC(x)
FC-90    Engineering      2    GESTEP             Tests whether a number is greater than a threshold value                    =GESTEP(number,step)
FC-91    Financial        2    AMORDEGRC          Returns the depreciation for each accounting period by using a              =AMORDEGRC(cost,date_purchased,first_period,salvage,peri
                                                  depreciation coefficient                                                    od,rate,basis)
FC-92    Financial        2    AMORLINC           Returns the depreciation for each accounting period                         =AMORLINC(cost,date_purchased,first_period,salvage,perio
                                                                                                                              d,rate,basis)
FC-93    Financial        2    DOLLARDE           Converts a dollar price, expressed as a fraction, into a dollar price,      =DOLLARDE(fractional_dollar,fraction)
                                                  expressed as a decimal number
FC-94    Financial        2    DOLLARFR           Converts a dollar price, expressed as a decimal number, into a dollar       =DOLLARFR(decimal_dollar,fraction)
                                                  price, expressed as a fraction
FC-95    Financial        2    SLN                Returns the straight-line depreciation of an asset for one period           =SLN(cost,salvage,life)

FC-96    Financial        2    SYD                Returns the sum-of-years' digits depreciation of an asset for a specified   =SYD(cost,salvage,life,per)
                                                  period
FC-97    Information      2    ERROR.TYPE         Returns a number corresponding to an error type                             =ERROR.TYPE(error_val)
FC-98    Information      2    INFO               Returns information about the current operating environment                 =INFO(type_text)
FC-99    Information      2    ISERR              Returns TRUE if the value is any error value except #N/A                    =ISERR(value)
FC-100   Information      2    ISEVEN             Returns TRUE if the number is even                                          =ISEVEN(number)
FC-101   Information      2    ISLOGICAL          Returns TRUE if the value is a logical value                                =ISLOGICAL(value)
FC-102   Information      2    ISNA               Returns TRUE if the value is the #N/A error value                           =ISNA(value)
FC-103   Information      2    ISODD              Returns TRUE if the number is odd                                           =ISODD(number)
FC-104   Information      2    ISREF              Returns TRUE if the value is a reference                                    =ISREF(value)
FC-105   Information      2    N                  Returns a value converted to a number                                       =N(value)
FC-106   Information      2    NA                 Returns the error value #N/A                                                =NA()
FC-107   Information      2    TYPE               Returns a number indicating the data type of a value                        =TYPE(value)
FC-108   Lookup/Ref.      2    CHOOSE             Chooses a value from a list of values                                       =CHOOSE(index_num,value1,value2,...)
FC-109   Lookup/Ref.      2    GETPIVOTDATA       Returns data stored in a PivotTable report                                  =GETPIVOTDATA(data_field,pivot_table,field,item,...)
FC-110   Lookup/Ref.      2    HYPERLINK          Creates a shortcut or jump that opens a document stored on a network        =HYPERLINK(link_location,friendly_name)
                                                  server, an intranet, or the Internet
FC-111   Lookup/Ref.      2    TRANSPOSE          Returns the transpose of an array                                           =TRANSPOSE(array)
FC-112   Math & Trig      2    CEILING            Rounds a number to the nearest integer or to the nearest multiple of        =CEILING(number,significance)
                                                  significance
FC-113   Math & Trig      2    CEILING.PRECISE    Rounds a number the nearest integer or to the nearest multiple of           =CEILING.PRECISE(number,significance)
                                                  significance. Regardless of the sign of the number, the number is
                                                  rounded up.
FC-114   Math & Trig      2    EVEN               Rounds a number up to the nearest even integer                              =EVEN(number)
FC-115   Math & Trig      2    EXP                Returns e raised to the power of a given number                             =EXP(number)
FC-116   Math & Trig      2    FACT               Returns the factorial of a number                                           =FACT(number)
FC-117   Math & Trig      2    FLOOR              Rounds a number down, toward zero                                           =FLOOR(number,significance)
FC-118   Math & Trig      2    FLOOR.PRECISE      Rounds a number the nearest integer or to the nearest multiple of           =FLOOR.PRECISE(number,significance)
                                                  significance. Regardless of the sign of the number, the number is
                                                  rounded up.
FC-119   Math & Trig      2    GCD                Returns the greatest common divisor                                         =GCD(number1,number2,...)
FC-120   Math & Trig      2    INT                Rounds a number down to the nearest integer                                 =INT(number)
FC-121   Math & Trig      2    ISO.CEILING        Returns a number that is rounded up to the nearest integer or to the        =ISO.CEILING(number,significance)
                                                  nearest multiple of significance
FC-122   Math & Trig      2    LCM                Returns the least common multiple                                           =LCM(number1,number2,...)
FC-123   Math & Trig      2    MOD                Returns the remainder from division                                         =MOD(number,divisor)
FC-124   Math & Trig      2    MROUND             Returns a number rounded to the desired multiple                            =MROUND(number,multiple)
FC-125   Math & Trig      2    ODD                Rounds a number up to the nearest odd integer                               =ODD(number)
FC-126   Math & Trig      2    PI                 Returns the value of pi                                                     =PI()
FC-127   Math & Trig      2    POWER              Returns the result of a number raised to a power                            =POWER(number,power)
FC-128   Math & Trig      2    QUOTIENT           Returns the integer portion of a division                                   =QUOTIENT(numerator,denominator)
FC-129   Math & Trig      2    SERIESSUM          Returns the sum of a power series based on the formula                      =SERIESSUM(x,n,m,coefficients)
FC-130   Math & Trig      2    SIGN               Returns the sign of a number                                                =SIGN(number)
FC-131   Math & Trig      2    SQRT               Returns a positive square root                                              =SQRT(number)
FC-132   Math & Trig      2    SUMSQ              Returns the sum of the squares of the arguments                             =SUMSQ(number1,number2,...)
FC-133   Math & Trig      2    TRUNC              Truncates a number to an integer                                            =TRUNC(number,num_digits)
FC-134   Statistical      2    AVERAGEA           Returns the average of its arguments, including numbers, text, and          =AVERAGEA(value1,value2,...)
                                                  logical values
FC-135   Statistical      2    AVERAGEIFS         Returns the average (arithmetic mean) of all cells that meet multiple       =AVERAGEIFS(average_range,criteria_range,criteria,...)
                                                  criteria.
FC-136   Statistical      2    GEOMEAN            Returns the geometric mean                                                  =GEOMEAN(number1,number2,...)
FC-137   Statistical      2    INTERCEPT          Returns the intercept of the linear regression line                         =INTERCEPT(known_y's,known_x's)




                                                                                        Page 5 of 53
Excel Functions                                                                                                                                                                        Level 1


Ref.     Category        Level   Function       Description                                                                Syntax -                                               Favorites
FC-138   Statistical        2    LARGE          Returns the k-th largest value in a data set                               =LARGE(array,k)
FC-139   Statistical        2    LINEST         Returns the parameters of a linear trend                                   =LINEST(known_y's,known_x's,const,stats)
FC-140   Statistical        2    LOGEST         Returns the parameters of an exponential trend                             =LOGEST(known_y's,known_x's,const,stats)
FC-141   Statistical        2    MAXA           Returns the maximum value in a list of arguments, including numbers,       =MAXA(value1,value2,...)
                                                text, and logical values
FC-142   Statistical        2    MINA           Returns the smallest value in a list of arguments, including numbers,      =MINA(value1,value2,...)
                                                text, and logical values
FC-143   Statistical        2    MODE.MULT      Returns a vertical array of the most frequently occurring, or repetitive   =MODE.MULT(number1,number2,...)
                                                values in an array or range of data
FC-144   Statistical        2    MODE.SNGL      Returns the most common value in a data set                                =MODE.SNGL(number1,number2,...)
FC-145   Statistical        2    PROB           Returns the probability that values in a range are between two limits      =PROB(x_range,prob_range,lower_limit,upper_limit)

FC-146   Statistical        2    RANK.AVG       Returns the rank of a number in a list of numbers                          =RANK.AVG(number,ref,order)
FC-147   Statistical        2    RANK.EQ        Returns the rank of a number in a list of numbers                          =RANK.EQ(number,ref,order)
FC-148   Statistical        2    SKEW           Returns the skewness of a distribution                                     =SKEW(number1,number2,...)
FC-149   Statistical        2    SLOPE          Returns the slope of the linear regression line                            =SLOPE(known_y's,known_x's)
FC-150   Statistical        2    SMALL          Returns the k-th smallest value in a data set                              =SMALL(array,k)
FC-151   Statistical        2    STANDARDIZE    Returns a normalized value                                                 =STANDARDIZE(x,mean,standard_dev)
FC-152   Statistical        2    TREND          Returns values along a linear trend                                        =TREND(known_y's,known_x's,new_x's,const)
FC-153   Text               2    CHAR           Returns the character specified by the code number                         =CHAR(number)
FC-154   Text               2    CLEAN          Removes all nonprintable characters from text                              =CLEAN(text)
FC-155   Text               2    CODE           Returns a numeric code for the first character in a text string            =CODE(text)
FC-156   Text               2    DOLLAR         Converts a number to text, using the $ (dollar) currency format            =DOLLAR(number,decimals)
FC-157   Text               2    FIXED          Formats a number as text with a fixed number of decimals                   =FIXED(number,decimals,no_commas)
FC-158   Text               2    PHONETIC       Extracts the phonetic (furigana) characters from a text string             =PHONETIC(reference)
FC-159   Text               2    REPT           Repeats text a given number of times                                       =REPT(text,number_times)
FC-160   Text               2    SUBSTITUTE     Substitutes new text for old text in a text string                         =SUBSTITUTE(text,old_text,new_text,instance_num)
FC-161   Text               2    T              Converts its arguments to text                                             =T(value)
FC-162   Text               2    VALUE          Converts a text argument to a number                                       =VALUE(text)
FC-163   Compatibility      3    BINOMDIST      Returns the individual term binomial distribution probability              =BINOMDIST(number_s,trials,probability_s,cumulative)

FC-164   Compatibility      3    CHIDIST        Returns the one-tailed probability of the chi-squared distribution         =CHIDIST(x,deg_freedom)

FC-165   Compatibility      3    CHIINV         Returns the inverse of the one-tailed probability of the chi-squared       =CHIINV(probability,deg_freedom)
                                                distribution
FC-166   Compatibility      3    CHITEST        Returns the test for independence                                          =CHITEST(actual_range,expected_range)
FC-167   Compatibility      3    CONFIDENCE     Returns the confidence interval for a population mean                      =CONFIDENCE(alpha,standard_dev,size)
FC-168   Compatibility      3    FTEST                                                                                     =FTEST(array1,array2)
FC-169   Compatibility      3    LOGINV         Returns the inverse of the lognormal cumulative distribution               =LOGINV(probability,mean,standard_dev)
FC-170   Compatibility      3    LOGNORMDIST    Returns the cumulative lognormal distribution                              =LOGNORMDIST(x,mean,standard_dev)
FC-171   Compatibility      3    MODE           Returns the most common value in a data set                                =MODE(number1,number2,...)
FC-172   Compatibility      3    NORMDIST       Returns the normal cumulative distribution                                 =NORMDIST(x,mean,standard_dev,cumulative)
FC-173   Compatibility      3    NORMINV        Returns the inverse of the normal cumulative distribution                  =NORMINV(probability,mean,standard_dev)
FC-174   Compatibility      3    NORMSDIST      Returns the standard normal cumulative distribution                        =NORMSDIST(z)
FC-175   Compatibility      3    NORMSINV       Returns the inverse of the standard normal cumulative distribution         =NORMSINV(probability)

FC-176   Compatibility      3    PERCENTILE     Returns the k-th percentile of values in a range                           =PERCENTILE(array,k)
FC-177   Compatibility      3    PERCENTRANK    Returns the percentage rank of a value in a data set                       =PERCENTRANK(array,x,significance)
FC-178   Compatibility      3    POISSON        Returns the Poisson distribution                                           =POISSON(x,mean,cumulative)
FC-179   Compatibility      3    QUARTILE       Returns the quartile of a data set                                         =QUARTILE(array,quart)
FC-180   Compatibility      3    RANK           Returns the rank of a number in a list of numbers                          =RANK(number,ref,order)
FC-181   Compatibility      3    STDEV          Estimates standard deviation based on a sample                             =STDEV(number1,number2,...)
FC-182   Compatibility      3    STDEVP         Calculates standard deviation based on the entire population               =STDEVP(number1,number2,...)
FC-183   Compatibility      3    TDIST          Returns the Student's t-distribution                                       =TDIST(x,deg_freedom,tails)
FC-184   Compatibility      3    TINV           Returns the inverse of the Student's t-distribution                        =TINV(probability,deg_freedom)
FC-185   Compatibility      3    VAR            Estimates variance based on a sample                                       =VAR(number1,number2,...)
FC-186   Compatibility      3    VARP           Calculates variance based on the entire population                         =VARP(number1,number2,...)
FC-187   Database           3    DAVERAGE       Returns the average of selected database entries                           =DAVERAGE(database,field,criteria)
FC-188   Database           3    DCOUNT         Counts the cells that contain numbers in a database                        =DCOUNT(database,field,criteria)
FC-189   Database           3    DCOUNTA        Counts nonblank cells in a database                                        =DCOUNTA(database,field,criteria)
FC-190   Database           3    DMAX           Returns the maximum value from selected database entries                   =DMAX(database,field,criteria)
FC-191   Database           3    DMIN           Returns the minimum value from selected database entries                   =DMIN(database,field,criteria)
FC-192   Database           3    DPRODUCT       Multiplies the values in a particular field of records that match the      =DPRODUCT(database,field,criteria)
                                                criteria in a database
FC-193   Database           3    DSTDEV         Estimates the standard deviation based on a sample of selected             =DSTDEV(database,field,criteria)
                                                database entries
FC-194   Database           3    DSTDEVP        Calculates the standard deviation based on the entire population of        =DSTDEVP(database,field,criteria)
                                                selected database entries
FC-195   Database           3    DVAR           Estimates variance based on a sample from selected database entries        =DVAR(database,field,criteria)

FC-196   Database           3    DVARP          Calculates variance based on the entire population of selected             =DVARP(database,field,criteria)
                                                database entries
FC-197   Engineering        3    ERF.PRECISE    Returns the error function                                                 =ERF.PRECISE(X)
FC-198   Engineering        3    ERFC.PRECISE   Returns the complementary ERF function integrated between x and            =ERFC.PRECISE(X)
                                                infinity
FC-199   Financial          3    DB             Returns the depreciation of an asset for a specified period by using the   =DB(cost,salvage,life,period,month)
                                                fixed-declining balance method
FC-200   Financial          3    DDB            Returns the depreciation of an asset for a specified period by using the   =DDB(cost,salvage,life,period,factor)
                                                double-declining balance method or some other method that you
                                                specify
FC-201   Financial          3    EFFECT         Returns the effective annual interest rate                                 =EFFECT(nominal_rate,npery)
FC-202   Financial          3    FV             Returns the future value of an investment                                  =FV(rate,nper,pmt,pv,type)
FC-203   Financial          3    IPMT           Returns the interest payment for an investment for a given period          =IPMT(rate,per,nper,pv,fv,type)

FC-204   Financial          3    IRR            Returns the internal rate of return for a series of cash flows             =IRR(values,guess)
FC-205   Financial          3    MIRR           Returns the internal rate of return where positive and negative cash       =MIRR(values,finance_rate,reinvest_rate)
                                                flows are financed at different rates
FC-206   Financial          3    NOMINAL        Returns the annual nominal interest rate                                   =NOMINAL(effect_rate,npery)
FC-207   Financial          3    NPER           Returns the number of periods for an investment                            =NPER(rate,pmt,pv,fv,type)




                                                                                      Page 6 of 53
Excel Functions                                                                                                                                                                          Level 1


Ref.     Category       Level   Function          Description                                                               Syntax -                                                Favorites
FC-208   Financial         3    NPV               Returns the net present value of an investment based on a series of       =NPV(rate,value1,value2,...)
                                                  periodic cash flows and a discount rate
FC-209   Financial         3    PV                Returns the present value of an investment                       =PV(rate,nper,pmt,fv,type)
FC-210   Financial         3    RATE              Returns the interest rate per period of an annuity               =RATE(nper,pmt,pv,fv,type,guess)
FC-211   Financial         3    YIELD             Returns the yield on a security that pays periodic interest      =YIELD(settlement,maturity,rate,pr,redemption,frequency,b
                                                                                                                   asis)
FC-212   Lookup/Ref.       3    AREAS             Returns the number of areas in a reference                       =AREAS(reference)
FC-213   Lookup/Ref.       3    RTD               ########################################################## =RTD(progID,server,topic1,topic2,...)
FC-214   Math & Trig       3    AGGREGATE         Returns an aggregate in a list or database                       =AGGREGATE(function_num,options,array,k)
FC-215   Math & Trig       3    COMBIN            Returns the number of combinations for a given number of objects =COMBIN(number,number_chosen)

FC-216   Math & Trig       3    COS               Returns the cosine of a number                                            =COS(number)
FC-217   Math & Trig       3    COSH              Returns the hyperbolic cosine of a number                                 =COSH(number)
FC-218   Math & Trig       3    FACTDOUBLE        Returns the double factorial of a number                                  =FACTDOUBLE(number)
FC-219   Math & Trig       3    LN                Returns the natural logarithm of a number                                 =LN(number)
FC-220   Math & Trig       3    LOG               Returns the logarithm of a number to a specified base                     =LOG(number,base)
FC-221   Math & Trig       3    LOG10             Returns the base-10 logarithm of a number                                 =LOG10(number)
FC-222   Math & Trig       3    MULTINOMIAL       Returns the multinomial of a set of numbers                               =MULTINOMIAL(number1,number2,...)
FC-223   Math & Trig       3    SIN               Returns the sine of the given angle                                       =SIN(number)
FC-224   Math & Trig       3    SINH              Returns the hyperbolic sine of a number                                   =SINH(number)
FC-225   Math & Trig       3    SUMX2MY2          Returns the sum of the difference of squares of corresponding values in   =SUMX2MY2(array_x,array_y)
                                                  two arrays
FC-226   Math & Trig       3    SUMX2PY2          Returns the sum of the sum of squares of corresponding values in two      =SUMX2PY2(array_x,array_y)
                                                  arrays
FC-227   Math & Trig       3    SUMXMY2           Returns the sum of squares of differences of corresponding values in      =SUMXMY2(array_x,array_y)
                                                  two arrays
FC-228   Math & Trig       3    TAN               Returns the tangent of a number                                           =TAN(number)
FC-229   Math & Trig       3    TANH              Returns the hyperbolic tangent of a number                                =TANH(number)
FC-230   RStatistical      3    NORM.S.INV        Returns the inverse of the standard normal cumulative distribution        =NORM.S.INV(probability)

FC-231   Statistical       3    AVEDEV            Returns the average of the absolute deviations of data points from        =AVEDEV(number1,number2,...)
                                                  their mean
FC-232   Statistical       3    BETA.DIST         Returns the beta cumulative distribution function                         =BETA.DIST(x,alpha,beta,cumulative,A,B)
FC-233   Statistical       3    BETA.INV          Returns the inverse of the cumulative distribution function for a         =BETA.INV(probability,alpha,beta,A,B)
                                                  specified beta distribution
FC-234   Statistical       3    BINOM.DIST        Returns the individual term binomial distribution probability             =BINOM.DIST(number_s,trials,probability_s,cumulative)

FC-235   Statistical       3    BINOM.INV         Returns the smallest value for which the cumulative binomial              =BINOM.INV(trials,probability_s,alpha)
                                                  distribution is less than or equal to a criterion value
FC-236   Statistical       3    CHISQ.DIST        Returns the cumulative beta probability density function                  =CHISQ.DIST(x,deg_freedom,cumulative)
FC-237   Statistical       3    CHISQ.DIST.RT     Returns the one-tailed probability of the chi-squared distribution        =CHISQ.DIST.RT(x,deg_freedom)

FC-238   Statistical       3    CHISQ.INV         Returns the cumulative beta probability density function                  =CHISQ.INV(probability,deg_freedom)
FC-239   Statistical       3    CHISQ.INV.RT      Returns the inverse of the one-tailed probability of the chi-squared      =CHISQ.INV.RT(probability,deg_freedom)
                                                  distribution
FC-240   Statistical       3    CHISQ.TEST        Returns the test for independence                                         =CHISQ.TEST(actual_range,expected_range)
FC-241   Statistical       3    CONFIDENCE.NORM   Returns the confidence interval for a population mean                     =CONFIDENCE.NORM(alpha,standard_dev,size)
FC-242   Statistical       3    CONFIDENCE.T      Returns the confidence interval for a population mean, using a            =CONFIDENCE.T(alpha,standard_dev,size)
                                                  Student's t distribution
FC-243   Statistical       3    CORREL            Returns the correlation coefficient between two data sets                 =CORREL(array1,array2)
FC-244   Statistical       3    COVARIANCE.P      Returns covariance, the average of the products of paired deviations      =COVARIANCE.P(array1,array2)

FC-245   Statistical       3    COVARIANCE.S      Returns the sample covariance, the average of the products deviations     =COVARIANCE.S(array1,array2)
                                                  for each data point pair in two data sets
FC-246   Statistical       3    DEVSQ             Returns the sum of squares of deviations                                  =DEVSQ(number1,number2,...)
FC-247   Statistical       3    EXPON.DIST        Returns the exponential distribution                                      =EXPON.DIST(x,lambda,cumulative)
FC-248   Statistical       3    F.DIST            Returns the F probability distribution                                    =F.DIST(x,deg_freedom1,deg_freedom2,cumulative)
FC-249   Statistical       3    F.DIST.RT         Returns the F probability distribution                                    =F.DIST.RT(x,deg_freedom1,deg_freedom2)
FC-250   Statistical       3    F.INV             Returns the inverse of the F probability distribution                     =F.INV(probability,deg_freedom1,deg_freedom2)
FC-251   Statistical       3    F.INV.RT          Returns the inverse of the F probability distribution                     =F.INV.RT(probability,deg_freedom1,deg_freedom2)
FC-252   Statistical       3    F.TEST            Returns the result of an F-test                                           =F.TEST(array1,array2)
FC-253   Statistical       3    FINV              Returns the inverse of the F probability distribution                     =FINV(probability,deg_freedom1,deg_freedom2)
FC-254   Statistical       3    FISHER            Returns the Fisher transformation                                         =FISHER(x)
FC-255   Statistical       3    FISHERINV         Returns the inverse of the Fisher transformation                          =FISHERINV(y)
FC-256   Statistical       3    FORECAST          Returns a value along a linear trend                                      =FORECAST(x,known_y's,known_x's)
FC-257   Statistical       3    FREQUENCY         Returns a frequency distribution as a vertical array                      =FREQUENCY(data_array,bins_array)
FC-258   Statistical       3    GAMMA.DIST        Returns the gamma distribution                                            =GAMMA.DIST(x,alpha,beta,cumulative)
FC-259   Statistical       3    GAMMA.INV         Returns the inverse of the gamma cumulative distribution                  =GAMMA.INV(probability,alpha,beta)
FC-260   Statistical       3    GAMMALN           Returns the natural logarithm of the gamma function, Γ(x)                 =GAMMALN(x)
FC-261   Statistical       3    GAMMALN.PRECISE   Returns the natural logarithm of the gamma function, Γ(x)                 =GAMMALN.PRECISE(x)
FC-262   Statistical       3    GROWTH            Returns values along an exponential trend                                 =GROWTH(known_y's,known_x's,new_x's,const)
FC-263   Statistical       3    HARMEAN           Returns the harmonic mean                                                 =HARMEAN(number1,number2,...)
FC-264   Statistical       3    HYPGEOM.DIST      Returns the hypergeometric distribution                                   =HYPGEOM.DIST(sample_s,number_sample,population_s,nu
                                                                                                                            mber_pop,cumulative)
FC-265   Statistical       3    KURT              Returns the kurtosis of a data set                                        =KURT(number1,number2,...)
FC-266   Statistical       3    LOGNORM.DIST      Returns the cumulative lognormal distribution                             =LOGNORM.DIST(x,mean,standard_dev,cumulative)
FC-267   Statistical       3    LOGNORM.INV       Returns the inverse of the lognormal cumulative distribution              =LOGNORM.INV(probability,mean,standard_dev)
FC-268   Statistical       3    NEGBINOM.DIST     Returns the negative binomial distribution                                =NEGBINOM.DIST(number_f,number_s,probability_s,cumula
                                                                                                                            tive)
FC-269   Statistical       3    NORM.DIST         Returns the normal cumulative distribution                                =NORM.DIST(x,mean,standard_dev,cumulative)
FC-270   Statistical       3    NORM.INV          Returns the inverse of the normal cumulative distribution                 =NORM.INV(probability,mean,standard_dev)
FC-271   Statistical       3    NORM.S.DIST       Returns the standard normal cumulative distribution                       =NORM.S.DIST(z,cumulative)
FC-272   Statistical       3    PEARSON           Returns the Pearson product moment correlation coefficient                =PEARSON(array1,array2)
FC-273   Statistical       3    PERCENTILE.EXC    Returns the k-th percentile of values in a range, where k is in the range =PERCENTILE.EXC(array,k)
                                                  0..1, exclusive
FC-274   Statistical       3    PERCENTILE.INC    Returns the k-th percentile of values in a range                          =PERCENTILE.INC(array,k)
FC-275   Statistical       3    PERCENTRANK.EXC   Returns the rank of a value in a data set as a percentage (0..1,          =PERCENTRANK.EXC(array,x,significance)
                                                  exclusive) of the data set
FC-276   Statistical       3    PERCENTRANK.INC   Returns the percentage rank of a value in a data set                      =PERCENTRANK.INC(array,x,significance)




                                                                                       Page 7 of 53
Excel Functions                                                                                                                                                                   Level 1


Ref.     Category        Level   Function       Description                                                                Syntax -                                          Favorites
FC-277   Statistical        3    PERMUT         Returns the number of permutations for a given number of objects           =PERMUT(number,number_chosen)

FC-278   Statistical        3    POISSON.DIST   Returns the Poisson distribution                                           =POISSON.DIST(x,mean,cumulative)
FC-279   Statistical        3    QUARTILE.EXC   Returns the quartile of the data set, based on percentile values from      =QUARTILE.EXC(array,quart)
                                                0..1, exclusive
FC-280   Statistical        3    QUARTILE.INC   Returns the quartile of a data set                                         =QUARTILE.INC(array,quart)
FC-281   Statistical        3    RSQ            Returns the square of the Pearson product moment correlation               =RSQ(known_y's,known_x's)
                                                coefficient
FC-282   Statistical        3    STDEV.P        Calculates standard deviation based on the entire population               =STDEV.P(number1,number2,...)
FC-283   Statistical        3    STDEV.S        Estimates standard deviation based on a sample                             =STDEV.S(number1,number2,...)
FC-284   Statistical        3    STDEVA         Estimates standard deviation based on a sample, including numbers,         =STDEVA(value1,value2,...)
                                                text, and logical values
FC-285   Statistical        3    STDEVPA        Calculates standard deviation based on the entire population, including    =STDEVPA(value1,value2,...)
                                                numbers, text, and logical values
FC-286   Statistical        3    STEYX          Returns the standard error of the predicted y-value for each x in the      =STEYX(known_y's,known_x's)
                                                regression
FC-287   Statistical        3    T.DIST         Returns the Percentage Points (probability) for the Student t-             =T.DIST(x,deg_freedom,cumulative)
                                                distribution
FC-288   Statistical        3    T.DIST.2T      Returns the Percentage Points (probability) for the Student t-             =T.DIST.2T(x,deg_freedom)
                                                distribution
FC-289   Statistical        3    T.DIST.RT      Returns the Student's t-distribution                                       =T.DIST.RT(x,deg_freedom)
FC-290   Statistical        3    T.INV          Returns the t-value of the Student's t-distribution as a function of the   =T.INV(probability,deg_freedom)
                                                probability and the degrees of freedom
FC-291   Statistical        3    T.INV.2T       Returns the inverse of the Student's t-distribution                        =T.INV.2T(probability,deg_freedom)
FC-292   Statistical        3    T.TEST         Returns the probability associated with a Student's t-test                 =T.TEST(array1,array2,tails,type)
FC-293   Statistical        3    TRIMMEAN       Returns the mean of the interior of a data set                             =TRIMMEAN(array,percent)
FC-294   Statistical        3    VAR.P          Calculates variance based on the entire population                         =VAR.P(number1,number2,...)
FC-295   Statistical        3    VAR.S          Estimates variance based on a sample                                       =VAR.S(number1,number2,...)
FC-296   Statistical        3    VARA           Estimates variance based on a sample, including numbers, text, and         =VARA(value1,value2,...)
                                                logical values
FC-297   Statistical        3    VARPA          Calculates variance based on the entire population, including numbers,     =VARPA(value1,value2,...)
                                                text, and logical values
FC-298   Statistical        3    WEIBULL.DIST   Returns the Weibull distribution                                           =WEIBULL.DIST(x,alpha,beta,cumulative)
FC-299   Statistical        3    Z.TEST         Returns the one-tailed probability-value of a z-test                       =Z.TEST(array,x,sigma)
FC-300   Text               3    ASC            Changes full-width (double-byte) English letters or katakana within a      =ASC(text)
                                                character string to half-width (single-byte) characters
FC-301   Compatibility      4    BETADIST       Returns the beta cumulative distribution function                          =BETADIST(x,alpha,beta,A,B)
FC-302   Compatibility      4    BETAINV        Returns the inverse of the cumulative distribution function for a          =BETAINV(probability,alpha,beta,A,B)
                                                specified beta distribution
FC-303   Compatibility      4    COVAR          Returns covariance, the average of the products of paired deviations       =COVAR(array1,array2)

FC-304   Compatibility      4    CRITBINOM      Returns the smallest value for which the cumulative binomial               =CRITBINOM(trials,probability_s,alpha)
                                                distribution is less than or equal to a criterion value
FC-305   Compatibility      4    EXPONDIST      Returns the exponential distribution                                   =EXPONDIST(x,lambda,cumulative)
FC-306   Compatibility      4    FDIST          Returns the F probability distribution                                 =FDIST(x,deg_freedom1,deg_freedom2)
FC-307   Compatibility      4    GAMMADIST      Returns the gamma distribution                                         =GAMMADIST(x,alpha,beta,cumulative)
FC-308   Compatibility      4    GAMMAINV       Returns the inverse of the gamma cumulative distribution               =GAMMAINV(probability,alpha,beta)
FC-309   Compatibility      4    HYPGEOMDIST    Returns the hypergeometric distribution                                =HYPGEOMDIST(sample_s,number_sample,population_s,nu
                                                                                                                       mber_pop)
FC-310   Compatibility      4    NEGBINOMDIST   Returns the negative binomial distribution                             =NEGBINOMDIST(number_f,number_s,probability_s)
FC-311   Compatibility      4    TTEST          Returns the probability associated with a Student's t-test             =TTEST(array1,array2,tails,type)
FC-312   Compatibility      4    WEIBULL        Calculates variance based on the entire population, including numbers, =WEIBULL(x,alpha,beta,cumulative)
                                                text, and logical values
FC-313   Compatibility      4    ZTEST          Returns the one-tailed probability-value of a z-test                   =ZTEST(array,x,sigma)
FC-314   Engineering        4    BESSELI        Returns the modified Bessel function In(x)                             =BESSELI(x,n)
FC-315   Engineering        4    BESSELJ        Returns the Bessel function Jn(x)                                      =BESSELJ(x,n)
FC-316   Engineering        4    BESSELK        Returns the modified Bessel function Kn(x)                             =BESSELK(x,n)
FC-317   Engineering        4    BESSELY        Returns the Bessel function Yn(x)                                      =BESSELY(x,n)
FC-318   Engineering        4    BIN2DEC        Converts a binary number to decimal                                    =BIN2DEC(number)
FC-319   Engineering        4    BIN2HEX        Converts a binary number to hexadecimal                                =BIN2HEX(number,places)
FC-320   Engineering        4    BIN2OCT        Converts a binary number to octal                                      =BIN2OCT(number,places)
FC-321   Engineering        4    COMPLEX        Converts real and imaginary coefficients into a complex number         =COMPLEX(real_num,i_num,suffix)

FC-322   Engineering        4    DEC2BIN        Converts a decimal number to binary                                        =DEC2BIN(number,places)
FC-323   Engineering        4    DEC2HEX        Converts a decimal number to hexadecimal                                   =DEC2HEX(number,places)
FC-324   Engineering        4    DEC2OCT        Converts a decimal number to octal                                         =DEC2OCT(number,places)
FC-325   Engineering        4    HEX2BIN        Converts a hexadecimal number to binary                                    =HEX2BIN(number,places)
FC-326   Engineering        4    HEX2DEC        Converts a hexadecimal number to decimal                                   =HEX2DEC(number)
FC-327   Engineering        4    HEX2OCT        Converts a hexadecimal number to octal                                     =HEX2OCT(number,places)
FC-328   Engineering        4    IMABS          Returns the absolute value (modulus) of a complex number                   =IMABS(inumber)
FC-329   Engineering        4    IMAGINARY      Returns the imaginary coefficient of a complex number                      =IMAGINARY(inumber)
FC-330   Engineering        4    IMARGUMENT     Returns the argument theta, an angle expressed in radians                  =IMARGUMENT(inumber)
FC-331   Engineering        4    IMCONJUGATE    Returns the complex conjugate of a complex number                          =IMCONJUGATE(inumber)
FC-332   Engineering        4    IMCOS          Returns the cosine of a complex number                                     =IMCOS(inumber)
FC-333   Engineering        4    IMDIV          Returns the quotient of two complex numbers                                =IMDIV(inumber1,inumber2)
FC-334   Engineering        4    IMEXP          Returns the exponential of a complex number                                =IMEXP(inumber)
FC-335   Engineering        4    IMLN           Returns the natural logarithm of a complex number                          =IMLN(inumber)
FC-336   Engineering        4    IMLOG10        Returns the base-10 logarithm of a complex number                          =IMLOG10(inumber)
FC-337   Engineering        4    IMLOG2         Returns the base-2 logarithm of a complex number                           =IMLOG2(inumber)
FC-338   Engineering        4    IMPOWER        Returns a complex number raised to an integer power                        =IMPOWER(inumber,number)
FC-339   Engineering        4    IMPRODUCT      Returns the product of complex numbers                                     =IMPRODUCT(inumber1,inumber2,...)
FC-340   Engineering        4    IMREAL         Returns the real coefficient of a complex number                           =IMREAL(inumber)
FC-341   Engineering        4    IMSIN          Returns the sine of a complex number                                       =IMSIN(inumber)
FC-342   Engineering        4    IMSQRT         Returns the square root of a complex number                                =IMSQRT(inumber)
FC-343   Engineering        4    IMSUB          Returns the difference between two complex numbers                         =IMSUB(inumber1,inumber2)
FC-344   Engineering        4    IMSUM          Returns the sum of complex numbers                                         =IMSUM(inumber1,inumber2,...)
FC-345   Engineering        4    OCT2BIN        Converts an octal number to binary                                         =OCT2BIN(number,places)
FC-346   Engineering        4    OCT2DEC        Converts an octal number to decimal                                        =OCT2DEC(number)
FC-347   Engineering        4    OCT2HEX        Converts an octal number to hexadecimal                                    =OCT2HEX(number,places)




                                                                                     Page 8 of 53
Excel Functions                                                                                                                                                                                     Level 1


Ref.     Category      Level   Function             Description                                                                   Syntax -                                                     Favorites
FC-348   Financial        4    ACCRINT              Returns the accrued interest for a security that pays periodic interest       =ACCRINT(issue,first_interest,settlement,rate,par,frequency,
                                                                                                                                  basis,calc_method)
FC-349   Financial        4    ACCRINTM             Returns the accrued interest for a security that pays interest at             =ACCRINTM(issue,settlement,rate,par,basis)
                                                    maturity
FC-350   Financial        4    COUPDAYBS            Returns the number of days from the beginning of the coupon period            =COUPDAYBS(settlement,maturity,frequency,basis)
                                                    to the settlement date
FC-351   Financial        4    COUPDAYS             Returns the number of days in the coupon period that contains the             =COUPDAYS(settlement,maturity,frequency,basis)
                                                    settlement date
FC-352   Financial        4    COUPDAYSNC           Returns the number of days from the settlement date to the next               =COUPDAYSNC(settlement,maturity,frequency,basis)
                                                    coupon date
FC-353   Financial        4    COUPNCD              Returns the next coupon date after the settlement date                        =COUPNCD(settlement,maturity,frequency,basis)
FC-354   Financial        4    COUPNUM              Returns the number of coupons payable between the settlement date             =COUPNUM(settlement,maturity,frequency,basis)
                                                    and maturity date
FC-355   Financial        4    COUPPCD              Returns the previous coupon date before the settlement date                   =COUPPCD(settlement,maturity,frequency,basis)
FC-356   Financial        4    CUMIPMT              Returns the cumulative interest paid between two periods                      =CUMIPMT(rate,nper,pv,start_period,end_period,type)

FC-357   Financial        4    CUMPRINC             Returns the cumulative principal paid on a loan between two periods           =CUMPRINC(rate,nper,pv,start_period,end_period,type)

FC-358   Financial        4    DISC                 Returns the discount rate for a security                                      =DISC(settlement,maturity,pr,redemption,basis)
FC-359   Financial        4    DURATION             Returns the annual duration of a security with periodic interest              =DURATION(settlement,maturity,coupon,yld,frequency,basis
                                                    payments                                                                      )
FC-360   Financial        4    FVSCHEDULE           Returns the future value of an initial principal after applying a series of   =FVSCHEDULE(principal,schedule)
                                                    compound interest rates
FC-361   Financial        4    INTRATE              Returns the interest rate for a fully invested security                       =INTRATE(settlement,maturity,investment,redemption,basis
                                                                                                                                  )
FC-362   Financial        4    ISPMT                Calculates the interest paid during a specific period of an investment        =ISPMT(rate,per,nper,pv)

FC-363   Financial        4    MDURATION            Returns the Macauley modified duration for a security with an assumed         =MDURATION(settlement,maturity,coupon,yld,frequency,ba
                                                    par value of $100                                                             sis)
FC-364   Financial        4    ODDFPRICE            Returns the price per $100 face value of a security with an odd first         =ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yld
                                                    period                                                                        ,redemption,frequency,basis)
FC-365   Financial        4    ODDFYIELD            Returns the yield of a security with an odd first period                      =ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,
                                                                                                                                  redemption,frequency,basis)
FC-366   Financial        4    ODDLPRICE            Returns the price per $100 face value of a security with an odd last          =ODDLPRICE(settlement,maturity,last_interest,rate,yld,rede
                                                    period                                                                        mption,frequency,basis)
FC-367   Financial        4    ODDLYIELD            Returns the yield of a security with an odd last period                       =ODDLYIELD(settlement,maturity,last_interest,rate,pr,redem
                                                                                                                                  ption,frequency,basis)
FC-368   Financial        4    PMT                  Returns the periodic payment for an annuity                                   =PMT(rate,nper,pv,fv,type)
FC-369   Financial        4    PPMT                 Returns the payment on the principal for an investment for a given            =PPMT(rate,per,nper,pv,fv,type)
                                                    period
FC-370   Financial        4    PRICE                Returns the price per $100 face value of a security that pays periodic        =PRICE(settlement,maturity,rate,yld,redemption,frequency,
                                                    interest                                                                      basis)
FC-371   Financial        4    PRICEDISC            Returns the price per $100 face value of a discounted security                =PRICEDISC(settlement,maturity,discount,redemption,basis)

FC-372   Financial        4    PRICEMAT             Returns the price per $100 face value of a security that pays interest at =PRICEMAT(settlement,maturity,issue,rate,yld,basis)
                                                    maturity
FC-373   Financial        4    RECEIVED             Returns the amount received at maturity for a fully invested security     =RECEIVED(settlement,maturity,investment,discount,basis)

FC-374   Financial        4    TBILLEQ              Returns the bond-equivalent yield for a Treasury bill                         =TBILLEQ(settlement,maturity,discount)
FC-375   Financial        4    TBILLPRICE           Returns the price per $100 face value for a Treasury bill                     =TBILLPRICE(settlement,maturity,discount)
FC-376   Financial        4    TBILLYIELD           Returns the yield for a Treasury bill                                         =TBILLYIELD(settlement,maturity,pr)
FC-377   Financial        4    VDB                  Returns the depreciation of an asset for a specified or partial period by     =VDB(cost,salvage,life,start_period,end_period,factor,no_sw
                                                    using a declining balance method                                              itch)
FC-378   Financial        4    XIRR                 Returns the internal rate of return for a schedule of cash flows that is      =XIRR(values,dates,guess)
                                                    not necessarily periodic
FC-379   Financial        4    XNPV                 Returns the net present value for a schedule of cash flows that is not        =XNPV(rate,values,dates)
                                                    necessarily periodic
FC-380   Financial        4    YIELDDISC            Returns the annual yield for a discounted security; for example, a            =YIELDDISC(settlement,maturity,pr,redemption,basis)
                                                    Treasury bill
FC-381   Financial        4    YIELDMAT             Returns the annual yield of a security that pays interest at maturity         =YIELDMAT(settlement,maturity,issue,rate,pr,basis)

FC-382   Math & Trig      4    ACOS                 Returns the arccosine of a number                                       =ACOS(number)
FC-383   Math & Trig      4    ACOSH                Returns the inverse hyperbolic cosine of a number                       =ACOSH(number)
FC-384   Math & Trig      4    ASIN                 Returns the arcsine of a number                                         =ASIN(number)
FC-385   Math & Trig      4    ASINH                Returns the inverse hyperbolic sine of a number                         =ASINH(number)
FC-386   Math & Trig      4    ATAN                 Returns the arctangent of a number                                      =ATAN(number)
FC-387   Math & Trig      4    ATAN2                Returns the arctangent from x- and y-coordinates                        =ATAN2(x_num,y_num)
FC-388   Math & Trig      4    ATANH                Returns the inverse hyperbolic tangent of a number                      =ATANH(number)
FC-389   Math & Trig      4    DEGREES              Converts radians to degrees                                             =DEGREES(angle)
FC-390   Math & Trig      4    MDETERM              Returns the matrix determinant of an array                              =MDETERM(array)
FC-391   Math & Trig      4    MINVERSE             Returns the matrix inverse of an array                                  =MINVERSE(array)
FC-392   Math & Trig      4    MMULT                Returns the matrix product of two arrays                                =MMULT(array1,array2)
FC-393   Math & Trig      4    RADIANS              Converts degrees to radians                                             =RADIANS(angle)
FC-394   Math & Trig      4    ROMAN                Converts an arabic numeral to roman, as text                            =ROMAN(number,form)
FC-395   Math & Trig      4    SQRTPI               Returns the square root of (number * pi)                                =SQRTPI(number)
FC-396   Text             4    BAHTTEXT             Converts a number to text, using the ß (baht) currency format           =BAHTTEXT(number)
FC-397   Cube             5    CUBEKPIMEMBER        ##########################################################              =CUBEKPIMEMBER(connection,kpi_name,kpi_property,capti
                                                                                                                            on)
FC-398   Cube             5    CUBEMEMBER           Returns a member or tuple in a cube hierarchy. Use to validate that the =CUBEMEMBER(connection,member_expression,caption)
                                                    member or tuple exists in the cube.
FC-399   Cube             5    CUBEMEMBERPROPERTY   Returns the value of a member property in the cube. Use to validate     =CUBEMEMBERPROPERTY(connection,member_expression,p
                                                    that a member name exists within the cube and to return the specified roperty)
                                                    property for this member.
FC-400   Cube             5    CUBERANKEDMEMBER     Returns the nth, or ranked, member in a set. Use to return one or more =CUBERANKEDMEMBER(connection,set_expression,rank,cap
                                                    elements in a set, such as the top sales performer or top 10 students. tion)

FC-401   Cube             5    CUBESET              Defines a calculated set of members or tuples by sending a set                =CUBESET(connection,set_expression,caption,sort_order,sor
                                                    expression to the cube on the server, which creates the set, and then         t_by)
                                                    returns that set to Microsoft Office Excel.




                                                                                           Page 9 of 53
Excel Functions                                                                                                                                   Level 1


Ref.     Category   Level   Function       Description                                       Syntax -                                        Favorites
FC-402   Cube          5    CUBESETCOUNT   Returns the number of items in a set.             =CUBESETCOUNT(set)
FC-403   Cube          5    CUBEVALUE      Returns an aggregated value from a cube.          =CUBEVALUE(connection,member_expression1,...)




                                                                             Page 10 of 53
Excel Shortcuts                                                                                                                                                                                                    Level 1

Back to Table of Contents

Ref.    Level     Category     Shortcut Method (Alt. Method)   Explanation                                                                                                                                     Favorites
SC-1       1      Data Entry   '                               An apostrophe at the beginning of a cell forces the format 'Text' and data is shown precisely as typed.                                         X
SC-2       1      Data Entry   &                               Can be used in formula to CONCATENATE without using the function. =A1&B1&C1 combines data from all 3 cells.                                     X
SC-3       1      Data Entry   ALT+ENTER                       Starts a new line in the same cell.                                                                                                             X
SC-4       1      Data Entry   BACKSPACE                       Deletes one character to the left in the Formula Bar. Also clears the content of the active cell. In cell editing mode, it deletes the
                                                               character to the left of the insertion point.
SC-5        1     Data Entry   CTRL+'                          Copies a formula from the cell above the active cell into the cell or the Formula Bar.                                                          X
SC-6        1     Data Entry   CTRL+;                          Enters the current date.                                                                                                                        X
SC-7        1     Data Entry   CTRL+C                          Copies the selected cells.                                                                                                                      X
SC-8        1     Data Entry   CTRL+ENTER                      Fills the selected cell range with the current entry.                                                                                           X
SC-9        1     Data Entry   CTRL+" (aka CTRL+SHIFT+')       Copies the value from the cell above the active cell into the cell or the Formula Bar.                                                          X
SC-10       1     Data Entry   CTRL+: (aka CTRL+SHIFT+;)       Enters the current time.                                                                                                                        X
SC-11       1     Data Entry   CTRL+V                          Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an         X
                                                               object, text, or cell contents.
SC-12       1     Data Entry   CTRL+X                          Cuts the selected cells.                                                                                                                        X
SC-13       1     Data Entry   CTRL+Y                          Repeats the last command or action, if possible.                                                                                                X
SC-14       1     Data Entry   CTRL+Z                          Uses the Undo command to reverse the last command or to delete the last entry that you typed.                                                   X
SC-15       1     Data Entry   CTRL+* (aka CTRL+SHIFT+8)       Selects active data area (pivot table / range)                                                                                                  X
SC-16       1     Data Entry   DELETE                          Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments. In cell editing mode, it          X
                                                               deletes the character to the right of the insertion point.
SC-17       1     Data Entry   ENTER                           Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default). In a data form, it moves to the first field   X
                                                               in the next record. Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command. In a
                                                               dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK
                                                               button).
SC-18       1     Data Entry   ESC                             Cancels an entry in the cell or Formula Bar. Closes an open menu or submenu, dialog box, or message window. It also closes full screen          X
                                                               mode when this mode has been applied, and returns to normal screen mode to display the ribbon and status bar again.

SC-19       1     Data Entry   F11                             Creates a chart of the data in the current range in a separate Chart sheet.
SC-20       1     Data Entry   F2                              Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula     X
                                                               Bar when editing in a cell is turned off.
SC-21       1     Data Entry   F9                              Calculates all worksheets in all open workbooks.                                                                                                X
SC-22       1     Data Entry   SHIFT+ENTER                     Completes a cell entry and selects the cell above.
SC-23       1     File         CTRL+N                          Creates a new, blank workbook.
SC-24       1     File         CTRL+O                          Displays the Open dialog box to open or find a file.
SC-25       1     File         CTRL+S                          Saves the active file with its current file name, location, and file format.                                                                    X
SC-26       1     File         SHIFT+F11 (ALT+SHIFT+F1)        Inserts a new worksheet.                                                                                                                        X
SC-27       1     File         CTRL+[DRAG SHEET]               Copies the selected sheet.                                                                                                                      X
SC-28       1     Format       CTRL+B (CTRL+2)                 Applies or removes bold formatting.                                                                                                             X
SC-29       1     Format       CTRL+I (CTRL+3)                 Applies or removes italic formatting.                                                                                                           X
SC-30       1     Format       CTRL+U (CTRL+4)                 Applies or removes underlining.                                                                                                                 X
SC-31       1     Menu         CTRL+1                          Displays the Format Cells dialog box.                                                                                                           X
SC-32       1     Menu         CTRL+F (SHIFT+F5)               Displays the Find and Replace dialog box, with the Find tab selected.                                                                           X
SC-33       1     Menu         CTRL+F1                         Displays or hides the ribbon.
SC-34       1     Menu         CTRL+G (F5)                     Displays the Go To dialog box.                                                                                                                  X
SC-35       1     Menu         CTRL+H                          Displays the Find and Replace dialog box, with the Replace tab selected.                                                                        X
SC-36       1     Menu         F1                              Displays the Excel Help task pane.
SC-37       1     Menu         F12                             Displays the Save As dialog box.
SC-38       1     Menu         F5                              Displays the Go To dialog box.




                                                                                                Page 11 of 53
Excel Shortcuts                                                                                                                                                                                                 Level 1

Ref.    Level   Category     Shortcut Method (Alt. Method)   Explanation                                                                                                                                    Favorites
SC-39      1    Menu         F7                              Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
SC-40      1    Menu         SHIFT+F10                       Displays the shortcut menu for a selected item.
SC-41      1    Menu         SHIFT+F3                        Displays the Insert Function dialog box.
SC-42      1    Navigation   ARROW KEYS                      Move one cell up, down, left, or right in a worksheet.                                                                                         X
SC-43      1    Navigation   CTRL+END                        Moves to the last cell on a worksheet, to the lowest used row of the rightmost used column. If the cursor is in the formula bar,               X
                                                             CTRL+END moves the cursor to the end of the text.
SC-44      1    Navigation   CTRL+HOME                       Moves to the beginning of a worksheet.                                                                                                         X
SC-45      1    Navigation   CTRL+PAGE DOWN                  Moves to the next sheet in a workbook.                                                                                                         X
SC-46      1    Navigation   CTRL+PAGE UP                    Moves to the previous sheet in a workbook.                                                                                                     X
SC-47      1    Navigation   END                             END turns End mode on. In End mode, you can then press an arrow key to move to the next nonblank cell in the same column or row as             X
                                                             the active cell. If the cells are blank, pressing END followed by an arrow key moves to the last cell in the row or column. END also selects
                                                             the last command on the menu when a menu or submenu is visible.
SC-48      1    Navigation   HOME                            Moves to the beginning of a row in a worksheet. Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned            X
                                                             on. Selects the first command on the menu when a menu or submenu is visible.
SC-49      1    Navigation   PAGE DOWN                       Moves one screen down in a worksheet.                                                                                                          X
SC-50      1    Navigation   PAGE UP                         Moves one screen up in a worksheet.                                                                                                            X
SC-51      1    Navigation   SHIFT+TAB                       Moves to the previous cell in a worksheet or the previous option in a dialog box.                                                              X
SC-52      1    Navigation   TAB                             Moves one cell to the right in a worksheet. Moves between unlocked cells in a protected worksheet. Moves to the next option or option          X
                                                             group in a dialog box.
SC-53      1    Select       CTRL+A                          Selects the entire worksheet. If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects         X
                                                             the entire worksheet. When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog
                                                             box.
SC-54      1    Select       CTRL+SHIFT+END                  Extends the selection of cells to the last used cell on the worksheet (lower-right corner). If the cursor is in the formula bar,               X
                                                             CTRL+SHIFT+END selects all text in the formula bar from the cursor position to the end—this does not affect the height of the formula
                                                             bar.
SC-55      1    Select       CTRL+SHIFT+HOME                 Extends the selection of cells to the beginning of the worksheet.                                                                              X
SC-56      1    Select       CTRL+SHIFT+SPACEBAR             Selects the entire worksheet. If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing                         X
                                                             CTRL+SHIFT+SPACEBAR a second time selects the current region and its summary rows. Pressing CTRL+SHIFT+SPACEBAR a third time
                                                             selects the entire worksheet.
SC-57      1    Select       CTRL+SPACEBAR                   Selects an entire column in a worksheet.                                                                                                       X
SC-58      1    Select       SHIFT+ARROW KEY                 Extends the selection of cells by one cell.                                                                                                    X
SC-59      1    Select       SHIFT+SPACEBAR                  Selects an entire row in a worksheet.                                                                                                          X
SC-60      2    Data Entry   ALT+F1                          Creates an embedded chart of the data in the current range.                                                                                    X
SC-61      2    Data Entry   CTRL+D                          Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.                       X

SC-62      2    Data Entry   CTRL+R                          Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.              X

SC-63      2    Data Entry   F4                              Repeats the last command or action, if possible.
SC-64      2    Data Entry   SHIFT+F9                        Calculates the active worksheet.                                                                                                               X
SC-65      2    File         CTRL+P                          Displays the Print dialog box. (xl2010 - Displays the Print tab in Microsoft Office Backstage view.)                                           X
SC-66      2    File         CTRL+W                          Closes the selected workbook window.
SC-67      2    Format       CTRL+`                          Alternates between displaying cell values and displaying formulas in the worksheet.                                                            X
SC-68      2    Format       CTRL+5                          Applies or removes strikethrough.
SC-69      2    Format       CTRL+& (aka CTRL+SHIFT+7)       Applies the outline border to the selected cells.                                                                                              X
SC-70      2    Format       CTRL+_ (aka CTRL+SHIFT+-)       Removes the outline border from the selected cells.                                                                                            X
SC-71      2    Menu         ALT+F8                          Displays the Macro dialog box to create, run, edit, or delete a macro.                                                                         X
SC-72      2    Menu         CTRL+SHIFT+F                    Opens the Format Cells dialog box with the Font tab selected.
SC-73      2    Menu         CTRL+ALT+V                      Displays the Paste Special dialog box. Available only after you have cut or copied an object, text, or cell contents on a worksheet or in
                                                             another program.



                                                                                              Page 12 of 53
Excel Shortcuts                                                                                                                                                                                                      Level 1

Ref.     Level   Category     Shortcut Method (Alt. Method)   Explanation                                                                                                                                        Favorites
SC-74       2    Menu         CTRL+Minus (-)                  Displays the Delete dialog box to delete the selected cells.
SC-75       2    Menu         CTRL+SHIFT+P                    Opens the Format Cells dialog box with the Font tab selected.
SC-76       2    Menu         CTRL+SHIFT+Plus (+)             Displays the Insert dialog box to insert blank cells.
SC-77       2    Menu         CTRL+T                          Displays the Create Table dialog box.
SC-78       2    Menu         F3                              Displays the Paste Name dialog box. Available only if there are existing names in the workbook.
SC-79       2    Menu         SHIFT+F4                        Repeats the last Find action.
SC-80       2    Navigation   ALT+PAGE DOWN                   Moves one screen to the right in a worksheet.                                                                                                      X
SC-81       2    Navigation   ALT+PAGE UP                     Moves one screen to the left in a worksheet.                                                                                                       X
SC-82       2    Navigation   CTRL+ARROW KEY                  Moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or               X
                                                              datasheet borders.) in a worksheet.
SC-83       2    Select       CTRL+SHIFT+ARROW KEY            Extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the
                                                              selection to the next nonblank cell.
SC-84       2    Select       ALT+DOWN ARROW                  Opens a selected drop-down list.
SC-85       2    View         CTRL+0                          Hides the selected columns.
SC-86       2    View         CTRL+9                          Hides the selected rows.
SC-87       2    View         CTRL+SHIFT+(                    Unhides any hidden rows within the selection.
SC-88       2    View         CTRL+SHIFT+U                    Switches between expanding and collapsing of the formula bar.
SC-89       3    Data Entry   CTRL+ALT+F9                     Calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
SC-90       3    Data Entry   CTRL+ALT+SHIFT+F9               Rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be
                                                              calculated.
SC-91       3    Data Entry   CTRL+SHIFT+A                    Inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.
SC-92       3    Data Entry   CTRL+SHIFT+Z                    Uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.

SC-93       3    Data Entry   SHIFT+F2                        Adds or edits a cell comment.
SC-94       3    File         ALT+F11                         Opens the Microsoft Visual Basic For Applications Editor, in which you can create a macro by using Visual Basic for Applications (VBA).

SC-95       3    File         ALT+F4                          Closes Excel.
SC-96       3    File         CTRL+F4                         Closes the selected workbook window.
SC-97       3    Format       CTRL+SHIFT+!                    Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
SC-98       3    Format       CTRL+SHIFT+#                    Applies the Date format with the day, month, and year.
SC-99       3    Format       CTRL+SHIFT+$                    Applies the Currency format with two decimal places (negative numbers in parentheses).
SC-100      3    Format       CTRL+SHIFT+%                    Applies the Percentage format with no decimal places.
SC-101      3    Format       CTRL+SHIFT+@                    Applies the Time format with the hour and minute, and AM or PM.
SC-102      3    Format       CTRL+SHIFT+^                    Applies the Scientific number format with two decimal places.
SC-103      3    Format       CTRL+SHIFT+~                    Applies the General number format.
SC-104      3    Menu         ALT+SHIFT+F10                   Displays the menu or message for an Error Checking button.
SC-105      3    Menu         ALT+SPACEBAR                    Displays the Control menu for the Excel window.
SC-106      3    Menu         CTRL+F2                         Displays the print preview area on the Print tab in the Backstage view.
SC-107      3    Menu         CTRL+K                          Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.

SC-108      3    Menu         CTRL+L                          Displays the Create Table dialog box.
SC-109      3    Menu         F10                             Turns key tips on or off. (Pressing ALT does the same thing.)
SC-110      3    Navigation   CTRL+F6                         Switches to the next workbook window when more than one workbook window is open.
SC-111      3    Navigation   CTRL+SHIFT+TAB                  Switches to the previous tab in a dialog box.
SC-112      3    Navigation   CTRL+TAB                        Switches to the next tab in dialog box.
SC-113      3    Navigation   F6                              Switches between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This
                                                              Window, Freeze Panes, Split Window command), F6 includes the split panes when switching between panes and the ribbon area.

SC-114      3    Navigation   SHIFT+F6                        Switches between the worksheet, Zoom controls, task pane, and ribbon.



                                                                                               Page 13 of 53
Excel Shortcuts                                                                                                                                                                                     Level 1

Ref.   Level   Category   Shortcut Method (Alt. Method)   Explanation                                                                                                                            Favorites
SC-115    3    Select     CTRL+SHIFT+O                    Selects all cells that contain comments.
SC-116    3    Select     CTRL+SHIFT+PAGE DOWN            Selects the current and next sheet in a workbook.
SC-117    3    Select     CTRL+SHIFT+PAGE UP              Selects the current and previous sheet in a workbook.
SC-118    3    Select     F8                              Turns extend mode on or off. In extend mode, Extended Selection appears in the status line, and the arrow keys extend the selection.

SC-119    3    Select     SHIFT+F8                        Enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys.
SC-120    3    View       CTRL+6                          Alternates between hiding and displaying objects.
SC-121    3    View       CTRL+8                          Displays or hides the outline symbols.
SC-122    3    View       CTRL+F10                        Maximizes or restores the selected workbook window.
SC-123    3    View       CTRL+F5                         Restores the window size of the selected workbook window.
SC-124    3    View       CTRL+F7                         Performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when
                                                          finished press ENTER, or ESC to cancel.
SC-125    3    View       CTRL+F8                         Performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
SC-126    3    View       CTRL+F9                         Minimizes a workbook window to an icon.




                                                                                         Page 14 of 53
Back to Table of Contents

           Excel Function Examples by Category
           Date & Time Functions


           Text Functions


           Information Functions


           Lookup & Reference Functions


           Logical Functions & Formulas
Back to Table of Contents


 Order ID     OrderDate Month Region         Employee    Item      Units    Cost   Total
   10001        1/5/2011   Jan Midwest       Adams       Binder      94    19.99 1879.06
   10002        1/6/2011   Jan New England   Jones       Pencil      95     1.99  189.05
   10003       1/13/2011   Jan Midwest       Smith       Pencil      67     1.29   86.43
   10004       1/15/2011   Jan Midwest       Dwyer       Binder      46     8.99  413.54
   10005       1/22/2011   Jan Midwest       Andrews     Binder      28     4.99  139.72
   10006       1/23/2011   Jan Midwest       Stevenson   Binder      50    19.99   999.5
   10007       1/30/2011   Jan New England   Parent      Pen Set     16    15.99  255.84
   10008        2/1/2011  Feb Midwest        Smith       Binder      87       15    1305
   10009        2/7/2011  Feb New England    Jones       Pencil      95     1.99  189.05
   10010        2/9/2011  Feb Midwest        Adams       Pencil      36     4.99  179.64
   10011       2/16/2011  Feb Midwest        Dwyer       Binder      28     8.99  251.72
   10012       2/18/2011  Feb New England    Jones       Binder        4    4.99   19.96
   10013       2/24/2011  Feb Midwest        Stevenson   Binder      50    19.99   999.5
   10014       2/26/2011  Feb Midwest        Dwyer       Pen         27    19.99  539.73
   10015        3/5/2011  Mar Midwest        Smith       Pen         64     8.99  575.36
   10016        3/7/2011  Mar West Coast     Black       Binder        7   19.99  139.93
   10017       3/13/2011  Mar Midwest        Adams       Pencil      36     4.99  179.64
   10018       3/15/2011  Mar West Coast     Black       Pencil      56     2.99  167.44
   10019       3/22/2011  Mar New England    Jones       Pen         15    19.99  299.85
   10020       3/24/2011  Mar Midwest        Adams       Pen Set     50     4.99   249.5
   10021       3/30/2011  Mar Midwest        Dwyer       Pen         27    19.99  539.73
   10022        4/1/2011   Apr New England   Jones       Binder      60     4.99   299.4
   10023        4/8/2011   Apr West Coast    Black       Pen Set     96     4.99  479.04
   10024       4/10/2011   Apr Midwest       Andrews     Pencil      66     1.99  131.34
   10025       4/16/2011   Apr West Coast    Black       Pencil      56     2.99  167.44
   10026       4/18/2011   Apr Midwest       Andrews     Pencil      75     1.99  149.25
   10027       4/25/2011   Apr Midwest       Adams       Pencil      67     1.29   86.43
   10028       4/27/2011   Apr New England   Howard      Pen         96     4.99  479.04
   10029        5/3/2011  May New England    Jones       Binder      60     4.99   299.4
   10030        5/5/2011  May Midwest        Adams       Pencil      90     4.99   449.1
   10031       5/12/2011  May Midwest        Andrews     Pen Set     74    15.99 1183.26
   10032       5/14/2011  May Midwest        Dwyer       Pencil      53     1.29   68.37
   10033       5/20/2011  May Midwest        Andrews     Pencil      75     1.99  149.25
   10034       5/22/2011  May West Coast     Thompson    Pencil      32     1.99   63.68
   10035       5/29/2011  May New England    Howard      Binder      46     8.99  413.54
   10036       5/31/2011  May Midwest        Dwyer       Binder      80     8.99   719.2
   10037        6/6/2011   Jun Midwest       Adams       Pencil      90     4.99   449.1
   10038        6/8/2011   Jun New England   Jones       Binder      60     8.99   539.4
   10039       6/15/2011   Jun Midwest       Dwyer       Binder      87       15    1305
   10040       6/17/2011   Jun Midwest       Stevenson   Desk          5     125     625
   10041       6/23/2011   Jun West Coast    Thompson    Pencil      32     1.99   63.68
   10042       6/25/2011   Jun Midwest       Morgan      Pencil      90     4.99   449.1
   10043        7/2/2011    Jul Midwest      Dwyer       Binder        4    4.99   19.96
10044     7/4/2011    Jul   New England   Jones       Pen Set   62    4.99    309.38
10045    7/10/2011    Jul   New England   Jones       Binder    60    8.99     539.4
10046    7/12/2011    Jul   New England   Howard      Binder    29    1.99     57.71
10047    7/19/2011    Jul   Midwest       Stevenson   Binder     7   19.99    139.93
10048    7/21/2011    Jul   Midwest       Morgan      Pen Set   55   12.49    686.95
10049    7/27/2011    Jul   Midwest       Morgan      Pencil    90    4.99     449.1
10050    7/29/2011    Jul   New England   Parent      Binder    81   19.99   1619.19
10051     8/5/2011   Aug    New England   Jones       Pen Set   50    4.99     249.5
10052     8/7/2011   Aug    Midwest       Stevenson   Pen Set   42   23.95    1005.9
10053    8/13/2011   Aug    New England   Howard      Binder    29    1.99     57.71
10054    8/15/2011   Aug    New England   Jones       Pencil    35    4.99    174.65
10055    8/22/2011   Aug    Midwest       Morgan      Pencil    66    1.99    131.34
10056    8/24/2011   Aug    West Coast    Black       Desk       3     275       825
10057    8/30/2011   Aug    New England   Parent      Binder    81   19.99   1619.19
10058     9/1/2011   Sep    Midwest       Smith       Desk       2     125       250
10059     9/8/2011   Sep    Midwest       Stevenson   Pen       96    4.99    479.04
10060    9/10/2011   Sep    Midwest       Dwyer       Pencil     7    1.29      9.03
10061    9/16/2011   Sep    New England   Jones       Pencil    35    4.99    174.65
10062    9/18/2011   Sep    New England   Jones       Pen Set   16   15.99    255.84
10063    9/25/2011   Sep    West Coast    Black       Pencil    53    1.29     68.37
10064    9/27/2011   Sep    West Coast    Black       Pen       76    1.99    151.24
10065    10/3/2011   Oct    Midwest       Smith       Desk       2     125       250
10066    10/5/2011   Oct    Midwest       Morgan      Binder    28    8.99    251.72
10067   10/12/2011   Oct    Midwest       Dwyer       Binder    80    8.99     719.2
10068   10/14/2011   Oct    West Coast    Thompson    Binder    57   19.99   1139.43
10069   10/20/2011   Oct    New England   Jones       Pen Set   16   15.99    255.84
10070   10/22/2011   Oct    New England   Jones       Pen       64    8.99    575.36
10071   10/29/2011   Oct    West Coast    Black       Pen       76    1.99    151.24
10072   10/31/2011   Oct    Midwest       Andrews     Pencil    14    1.29     18.06
10073    11/6/2011   Nov    Midwest       Morgan      Binder    28    8.99    251.72
10074    11/8/2011   Nov    New England   Parent      Pen       15   19.99    299.85
10075   11/15/2011   Nov    West Coast    Thompson    Binder    57   19.99   1139.43
10076   11/17/2011   Nov    Midwest       Adams       Binder    11    4.99     54.89
10077   11/23/2011   Nov    New England   Jones       Pen       64    8.99    575.36
10078   11/25/2011   Nov    Midwest       Stevenson   Pen Set   96    4.99    479.04
10079    12/2/2011   Dec    Midwest       Andrews     Pencil    14    1.29     18.06
10080    12/4/2011   Dec    Midwest       Adams       Binder    94   19.99   1879.06
10081   12/10/2011   Dec    New England   Parent      Pen       15   19.99    299.85
10082   12/12/2011   Dec    Midwest       Smith       Pencil    67    1.29     86.43
10083   12/19/2011   Dec    Midwest       Adams       Binder    11    4.99     54.89
10084   12/21/2011   Dec    Midwest       Andrews     Binder    28    4.99    139.72
10085   12/27/2011   Dec    Midwest       Stevenson   Pen Set   96    4.99    479.04
10086   12/29/2011   Dec    New England   Parent      Pen Set   74   15.99   1183.26
Back to Table of Contents


           Region           (All)

           Sum of Units     Column Labels
           Row Labels       Jan                 Feb Mar Apr May Jun    Jul Aug Sep Oct Nov Dec
           Binder                           218 169    7 60 186 147    181 110      165 96 133
           Desk                                                    5          3   2   2
           Pen                                    27 106 96                     172 140 79 15
           Pen Set                           16       50 96 74         117 92 16 16 96 170
           Pencil                           162 131 92 264 250 212       90 101 95 14       81
           Grand Total                      396 327 255 516 510 364    388 306 285 337 271 399
Grand Total
       1472
         12
        635
        743
       1492
       4354
Back to Table of Contents


           Player #          (All)

                             Values
           Row Labels        Sum of # of Plays    Sum of High Score Sum of Low Score Sum of Variance
           Player 1 Name                      2                    96               86              10
           Player 2 Name                      2                    92               65              27
           Player 3 Name                      5                    94               79              15
           Player 4 Name                      6                    85               81               4
           Player 5 Name                      4                    89               80               9
           Player 6 Name                      3                    91               83               8
           Player 7 Name                      2                    93               85               8
           Player 8 Name                      9                    98               91               7
           Player 9 Name                      5                    89               67              22
           Player 10 Name                     8                    88               83               5
           Player 11 Name                     3                    86               75              11
           Player 12 Name                     4                    93               76              17
           Player 13 Name                     8                    93               80              13
           Player 14 Name                     6                    90               74              16
           Player 15 Name                     4                    85               70              15
           Grand Total                       71                 1362             1175             187




           Rank              (All)

                               Values
           Row Labels                            Sum
                               Count of X360 Metacriticof Userscore    Sum of Rank
           Assassin's Creed II                 2                  17.7               51
           BioShock                            1                   8.7                2
           Forza Motorsport 3                  1                   7.8               19
           Braid                               1                   8.7               12
           Call of Duty 4: Modern Warfare      1                   8.5                9
           Call of Duty: Modern Warfare 2      1                     6               11
           Fallout 3                           1                   8.4               15
           Batman: Arkham Asylum               1                   8.6               20
           Gears of War                        1                   8.5                7
           Gears of War 2                      1                   7.6               16
           Grand Theft Auto IV                 1                   7.9                1
           Guitar Hero II                      1                   8.1               17
           Halo 3                              1                   7.5               10
           Halo: Reach                         1                   7.5               23
           Mass Effect                         1                   8.8               24
           Mass Effect 2                       1                     9                4
Pac-Man Championship Edition DX    1     7.9     5
Red Dead Redemption                1     8.8     6
Rock Band 2                        1     8.3    21
Rock Band 3                        2      17    31
Street Fighter IV                  1     7.4    14
Super Street Fighter IV            1       8    22
The Elder Scrolls IV: Oblivion     1     8.8     8
The Orange Box                     1       9     3
Grand Total                       26   214.5   351
Back to Table of Contents


           Number           Day       Date         Formula
                    1       Sunday    12/13/2010        100
                    2       Monday    12/14/2010        104
                    3       Tuesday   12/15/2010        108
Month
January
February
March
Back to Table of Contents

           Referencing Tips - Excel Exposure
                             Test 1     Test 2     Test 3     Test 4     Test 5     Test 6   Average
           Student 1               70         54         92         33         61         99 68.17
           Student 2               93         69         69         35         81         37 64.00
           Student 3               95         66         62         36         71         41 61.83
           Student 4               73         75         82         89         93         41 75.50
           Student 5               49         57         52         58         75         61 58.67
           Student 6               71         74         38         59         45         36 53.83
           Student 7               98         34         74         84         97         35 70.33
           Student 8               36         38         86         93         93         30 62.67
           Student 9               53         97         48         44         91         76 68.17
           Student 10              95         83         38         32         58         99 67.50
           Student 11              50         60         50         60         50         60 55.00
           Total Score           783        707        691        623        815        615



      Number of Students         10
    Highest Possible Score      100
    Highest Total per Test     1000
        Percentage Boost       100%
              2     3     4     5     6     7
Student 1     70    54    92    33    61    99
Student 2     93    69    69    35    81    37
Student 3     95    66    62    36    71    41
Student 4     73    75    82    89    93    41
Student 5     49    57    52    58    75    61
Student 6     71    74    38    59    45    36
Student 7     98    34    74    84    97    35
Student 8     36    38    86    93    93    30
Student 9     53    97    48    44    91    76
Student 10    95    83    38    32    58    99
Student 11    50    60    50    60    50    60
             733   647   641   563   765   555
Back to Table of Contents
                      Example   Validation Criteria Types   Criteria 1      Criteria 2    Criteria 3
                                Any Value                                Default Validation
                     3          Whole Number                1               5
                     1.6        Decimal                     1.5
                                List                        Yes             No            Maybe
                                Date                        8/16/2012
                                Time                        5:00:00 AM      7:00:00 AM
                                Text Length                 5               6
                     12         Custom                      10              20         13
Screenshot:
Back to Table of Contents
                                                                               Wholesale %
Advanced Filtering - Example Data                                              COGS % of W/S

Product ID      Product Name            Category             Retail Price      Wholesale Price
HH-001          Can Opener              Household             $         5.00   $               4.00
HH-002          Scissors (3-pack)       Household             $        10.00   $               8.00
HH-003          Ice Cube Tray           Household             $        10.00   $               8.00
HH-004          Trash Can               Household             $        20.00   $             16.00
HH-005          Vacuum                  Household             $        40.00   $             32.00
HH-006          Mini-Fridge             Household             $        50.00   $             40.00
HH-007          Space Heater            Household             $        70.00   $             56.00
HH-008          Air Conditioner         Household             $      120.00    $             96.00
HH-009          Dining Room Table       Household             $      150.00    $            120.00
HH-010          Dresser / Bureau        Household             $      200.00    $            160.00
AU-001          Windshield Wiper        Automotive            $         5.00   $               4.00
AU-002          Replacement Headlight   Automotive            $        10.00   $               8.00
AU-003          Gas Cap                 Automotive            $        10.00   $               8.00
AU-004          Leather Seat Cover      Automotive            $        30.00   $             24.00
AU-005          Tires (Set of 4)        Automotive            $      300.00    $            240.00
OA-001          Wiffle Ball Set         Outdoor Activities    $         8.00   $               6.40
OA-002          Frisbee                 Outdoor Activities    $        10.00   $               8.00
OA-003          Horseshoe Set           Outdoor Activities    $        20.00   $             16.00
OA-004          Patio Table             Outdoor Activities    $        50.00   $             40.00
OA-005          Tent                    Outdoor Activities    $      100.00    $             80.00
     80%
     60%

COGS
 $   2.40
 $   4.80
 $   4.80
 $   9.60
 $ 19.20
 $ 24.00
 $ 33.60
 $ 57.60
 $ 72.00
 $ 96.00
 $   2.40
 $   4.80
 $   4.80
 $ 14.40
 $ 144.00
 $   3.84
 $   4.80
 $   9.60
 $ 24.00
 $ 48.00
Back to Table of Contents

            Pivot Table using Video Games and Metacritic Rankings

    Rank    Name                              X360 Metacritic Userscore
       1    Grand Theft Auto IV                     98              7.9
       2    BioShock                                96              8.7
       3    The Orange Box                          96              9.0
       4    Mass Effect 2                           96              9.0
       5    Pac-Man Championship Edition DX         95              7.9
       6    Red Dead Redemption                     95              8.8
       7    Gears of War                            94              8.5
       8    The Elder Scrolls IV: Oblivion          94              8.8
       9    Call of Duty 4: Modern Warfare          94              8.5
      10    Halo 3                                  94              7.5
      11    Call of Duty: Modern Warfare 2          94              6.0
      12    Braid                                   93              8.7
      13    Rock Band 3                             93              8.6
      14    Street Fighter IV                       93              7.4
      15    Fallout 3                               93              8.4
      16    Gears of War 2                          93              7.6
      17    Guitar Hero II                          92              8.1
      18    Rock Band 3                             92              8.4
      19    Forza Motorsport 3                      92              7.8
      20    Batman: Arkham Asylum                   92              8.6
      21    Rock Band 2                             92              8.3
      22    Super Street Fighter IV                 91              8.0
      23    Halo: Reach                             91              7.5
      24    Mass Effect                             91              8.8
      25    Assassin's Creed II                     90              8.9
      26    Assassin's Creed II                     89              8.8

Player #    Name                                High Score   Low Score    Variance
        1   Player 1 Name                           96          86               10
        2   Player 2 Name                           92          65               27
        3   Player 3 Name                           94          79               15
        4   Player 4 Name                           85          81                4
        5   Player 5 Name                           89          80                9
        6   Player 6 Name                           91          83                8
        7   Player 7 Name                           93          85                8
        8   Player 8 Name                           98          91                7
        9   Player 9 Name                           89          67               22
       10   Player 10 Name                          88          83                5
       11   Player 11 Name                          86          75               11
       12   Player 12 Name                          93          76               17
13 Player 13 Name   93   80   13
14 Player 14 Name   90   74   16
15 Player 15 Name   85   70   15
Name                   Grand Theft Auto IV   BioShock   The Orange Box   Mass Effect 2
X360 Metacritic        98                    96         96               96
Userscore              7.9                   8.7        9.0              9.0




      # of Plays
                   2
                   2
                   5
                   6
                   4
                   3
                   2
                   9
                   5
                   8
                   3
                   4
8
6
4
Pac-Man Championship Edition DX   Red Dead Redemption   Gears of War
95                                95                    94
7.9                               8.8                   8.5
The Elder Scrolls IV: Oblivion   Call of Duty 4: Modern Warfare   Halo 3
94                               94                               94
8.8                              8.5                              7.5
Call of Duty: Modern Warfare 2   Braid   Rock Band 3   Street Fighter IV   Fallout 3
94                               93      93            93                  93
6.0                              8.7     8.6           7.4                 8.4
Gears of War 2   Guitar Hero II   Rock Band 3   Forza Motorsport 3   Batman: Arkham Asylum
93               92               92            92                   92
7.6              8.1              8.4           7.8                  8.6
Rock Band 2   Super Street Fighter IV   Halo: Reach   Mass Effect   Assassin's Creed II
92            91                        91            91            90
8.3           8.0                       7.5           8.8           8.9
Shortcut Categories   Function Categories
Data Entry            Compatibility
File                  Cube
Format                Database
Menu                  Date & Time
Navigation            Engineering
Select                Financial
View                  Information
                      Logical
                      Lookup/Ref.
                      Math & Trig
                      RStatistical
                      Statistical
                      Text

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:44
posted:8/16/2012
language:English
pages:53