Date and time formulas
=NOW |
Show the date and time |
=TODAY() |
Show the current date without the time |
=DAY(TODAY)) |
Show today's date in a cell |
=MONTH(TODAY) |
Show current month in a cell |
=TODAY()+10 |
Add 10 days to current date |
Counting and rounding formulas
=SUM |
Calculates the sum of a group of values |
=AVERAGE |
Calculates the mean of a group of values |
=COUNT |
Counts the number of cells in a range that contains numbers |
=INT |
Removes the decimal portion of a number |
=ROUND |
Rounds a number to a specified number of decimal places |
=IF |
Tests for a true or false condition |
=NOW |
Returns the date and time |
=TODAY |
Returns the date, without the time |
=SUMIF |
Calculates a sum from a group of values in which a condition has been met |
=COUNTIF |
Counts the number of cells in a range that match a criteria |
=COUNTA(A1:A5) |
Count the number of non-blank cells in a range |
=ROUND(1.457,2) |
Rounds 1.45 to one decimal place |
=TRUE |
Returns the logical value TRUE |
=FALSE |
Returns the logical value FALSE |
=AND |
Returns TRUE if all of its arguments are TRUE |
=OR |
Returns TRUE if any argument is TRUE |
Finance
=INTRATE |
Calculates the interest rate for a fully invested security |
=EFFECT |
Calculates the effective annual interest rate |
=FV |
Calculates the future value of an investment |
=FVSCHEDULE |
Calculates the future value of an initial principal after applying a series of compound interest rates |
=IPMT |
Calculates the interest payment for an investment for a given period |
=ACCRINT |
Calculates the accrued interest for a security that pays periodic interest |
=ACCRINTM |
Calculates the accrued interest for a security that pays interest at maturity |
=AMORLINC |
Calculates the depreciation for each accounting period |
=NPV |
Calculates the net present value of cash flows based on a discount rate |
=YIELD |
Calculates the yield of a security based on maturity, face value, and interest rate |
=PRICE |
Calculates the price per $100 face value of a periodic coupon bond |
=PMT |
Calculates the total payment (debt and interest) on a debt security |
|
|
Unit conversion formulas
=CONVERT(A1,"day","hr") |
Converts value of A1 from days to hours |
=CONVERT(A1,"hr","mn") |
Converts value of A1 from hours to minutes |
=CONVERT(A1,"yr","day") |
Converts value of A1 from years to day |
=CONVERT(A1,"C","F") |
Converts value of A1 from Celsius to Fahrenheit |
=CONVERT(A1,"tsp","tbs") |
Converts value of A1 from teaspoons to tablespoons |
=CONVERT(A1,"gal","l") |
Converts value of A1 from gallons to litres |
=CONVERT(A1,"mi","km") |
Converts value of A1 from miles to kilometres |
=CONVERT(A1,"km","mi") |
Converts value of A1 from kilometres to miles |
=CONVERT(A1,"in","ft") |
Converts value of A1 from inches to feet |
=CONVERT(A1,"cm","in") |
Converts value of A1 from centimetres to inches |
=BIN2DEC(1100100) |
Converts binary 1100100 to decimal (100) |
=ROMAN |
Converts a number into a Roman numeral |
Text Formulas
=LEFT |
Extracts one or more characters from the left side of a text string |
=RIGHT |
Extracts one or more characters from the right side of a text string |
=MID |
Extracts characters from the middle of a text string |
=CONCATENATE |
Merges two or more text strings |
=REPLACE |
Replaces part of a text string |
=LOWER |
Converts a text string to all lowercase |
=UPPER |
Converts a text string to all uppercase |
=PROPER |
Converts a text string to proper case |
=LEN |
Returns a text string’s length in characters |
=REPT |
Repeats text a given number of times |
=TEXT |
Formats a number and converts it to text |
=VALUE |
Converts a text cell to a number |
=EXACT |
Checks to see if two text values are identical |
=DOLLAR |
Converts a number to text, using the USD currency format |
=CLEAN |
Removes all non-printable characters from text |
|
|
Mathematics
=B2-C9 |
Subtracts values in the two cells |
=D8*A3 |
Multiplies the numbers in the two cells |
=PRODUCT(A1:A19) |
Multiplies the cells in the range |
PRODUCT(F6:A1,2) |
Multiplies the cells in the range, and multiplies the result by 2 |
=A1/A3 |
Divides value in A1 by the value in A3 |
=MOD |
Returns the remainder from division |
=MIN(A1:A8) |
Calculates the smallest number in a range |
=MAX(C27:C34) |
Calculates the largest number in a range |
=SMALL(B1:B7,2) |
Calculates the second smallest number in a range |
=LARGE(G13:D7,3) |
Calculates the third largest number in a range |
=POWER(9,2) |
Calculates nine squared |
=9^3 |
Calculates nine cubed |
=FACT(A1) |
Factorial of value in A1 |
=EVEN |
Rounds a number up to the nearest even integer |
=ODD |
Rounds a number up to the nearest odd integer |
=AVERAGE |
Calculates the average |
=MEDIAN |
Calculates the median |
=SQRT |
Calculates the square root of a number |
=PI |
Shows the value of pi |
=POWER |
Calculates the result of a number raised to a power |
=RAND |
Returns a random number between 0 and 10 |
=RANDBETWEEN |
Returns a random number between the numbers you specify |
=COS |
Calculates the cosine of a number |
=SIN |
Calculates the sine of the given angle |
=TAN |
Calculates the tangent of a number |
=CORREL |
Calculates the correlation coefficient between two data sets |
=STDEVA |
Estimates standard deviation based on a sample |
=PROB |
Returns the probability that values in a range are between two limits |
|
Created By
Metadata
Comments
No comments yet. Add yours below!
Add a Comment
More Cheat Sheets by holly6901