Category: advanced Excel Functions

Advanced Excel Functions Tutorial PDF Version Quick Guide Resources Job Search Discussion This Microsoft Excel tutorial teaches you how to create and use Excel functions. You can use Excel functions to perform various mathematical, statistical, logical calculations. This tutorial takes you step-by-step through the process. Audience This tutorial is intended for people who use Excel but are intimidated by the concept of formulas and functions. Prerequisites This tutorial assumes your familiarity with basic fomulas for calculations in Excel. Print Page Previous Next Advertisements ”;

Advanced Excel – Engineering Functions ”; Previous Next The Excel Engineering functions perform the most commonly used Engineering calculations, many of which relate to Bessel functions, complex numbers, or converting between different bases. Engineering Functions The following table lists all the Engineering functions − S.No. Function and Description 1 BESSELI Returns the modified Bessel function In(x) 2 BESSELJ Returns the Bessel function Jn(x) 3 BESSELK Returns the modified Bessel function Kn(x) 4 BESSELY Returns the Bessel function Yn(x) 5 BIN2DEC Converts a binary number to decimal 6 BIN2HEX Converts a binary number to hexadecimal 7 BIN2OCT Converts a binary number to octal 8 BITAND Returns a ”Bitwise And” of two numbers 9 BITLSHIFT Returns a value number shifted left by shift_amount bits 10 BITOR Returns a bitwise OR of 2 numbers 11 BITRSHIFT Returns a value number shifted right by shift_amount bits 12 BITXOR Returns a bitwise ”Exclusive Or” of two numbers 13 COMPLEX Converts real and imaginary coefficients into a complex number 14 CONVERT Converts a number from one measurement system to another 15 DEC2BIN Converts a decimal number to binary 16 DEC2HEX Converts a decimal number to hexadecimal 17 DEC2OCT Converts a decimal number to octal 18 DELTA Tests whether two values are equal 19 ERF Returns the error function 20 ERF.PRECISE Returns the error function 21 ERFC Returns the complementary error function 22 ERFC.PRECISE Returns the complementary error function 23 GESTEP Tests whether a number is greater than a threshold value 24 HEX2BIN Converts a hexadecimal number to binary 25 HEX2DEC Converts a hexadecimal number to decimal 26 HEX2OCT Converts a hexadecimal number to octal 27 IMABS Returns the absolute value (modulus) of a complex number 28 IMAGINARY Returns the imaginary coefficient of a complex number 29 IMARGUMENT Returns the argument theta, an angle expressed in radians 30 IMCONJUGATE Returns the complex conjugate of a complex number 31 IMCOS Returns the cosine of a complex number 32 IMCOSH Returns the hyperbolic cosine of a complex number 33 IMCOT Returns the cotangent of a complex number 34 IMCSC Returns the cosecant of a complex number 35 IMCSCH Returns the hyperbolic cosecant of a complex number 36 IMDIV Returns the quotient of two complex numbers 37 IMEXP Returns the exponential of a complex number 38 IMLN Returns the natural logarithm of a complex number 39 IMLOG2 Returns the base-2 logarithm of a complex number 40 IMLOG10 Returns the base-10 logarithm of a complex number 41 IMPOWER Returns a complex number raised to an integer power 42 IMPRODUCT Returns the product of complex numbers 43 IMREAL Returns the real coefficient of a complex number 44 IMSEC Returns the secant of a complex number 45 IMSECH Returns the hyperbolic secant of a complex number 46 IMSIN Returns the sine of a complex number 47 IMSINH Returns the hyperbolic sine of a complex number 48 IMSQRT Returns the square root of a complex number 49 IMSUB Returns the difference of two complex numbers 50 IMSUM Returns the sum of complex numbers 51 IMTAN Returns the tangent of a complex number 52 OCT2BIN Converts an octal number to binary 53 OCT2DEC Converts an octal number to decimal 54 OCT2HEX Converts an octal number to hexadecimal Print Page Previous Next Advertisements ”;

