Announcement

Collapse
No announcement yet.

List All Excel Formulas

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • List All Excel Formulas

    I need some help on using formulae in Excel 2002 (XP).

    Where can I find some Tutorial/How to guide?

    My exam is on March 07. Please help soon.
    Muhammad Rehan Siddiqui
    Karachi, Pakistan.

  • #2
    Hi Aefroze and welcome to the board.

    Why not start with the directhelp in XL and then ask specific questions here on the board?

    See Also Excel Formulas and ALL Excel Formulas

    There exist a very good file that is available for download:
    http://homepage.ntlworld.com/noneley/
    Last edited by Dave Hawley; January 8th, 2007, 14:13.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

    Comment


    • #3
      Thank you very much. It is so very nice of you.
      Muhammad Rehan Siddiqui
      Karachi, Pakistan.

      Comment


      • #4
        Just to add if you are working and have no internet access like Jack at work, the built in help files, I find hand from time to time,

        Check in help, and type in formula and from the options click worksheet functions, your get a list like:

        Worksheet functions listed by category
        Database
        Microsoft Excel includes worksheet functions that analyze data stored in lists or databases. Each of these functions, referred to collectively as the Dfunctions, uses three arguments: database, field, and criteria. These arguments refer to the worksheet ranges that are used by the function.
        DAVERAGE Returns the average of selected database entries
        DCOUNT Counts the cells that contain numbers in a database
        DCOUNTA Counts nonblank cells in a database
        DGET Extracts from a database a single record that matches the specified criteria
        DMAX Returns the maximum value from selected database entries
        DMIN Returns the minimum value from selected database entries
        DPRODUCT Multiplies the values in a particular field of records that match the criteria in a database
        DSTDEV Estimates the standard deviation based on a sample of selected database entries
        DSTDEVP Calculates the standard deviation based on the entire population of selected database entries
        DSUM Adds the numbers in the field column of records in the database that match the criteria
        DVAR Estimates variance based on a sample from selected database entries
        DVARP Calculates variance based on the entire population of selected database entries
        GETPIVOTDATA Returns data stored in a PivotTable
        Date and Time
        DATE Returns the serial number of a particular date
        DATEVALUE Converts a date in the form of text to a serial number
        DAY Converts a serial number to a day of the month
        DAYS360 Calculates the number of days between two dates based on a 360-day year
        EDATE Returns the serial number of the date that is the indicated number of months before or after the start date
        EOMONTH Returns the serial number of the last day of the month before or after a specified number of months
        HOUR Converts a serial number to an hour
        MINUTE Converts a serial number to a minute
        MONTH Converts a serial number to a month
        NETWORKDAYS Returns the number of whole workdays between two dates
        NOW Returns the serial number of the current date and time
        SECOND Converts a serial number to a second
        TIME Returns the serial number of a particular time
        TIMEVALUE Converts a time in the form of text to a serial number
        TODAY Returns the serial number of today's date
        WEEKDAY Converts a serial number to a day of the week
        WEEKNUM Converts a serial number to a number representing where the week falls numerically with a year
        WORKDAY Returns the serial number of the date before or after a specified number of workdays
        YEAR Converts a serial number to a year
        YEARFRAC Returns the year fraction representing the number of whole days between start_date and end_date
        External
        These functions are loaded with add-in programs
        EUROCONVERT Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation)
        SQL.REQUEST Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming
        Engineering
        BESSELI Returns the modified Bessel function In(x)
        BESSELJ Returns the Bessel function Jn(x)
        BESSELK Returns the modified Bessel function Kn(x)
        BESSELY Returns the Bessel function Yn(x)
        BIN2DEC Converts a binary number to decimal
        BIN2HEX Converts a binary number to hexadecimal
        BIN2OCT Converts a binary number to octal
        COMPLEX Converts real and imaginary coefficients into a complex number
        CONVERT Converts a number from one measurement system to another
        DEC2BIN Converts a decimal number to binary
        DEC2HEX Converts a decimal number to hexadecimal
        DEC2OCT Converts a decimal number to octal
        DELTA Tests whether two values are equal
        ERF Returns the error function
        ERFC Returns the complementary error function
        GESTEP Tests whether a number is greater than a threshold value
        HEX2BIN Converts a hexadecimal number to binary
        HEX2DEC Converts a hexadecimal number to decimal
        HEX2OCT Converts a hexadecimal number to octal
        IMABS Returns the absolute value (modulus) of a complex number
        IMAGINARY Returns the imaginary coefficient of a complex number
        IMARGUMENT Returns the argument theta, an angle expressed in radians
        IMCONJUGATE Returns the complex conjugate of a complex number
        IMCOS Returns the cosine of a complex number
        IMDIV Returns the quotient of two complex numbers
        IMEXP Returns the exponential of a complex number
        IMLN Returns the natural logarithm of a complex number
        IMLOG10 Returns the base-10 logarithm of a complex number
        IMLOG2 Returns the base-2 logarithm of a complex number
        IMPOWER Returns a complex number raised to an integer power
        IMPRODUCT Returns the product of two complex numbers
        IMREAL Returns the real coefficient of a complex number
        IMSIN Returns the sine of a complex number
        IMSQRT Returns the square root of a complex number
        IMSUB Returns the difference between two complex numbers
        IMSUM Returns the sum of complex numbers
        OCT2BIN Converts an octal number to binary
        OCT2DEC Converts an octal number to decimal
        OCT2HEX Converts an octal number to hexadecimal
        Financial
        ACCRINT Returns the accrued interest for a security that pays periodic interest
        ACCRINTM Returns the accrued interest for a security that pays interest at maturity
        AMORDEGRC Returns the depreciation for each accounting period by using a depreciation coefficient
        AMORLINC Returns the depreciation for each accounting period
        COUPDAYBS Returns the number of days from the beginning of the coupon period to the settlement date
        COUPDAYS Returns the number of days in the coupon period that contains the settlement date
        COUPDAYSNC Returns the number of days from the settlement date to the next coupon date
        COUPNCD Returns the next coupon date after the settlement date
        COUPNUM Returns the number of coupons payable between the settlement date and maturity date
        COUPPCD Returns the previous coupon date before the settlement date
        CUMIPMT Returns the cumulative interest paid between two periods
        CUMPRINC Returns the cumulative principal paid on a loan between two periods
        DB Returns the depreciation of an asset for a specified period using the fixed-declining balance method
        DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify
        DISC Returns the discount rate for a security
        DOLLARDE Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
        DOLLARFR Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
        DURATION Returns the annual duration of a security with periodic interest payments
        EFFECT Returns the effective annual interest rate
        FV Returns the future value of an investment
        FVSCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates
        INTRATE Returns the interest rate for a fully invested security
        IPMT Returns the interest payment for an investment for a given period
        IRR Returns the internal rate of return for a series of cash flows
        ISPMT Calculates the interest paid during a specific period of an investment
        MDURATION Returns the Macauley modified duration for a security with an assumed par value of $100
        MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates
        NOMINAL Returns the annual nominal interest rate
        NPER Returns the number of periods for an investment
        NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
        ODDFPRICE Returns the price per $100 face value of a security with an odd first period
        ODDFYIELD Returns the yield of a security with an odd first period
        ODDLPRICE Returns the price per $100 face value of a security with an odd last period
        ODDLYIELD Returns the yield of a security with an odd last period
        PMT Returns the periodic payment for an annuity
        PPMT Returns the payment on the principal for an investment for a given period
        PRICE Returns the price per $100 face value of a security that pays periodic interest
        PRICEDISC Returns the price per $100 face value of a discounted security
        PRICEMAT Returns the price per $100 face value of a security that pays interest at maturity
        PV Returns the present value of an investment
        RATE Returns the interest rate per period of an annuity
        RECEIVED Returns the amount received at maturity for a fully invested security
        SLN Returns the straight-line depreciation of an asset for one period
        SYD Returns the sum-of-years' digits depreciation of an asset for a specified period
        TBILLEQ Returns the bond-equivalent yield for a Treasury bill
        TBILLPRICE Returns the price per $100 face value for a Treasury bill
        TBILLYIELD Returns the yield for a Treasury bill
        VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method
        XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic
        XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic
        YIELD Returns the yield on a security that pays periodic interest
        YIELDDISC Returns the annual yield for a discounted security; for example, a Treasury bill
        YIELDMAT Returns the annual yield of a security that pays interest at maturity
        Information
        CELL Returns information about the formatting, location, or contents of a cell
        COUNTBLANK Counts the number of blank cells within a range
        ERROR.TYPE Returns a number corresponding to an error type
        INFO Returns information about the current operating environment
        ISBLANK Returns TRUE if the value is blank
        ISERR Returns TRUE if the value is any error value except #N/A
        ISERROR Returns TRUE if the value is any error value
        ISEVEN Returns TRUE if the number is even
        ISLOGICAL Returns TRUE if the value is a logical value
        ISNA Returns TRUE if the value is the #N/A error value
        ISNONTEXT Returns TRUE if the value is not text
        ISNUMBER Returns TRUE if the value is a number
        ISODD Returns TRUE if the number is odd
        ISREF Returns TRUE if the value is a reference
        ISTEXT Returns TRUE if the value is text
        N Returns a value converted to a number
        NA Returns the error value #N/A
        TYPE Returns a number indicating the data type of a value
        Logical
        AND Returns TRUE if all its arguments are TRUE
        FALSE Returns the logical value FALSE
        IF Specifies a logical test to perform
        NOT Reverses the logic of its argument
        OR Returns TRUE if any argument is TRUE
        TRUE Returns the logical value TRUE
        Lookup and Reference
        ADDRESS Returns a reference as text to a single cell in a worksheet
        AREAS Returns the number of areas in a reference
        CHOOSE Chooses a value from a list of values
        COLUMN Returns the column number of a reference
        COLUMNS Returns the number of columns in a reference
        HLOOKUP Looks in the top row of an array and returns the value of the indicated cell
        HYPERLINK Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
        INDEX Uses an index to choose a value from a reference or array
        INDIRECT Returns a reference indicated by a text value
        LOOKUP Looks up values in a vector or array
        MATCH Looks up values in a reference or array
        OFFSET Returns a reference offset from a given reference
        ROW Returns the row number of a reference
        ROWS Returns the number of rows in a reference
        RTD Retrieves real-time data from a program that supports COM automation
        TRANSPOSE Returns the transpose of an array
        VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
        Math and Trigonometry
        ABS Returns the absolute value of a number
        ACOS Returns the arccosine of a number
        ACOSH Returns the inverse hyperbolic cosine of a number
        ASIN Returns the arcsine of a number
        ASINH Returns the inverse hyperbolic sine of a number
        ATAN Returns the arctangent of a number
        ATAN2 Returns the arctangent from x- and y-coordinates
        ATANH Returns the inverse hyperbolic tangent of a number
        CEILING Rounds a number to the nearest integer or to the nearest multiple of significance
        COMBIN Returns the number of combinations for a given number of objects
        COS Returns the cosine of a number
        COSH Returns the hyperbolic cosine of a number
        COUNTIF Counts the number of nonblank cells within a range that meet the given criteria
        DEGREES Converts radians to degrees
        EVEN Rounds a number up to the nearest even integer
        EXP Returns e raised to the power of a given number
        FACT Returns the factorial of a number
        FACTDOUBLE Returns the double factorial of a number
        FLOOR Rounds a number down, toward zero
        GCD Returns the greatest common divisor
        INT Rounds a number down to the nearest integer
        LCM Returns the least common multiple
        LN Returns the natural logarithm of a number
        LOG Returns the logarithm of a number to a specified base
        LOG10 Returns the base-10 logarithm of a number
        MDETERM Returns the matrix determinant of an array
        MINVERSE Returns the matrix inverse of an array
        MMULT Returns the matrix product of two arrays
        MOD Returns the remainder from division
        MROUND Returns a number rounded to the desired multiple
        MULTINOMIAL Returns the multinomial of a set of numbers
        ODD Rounds a number up to the nearest odd integer
        PI Returns the value of pi
        POWER Returns the result of a number raised to a power
        PRODUCT Multiplies its arguments
        QUOTIENT Returns the integer portion of a division
        RADIANS Converts degrees to radians
        RAND Returns a random number between 0 and 1
        RANDBETWEEN Returns a random number between the numbers you specify
        ROMAN Converts an arabic numeral to roman, as text
        ROUND Rounds a number to a specified number of digits
        ROUNDDOWN Rounds a number down, toward zero
        ROUNDUP Rounds a number up, away from zero
        SERIESSUM Returns the sum of a power series based on the formula
        SIGN Returns the sign of a number
        SIN Returns the sine of the given angle
        SINH Returns the hyperbolic sine of a number
        SQRT Returns a positive square root
        SQRTPI Returns the square root of (number * pi)
        SUBTOTAL Returns a subtotal in a list or database
        SUM Adds its arguments
        SUMIF Adds the cells specified by a given criteria
        SUMPRODUCT Returns the sum of the products of corresponding array components
        SUMSQ Returns the sum of the squares of the arguments
        SUMX2MY2 Returns the sum of the difference of squares of corresponding values in two arrays
        SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two arrays
        SUMXMY2 Returns the sum of squares of differences of corresponding values in two arrays
        TAN Returns the tangent of a number
        TANH Returns the hyperbolic tangent of a number
        TRUNC Truncates a number to an integer
        Statistical
        AVEDEV Returns the average of the absolute deviations of data points from their mean
        AVERAGE Returns the average of its arguments
        AVERAGEA Returns the average of its arguments, including numbers, text, and logical values
        BETADIST Returns the cumulative beta probability density function
        BETAINV Returns the inverse of the cumulative beta probability density function
        BINOMDIST Returns the individual term binomial distribution probability
        CHIDIST Returns the one-tailed probability of the chi-squared distribution
        CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution
        CHITEST Returns the test for independence
        CONFIDENCE Returns the confidence interval for a population mean
        CORREL Returns the correlation coefficient between two data sets
        COUNT Counts how many numbers are in the list of arguments
        COUNTA Counts how many values are in the list of arguments
        COVAR Returns covariance, the average of the products of paired deviations
        CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
        DEVSQ Returns the sum of squares of deviations
        EXPONDIST Returns the exponential distribution
        FDIST Returns the F probability distribution
        FINV Returns the inverse of the F probability distribution
        FISHER Returns the Fisher transformation
        FISHERINV Returns the inverse of the Fisher transformation
        FORECAST Returns a value along a linear trend
        FREQUENCY Returns a frequency distribution as a vertical array
        FTEST Returns the result of an F-test
        GAMMADIST Returns the gamma distribution
        GAMMAINV Returns the inverse of the gamma cumulative distribution
        GAMMALN Returns the natural logarithm of the gamma function, Γ(x)
        GEOMEAN Returns the geometric mean
        GROWTH Returns values along an exponential trend
        HARMEAN Returns the harmonic mean
        HYPGEOMDIST Returns the hypergeometric distribution
        INTERCEPT Returns the intercept of the linear regression line
        KURT Returns the kurtosis of a data set
        LARGE Returns the k-th largest value in a data set
        LINEST Returns the parameters of a linear trend
        LOGEST Returns the parameters of an exponential trend
        LOGINV Returns the inverse of the lognormal distribution
        LOGNORMDIST Returns the cumulative lognormal distribution
        MAX Returns the maximum value in a list of arguments
        MAXA Returns the maximum value in a list of arguments, including numbers, text, and logical values
        MEDIAN Returns the median of the given numbers
        MIN Returns the minimum value in a list of arguments
        MINA Returns the smallest value in a list of arguments, including numbers, text, and logical values
        MODE Returns the most common value in a data set
        NEGBINOMDIST Returns the negative binomial distribution
        NORMDIST Returns the normal cumulative distribution
        NORMINV Returns the inverse of the normal cumulative distribution
        NORMSDIST Returns the standard normal cumulative distribution
        NORMSINV Returns the inverse of the standard normal cumulative distribution
        PEARSON Returns the Pearson product moment correlation coefficient
        PERCENTILE Returns the k-th percentile of values in a range
        PERCENTRANK Returns the percentage rank of a value in a data set
        PERMUT Returns the number of permutations for a given number of objects
        POISSON Returns the Poisson distribution
        PROB Returns the probability that values in a range are between two limits
        QUARTILE Returns the quartile of a data set
        RANK Returns the rank of a number in a list of numbers
        RSQ Returns the square of the Pearson product moment correlation coefficient
        SKEW Returns the skewness of a distribution
        SLOPE Returns the slope of the linear regression line
        SMALL Returns the k-th smallest value in a data set
        STANDARDIZE Returns a normalized value
        STDEV Estimates standard deviation based on a sample
        STDEVA Estimates standard deviation based on a sample, including numbers, text, and logical values
        STDEVP Calculates standard deviation based on the entire population
        STDEVPA Calculates standard deviation based on the entire population, including numbers, text, and logical values
        STEYX Returns the standard error of the predicted y-value for each x in the regression
        TDIST Returns the Student's t-distribution
        TINV Returns the inverse of the Student's t-distribution
        TREND Returns values along a linear trend
        TRIMMEAN Returns the mean of the interior of a data set
        TTEST Returns the probability associated with a Student's t-test
        VAR Estimates variance based on a sample
        VARA Estimates variance based on a sample, including numbers, text, and logical values
        VARP Calculates variance based on the entire population
        VARPA Calculates variance based on the entire population, including numbers, text, and logical values
        WEIBULL Returns the Weibull distribution
        ZTEST Returns the two-tailed P-value of a z-test
        Text and Data


        This should cover most issues,

        Hope that helps as an alternative.

        Jack in the UK

        Comment


        • #5
          Way to go Jack

          Who sais there's a Character limit :wink1:

          There are a few very handy downloads here: Free Downloads including the one XlDennis posted a link to. Another that is similiar is Function List Workbook and Document by Norman Harker.
          Last edited by Dave Hawley; January 8th, 2007, 14:11.

          Comment

          Working...
          X