Hippo File

Looking to download safe free versions of the latest software, freeware, shareware and demo programs from a reputable download site and much more

Blog

The Essential Microsoft Excel Formulas and Functions Cheat Sheet

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.45, 1) Rounds 1.45 to one decimal place =ROUND(-1.457, 2) Rounds -1.457 to two decimal places =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 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 days =CONVERT(A1,”C”,”F”) Converts value of A1 from Celsius to Fahrenheit =CONVERT(A1,”tsp”,”tbs”) Converts value of A1 from teaspoons to tablespoons !ERROR! A1 does not contain a number or expression Converts value of A1 from gallons to liters =CONVERT(A1,”mi”,”km”) Converts value of A1 from miles to kilometers =CONVERT(A1,”km”,”mi”) Converts value of A1 from kilometers to miles =CONVERT(A1,”in”,”ft”) Converts value of A1 from inches to feet =CONVERT(A1,”cm”,”in”) Converts value of A1 from centimeters to inches =BIN2DEC(1100100) Converts binary 1100100 to decimal (100) =ROMAN Converts a number into a Roman numeral 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 mulitplies 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 1 =RANDBETWEEN Returns a random number between the numbers you specify =COS Calculates the cosine of a number =SIN Returns the sine of the given angle 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 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 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 =PMT Calculates the total payment (debt and interest) on a debt security =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



Source link

LEAVE A RESPONSE

Your email address will not be published. Required fields are marked *