Advanced Excel – Compatibility Functions ”; Previous Next In Excel 2010 or later, the functions listed in this category were replaced with new functions that provide improved accuracy and have names that reflect their usage better. The new functions can be found in Statistical functions and Math and trigonometry functions. If backward compatibility is not required, you should start using the new functions. You can still use these earlier versions of functions for compatibility with earlier versions of Excel. If you are using Excel 2007, you will find these functions in the Statistical or Math & Trig categories on the Formulas tab. Compatibility Functions The following table lists all the Compatibility functions − S.No. Function and Description 1 BETADIST Returns the cumulative beta probability density function 2 BETAINV Returns the inverse of the cumulative beta probability density function 3 BINOMDIST Returns the individual term binomial distribution probability 4 CEILING Rounds a number to the nearest integer or to the nearest multiple of significance 5 CHIDIST Returns the one-tailed probability of the chi-squared distribution 6 CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution 7 CHITEST Returns the test for independence 8 CONFIDENCE Returns the confidence interval for a population mean 9 COVAR Returns covariance, the average of the products of paired deviations 10 CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value 11 EXPONDIST Returns the exponential distribution 12 FDIST Returns the F probability distribution 13 FINV Returns the inverse of the F probability distribution 14 FLOOR Rounds a number down, toward 0 15 FTEST Returns the result of an F-Test 16 GAMMADIST Returns the gamma distribution 17 GAMMAINV Returns the inverse of the gamma cumulative distribution 18 HYPGEOMDIST Returns the hypergeometric distribution 19 LOGINV Returns the inverse of the lognormal distribution 20 LOGNORMDIST Returns the cumulative lognormal distribution 21 MODE Returns the most common value in a data set 22 NEGBINOMDIST Returns the negative binomial distribution 23 NORMDIST Returns the normal cumulative distribution 24 NORMINV Returns the inverse of the normal cumulative distribution 25 NORMSDIST Returns the standard normal cumulative distribution 26 NORMSINV Returns the inverse of the standard normal cumulative distribution 27 PERCENTILE Returns the kth percentile of values in a range 28 PERCENTRANK Returns the percentage rank of a value in a data set 29 POISSON Returns the Poisson distribution 30 QUARTILE Returns the quartile of a data set 31 RANK Returns the rank of a number in a list of numbers 32 STDEV Estimates standard deviation based on a sample, ignoring text and logical values 33 STDEVP Calculates standard deviation based on the entire population, ignoring text and logical values 34 TDIST Returns the student’s t-distribution 35 TINV Returns the inverse of the student’s t-distribution 36 TTEST Returns the probability associated with a student’s t-Test 37 VAR Estimates variance based on a sample, ignoring logical values and text 38 VARP Calculates variance based on the entire population, ignoring logical values and text 39 WEIBULL Returns the Weibull distribution 40 ZTEST Returns the two-tailed P-value of a z-test Print Page Previous Next Advertisements ”;

Advanced Excel – Date & Time Functions ”; Previous Next Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times. Some of the Excel Date & Time functions are new to Excel 2010 or Excel 2013, so are not available in earlier versions of Excel. You can find this information in the applicability section of the function. Date and Time Functions The following table lists all the Date & Time functions − S.No. Function and Description 1 DATE Returns the serial number of a particular date. 2 DATEDIF Calculates the number of days, months, or years between two dates. 3 DATEVALUE Converts a date in the form of text to a serial number. 4 DAY Converts a serial number to a day of the month. 5 DAYS Returns the number of days between two dates. 6 DAYS360 Calculates the number of days between two dates, based on a 360-day year. 7 EDATE Returns the serial number of the date that is the indicated number of months before or after the start date. 8 EOMONTH Returns the serial number of the last day of the month before or after a specified number of months. 9 HOUR Converts a serial number to an hour. 10 ISOWEEKNUM Returns the number of the ISO week number of the year for a given date. 11 MINUTE Converts a serial number to a minute. 12 MONTH Converts a serial number to a month. 13 NETWORKDAYS Returns the number of whole workdays between two dates. 14 NETWORKDAYS.INTL Returns the number of whole workdays between two dates (international version). 15 NOW Returns the serial number of the current date and time. 16 SECOND Converts a serial number to a second. 17 TIME Returns the serial number of a particular time. 18 TIMEVALUE Converts a time in the form of text to a serial number. 19 TODAY Returns the serial number of today’s date. 20 WEEKDAY Converts a serial number to a day of the week. 21 WEEKNUM Returns the week number in the year. 22 WORKDAY Returns the serial number of the date before or after a specified number of workdays. 23 WORKDAY.INTL Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days. 24 YEAR Converts a serial number to a year. 25 YEARFRAC Returns the year fraction representing the number of whole days between start_date and end_date. Print Page Previous Next Advertisements ”;

Discuss Advanced Excel Functions ”; Previous Next This Microsoft Excel tutorial teaches you how to create and use Excel functions. You can use Excel functions to perform various mathematical, statistical, logical calculations. This tutorial takes you step-by-step through the process. Print Page Previous Next Advertisements ”;