Excel can be used to calculate and analyze numerical information; however, you will need to know how to write formulas to maximize Excel's capabilities. A formula is an equation that performs a calculation using values in the worksheet. In this lesson, you will learn how to create simple formulas using mathematical operators such as the addition, subtraction, multiplication, and division signs.
Download the example to work along with the video. Financial: Returns the periodic payment for an annuity. Compatibility: Returns the Poisson distribution. POWER function. Math and trigonometry: Returns the result of a number raised to a power.
PPMT function. Financial: Returns the payment on the principal for an investment for a given period. PRICE function. PROB function. Statistical: Returns the probability that values in a range are between two limits. Math and trigonometry: Multiplies its arguments. Text: Capitalizes the first letter in each word of a text value. PV function. Financial: Returns the present value of an investment. Compatibility: Returns the quartile of a data set.
Statistical: Returns the quartile of the data set, based on percentile values from Statistical: Returns the quartile of a data set. Math and trigonometry: Returns the integer portion of a division. Math and trigonometry: Converts degrees to radians. RAND function. Math and trigonometry: Returns a random number between 0 and 1. Math and trigonometry: Returns an array of random numbers between 0 and 1.
However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values. Math and trigonometry: Returns a random number between the numbers you specify. AVG function. Statistical: Returns the rank of a number in a list of numbers.
EQ function. RANK function. Compatibility: Returns the rank of a number in a list of numbers. RATE function. Financial: Returns the interest rate per period of an annuity. Financial: Returns the amount received at maturity for a fully invested security. ID function. Add-in and Automation: Returns the register ID of the specified dynamic link library DLL or code resource that has been previously registered. REPT function. Text: Returns the rightmost characters from a text value. ROMAN function.
Math and trigonometry: Converts an arabic numeral to roman, as text. ROUND function. Math and trigonometry: Rounds a number to a specified number of digits. Math and trigonometry: Rounds a number down, toward zero. Math and trigonometry: Rounds a number up, away from zero. ROW function. Lookup and reference: Returns the row number of a reference. ROWS function. Lookup and reference: Returns the number of rows in a reference.
RRI function. Financial: Returns an equivalent interest rate for the growth of an investment. RSQ function. Statistical: Returns the square of the Pearson product moment correlation coefficient. RTD function. Lookup and reference: Retrieves real-time data from a program that supports COM automation. Text: Finds one text value within another not case-sensitive. SEC function. Math and trigonometry: Returns the secant of an angle. SECH function. Math and trigonometry: Returns the hyperbolic secant of an angle.
Date and time: Converts a serial number to a second. Math and trigonometry: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4. Math and trigonometry: Returns the sum of a power series based on the formula. SHEET function. Information: Returns the sheet number of the referenced sheet. Information: Returns the number of sheets in a reference. SIGN function. Math and trigonometry: Returns the sign of a number. SIN function. Math and trigonometry: Returns the sine of the given angle.
SINH function. Math and trigonometry: Returns the hyperbolic sine of a number. SKEW function. Statistical: Returns the skewness of a distribution. Statistical: Returns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean.
SLN function. Financial: Returns the straight-line depreciation of an asset for one period. SLOPE function. Statistical: Returns the slope of the linear regression line. SMALL function. Statistical: Returns the k-th smallest value in a data set. SORT function. Lookup and reference: Sorts the contents of a range or array. Lookup and reference: Sorts the contents of a range or array based on the values in a corresponding range or array. SQRT function. Math and trigonometry: Returns a positive square root.
Financial: Retrieves historical data about a financial instrument. STDEV function. Compatibility: Estimates standard deviation based on a sample. Statistical: Calculates standard deviation based on the entire population.
Statistical: Estimates standard deviation based on a sample. Statistical: Estimates standard deviation based on a sample, including numbers, text, and logical values. Compatibility: Calculates standard deviation based on the entire population. Statistical: Calculates standard deviation based on the entire population, including numbers, text, and logical values. STEYX function. Statistical: Returns the standard error of the predicted y-value for each x in the regression.
Text: Substitutes new text for old text in a text string. Math and trigonometry: Returns a subtotal in a list or database. SUM function. SUMIF function. Math and trigonometry: Adds the cells specified by a given criteria. Math and trigonometry: Adds the cells in a range that meet multiple criteria. Math and trigonometry: Returns the sum of the products of corresponding array components.
SUMSQ function. Math and trigonometry: Returns the sum of the squares of the arguments. Math and trigonometry: Returns the sum of the difference of squares of corresponding values in two arrays. Math and trigonometry: Returns the sum of the sum of squares of corresponding values in two arrays.
Math and trigonometry: Returns the sum of squares of differences of corresponding values in two arrays. Logical: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. SYD function. Financial: Returns the sum-of-years' digits depreciation of an asset for a specified period. TAN function.
Math and trigonometry: Returns the tangent of a number. TANH function. Math and trigonometry: Returns the hyperbolic tangent of a number. Financial: Returns the bond-equivalent yield for a Treasury bill.
Financial: Returns the yield for a Treasury bill. Statistical: Returns the Percentage Points probability for the Student t-distribution.
Statistical: Returns the Student's t-distribution. TDIST function. Compatibility: Returns the Student's t-distribution.
TEXT function. Text: Formats a number and converts it to text. If the delimiter is an empty text string, this function will effectively concatenate the ranges. TIME function. Date and time: Returns the serial number of a particular time. Date and time: Converts a time in the form of text to a serial number. Statistical: Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom. Statistical: Returns the inverse of the Student's t-distribution.
TINV function. Compatibility: Returns the inverse of the Student's t-distribution. TODAY function. Date and time: Returns the serial number of today's date. Lookup and reference: Returns the transpose of an array. TREND function. Statistical: Returns values along a linear trend. TRIM function. Statistical: Returns the mean of the interior of a data set. TRUE function. TRUNC function. Math and trigonometry: Truncates a number to an integer.
Statistical: Returns the probability associated with a Student's t-test. TTEST function. Compatibility: Returns the probability associated with a Student's t-test. Information: Returns a number indicating the data type of a value. Text: Returns the Unicode character that is references by the given numeric value.
Text: Returns the number code point that corresponds to the first character of the text. Lookup and reference: Returns a list of unique values in a list or range. UPPER function. VALUE function. VAR function. Compatibility: Estimates variance based on a sample. Statistical: Calculates variance based on the entire population. Statistical: Estimates variance based on a sample. VARA function.
Statistical: Estimates variance based on a sample, including numbers, text, and logical values. VARP function. Compatibility: Calculates variance based on the entire population. VARPA function. Statistical: Calculates variance based on the entire population, including numbers, text, and logical values. VDB function. Financial: Returns the depreciation of an asset for a specified or partial period by using a declining balance method. Lookup and reference: Looks in the first column of an array and moves across the row to return the value of a cell.
Date and time: Converts a serial number to a day of the week. Date and time: Converts a serial number to a number representing where the week falls numerically with a year.
Compatibility: Calculates variance based on the entire population, including numbers, text, and logical values. Date and time: Returns the serial number of the date before or after a specified number of workdays. Date and time: 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.
XIRR function. Financial: Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. Lookup and reference: Searches a range or an array, and returns an item corresponding to the first match it finds.
Lookup and reference: Returns the relative position of an item in an array or range of cells. XNPV function. Financial: Returns the net present value for a schedule of cash flows that is not necessarily periodic. XOR function. Logical: Returns a logical exclusive OR of all arguments. YEAR function. Date and time: Converts a serial number to a year.
YIELD function. Financial: Returns the yield on a security that pays periodic interest. Because the portion to be rounded is 0.
Rounds the number to the nearest hundredth two decimal places. Because the portion to be rounded, 0. You can use the following formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters. Check if a column value or a part of it matches specific text. Remove spaces from the beginning and end of a column.
To remove spaces from a column, use the TRIM function. To repeat a character in a column, use the REPT function. The following is an alphabetical list of links to functions available to SharePoint users. It includes trigonometric, statistical, and financial functions, as well as conditional, date, math, and text formulas.
ABS function. ACOS function. ACOSH function. AND function. ASIN function. ASINH function. ATAN function. ATAN2 function. CHAR function. CODE function. COS function. COUNT function. DATE function. DAY function. DAYS function. DDB function. EVEN function.
EXACT function. FACT function. FDIST function. FIND function. FINV function. FIXED function. HOUR function. IF function. INT function. IPmt Function. IS functions. LEFT function. LEN function. LN function. Log Function. LOG10 function. Converts text to uppercase. Returns the character specified by the code number. Removes all nonprintable characters from text. Returns a numeric code for the first character in a text string.
Formats a number as text with a fixed number of decimals. Extracts the phonetic furigana characters from a text string. Repeats text a given number of times. Substitutes new text for old text in a text string. Converts its arguments to text. Converts a text argument to a number. Changes full-width double-byte English letters or katakana within a character string to half-width single-byte characters. Finds one text value within another case-sensitive.
Returns the leftmost characters from a text value. Returns the number of characters in a text string. Returns a specific number of characters from a text string starting at the position you specify. Replaces characters within text. Returns the rightmost characters from a text value. Finds one text value within another not case-sensitive. Converts a number from one measurement system to another. Tests whether two values are equal.
Returns the error function. Returns the complementary error function. Tests whether a number is greater than a threshold value. Returns the complementary ERF function integrated between x and infinity. Returns the modified Bessel function In x. Returns the Bessel function Jn x. Returns the modified Bessel function Kn x. Returns the Bessel function Yn x.
Converts a binary number to decimal. Converts a binary number to hexadecimal. Converts a decimal number to octal. Converts a hexadecimal number to binary. Converts a hexadecimal number to decimal. Converts a hexadecimal number to octal. Returns the absolute value modulus of a complex number. Returns the imaginary coefficient of a complex number. Returns the argument theta, an angle expressed in radians. Returns the complex conjugate of a complex number. Returns the cosine of a complex number.
Returns the quotient of two complex numbers. Returns the exponential of a complex number. Returns the natural logarithm of a complex number. Returns the base logarithm of a complex number. Returns the base-2 logarithm of a complex number. Returns a complex number raised to an integer power. Returns the product of complex numbers.
Returns the real coefficient of a complex number. Returns the sine of a complex number. Returns the square root of a complex number. Returns the difference between two complex numbers. Returns the sum of complex numbers. Converts an octal number to binary. Converts an octal number to decimal. Converts an octal number to hexadecimal. Returns the depreciation for each accounting period by using a depreciation coefficient.
Returns the depreciation for each accounting period. Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number. Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction. Returns the straight-line depreciation of an asset for one period. Returns the depreciation of an asset for a specified period by using the fixed-declining balance method.
Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify. Returns the effective annual interest rate. Returns the future value of an investment.
Returns the interest payment for an investment for a given period. Returns the internal rate of return for a series of cash flows. Returns the internal rate of return where positive and negative cash flows are financed at different rates. Returns the annual nominal interest rate. Returns the number of periods for an investment. Returns the net present value of an investment based on a series of periodic cash flows and a discount rate. Returns the present value of an investment. Returns the interest rate per period of an annuity.
Returns the yield on a security that pays periodic interest. Returns the accrued interest for a security that pays periodic interest. Returns the accrued interest for a security that pays interest at maturity. Returns the number of days from the beginning of the coupon period to the settlement date. Returns the number of days in the coupon period that contains the settlement date. Returns the number of days from the settlement date to the next coupon date.
Returns the next coupon date after the settlement date. Returns the number of coupons payable between the settlement date and maturity date. Returns the previous coupon date before the settlement date. Returns the cumulative interest paid between two periods.
Returns the cumulative principal paid on a loan between two periods. Returns the discount rate for a security. Returns the annual duration of a security with periodic interest payments. Returns the future value of an initial principal after applying a series of compound interest rates. Returns the interest rate for a fully invested security.
Calculates the interest paid during a specific period of an investment. Returns the yield of a security with an odd first period.
Returns the yield of a security with an odd last period. Returns the periodic payment for an annuity. Returns the payment on the principal for an investment for a given period.
0コメント