370+ Spreadsheet Formulas

SUMCalculates the sum of a group of numbers SUMPRODUCTReturns the sum of products of corresponding arrays SUMSQReturns the sum of squares of arguments SUMX2MY2Returns sum of difference of squares SUMX2PY2Returns sum of sum of squares SUMXMY2Returns sum of squares of differences PRODUCTMultiplies all the numbers given as arguments QUOTIENTReturns the integer portion of a division ABSReturns the absolute value of a number SQRTReturns the positive square root of a number POWERReturns the result of a number raised to a power MODReturns the remainder from division ROUNDRounds a number to a specified number of digits ROUNDUPRounds a number up, away from zero ROUNDDOWNRounds a number down, toward zero FLOORRounds a number down to nearest multiple FLOOR.MATHRounds down to nearest integer or multiple CEILINGRounds a number up to nearest multiple CEILING.MATHRounds up to nearest integer or multiple TRUNCTruncates a number to an integer INTRounds a number down to the nearest integer MROUNDRounds to nearest multiple EVENRounds a number up to nearest even integer ODDRounds a number up to nearest odd integer SIGNReturns the sign of a number FACTReturns the factorial of a number GCDReturns the greatest common divisor LCMReturns the least common multiple COMBINReturns the number of combinations COMBINAReturns combinations with repetitions PERMUTReturns the number of permutations PERMUTAReturns permutations with repetitions MULTINOMIALReturns the multinomial of a set PIReturns the value of pi EXPReturns e raised to a power LNReturns the natural logarithm LOGReturns the logarithm of a number to a base LOG10Returns the base-10 logarithm RANDReturns a random number between 0 and 1 RANDBETWEENReturns a random integer between two values SERIESSUMReturns sum of power series SUBTOTALReturns a subtotal in a list AGGREGATEReturns aggregate in a list ignoring errors SINReturns the sine of an angle COSReturns the cosine of an angle TANReturns the tangent of an angle COTReturns the cotangent of an angle SECReturns the secant of an angle CSCReturns the cosecant of an angle ASINReturns the arcsine of a number ACOSReturns the arccosine of a number ATANReturns the arctangent of a number ATAN2Returns arctangent from x and y coordinates ACOTReturns the arccotangent of a number SINHReturns the hyperbolic sine COSHReturns the hyperbolic cosine TANHReturns the hyperbolic tangent COTHReturns the hyperbolic cotangent SECHReturns the hyperbolic secant CSCHReturns the hyperbolic cosecant ACOTHReturns the inverse hyperbolic cotangent DEGREESConverts radians to degrees RADIANSConverts degrees to radians SUMIFAdds cells specified by a given condition SUMIFSAdds cells that meet multiple criteria

AVERAGECalculates the arithmetic mean AVGAlias for AVERAGE MINReturns the minimum value MAXReturns the maximum value COUNTCounts how many numbers are in the list COUNTACounts non-empty cells COUNTBLANKCounts empty cells in a range MEDIANReturns the median of given numbers MODEReturns the most frequent value MODE.SNGLReturns the most frequent value LARGEReturns the k-th largest value SMALLReturns the k-th smallest value PERCENTILEReturns the k-th percentile PERCENTILE.INCReturns the k-th percentile (inclusive) PERCENTRANKReturns percentile rank PERCENTRANK.INCReturns percentile rank (inclusive) PERCENTRANK.EXCReturns percentile rank (exclusive) QUARTILEReturns the quartile of a data set QUARTILE.INCReturns quartile (inclusive) QUARTILE.EXCReturns quartile (exclusive) RANKReturns the rank of a number in a list RANK.EQReturns the rank of a number RANK.AVGReturns averaged rank for ties STDEVEstimates standard deviation from sample STDEV.SEstimates standard deviation from sample STDEV.PCalculates standard deviation of population STDEVPCalculates standard deviation of population VAREstimates variance from sample VAR.SEstimates variance from sample VAR.PCalculates variance of population VARPCalculates variance of population AVEDEVReturns average of absolute deviations DEVSQReturns sum of squares of deviations GEOMEANReturns the geometric mean HARMEANReturns the harmonic mean TRIMMEANReturns mean of interior of data set FREQUENCYReturns frequency distribution as array SKEWReturns the skewness of a distribution KURTReturns the kurtosis of a data set CORRELReturns the correlation coefficient COVARReturns covariance COVARIANCE.PReturns population covariance COVARIANCE.SReturns sample covariance SLOPEReturns slope of linear regression line INTERCEPTReturns intercept of linear regression RSQReturns R-squared value STEYXReturns standard error of predicted y FORECASTCalculates a future value using linear regression FORECAST.LINEARCalculates a future value using linear regression FISHERReturns the Fisher transformation FISHERINVReturns inverse of Fisher transformation STANDARDIZEReturns a normalized value CONFIDENCEReturns confidence interval for population mean CONFIDENCE.NORMReturns confidence interval (normal distribution) CONFIDENCE.TReturns confidence interval (t-distribution) NORM.DISTReturns normal distribution NORMDISTReturns normal distribution NORM.S.DISTReturns standard normal distribution NORMSDISTReturns standard normal CDF NORM.INVReturns inverse of normal distribution NORMINVReturns inverse of normal distribution NORM.S.INVReturns inverse of standard normal NORMSINVReturns inverse of standard normal CDF LOGNORM.DISTReturns lognormal distribution LOGNORMDISTReturns lognormal CDF LOGINVReturns inverse of lognormal distribution LOGNORM.INVReturns inverse of lognormal distribution EXPON.DISTReturns exponential distribution EXPONDISTReturns exponential distribution POISSON.DISTReturns Poisson distribution POISSONReturns Poisson distribution BINOM.DISTReturns binomial distribution probability BINOMDISTReturns binomial distribution probability NEGBINOM.DISTReturns negative binomial distribution NEGBINOMDISTReturns negative binomial distribution HYPGEOM.DISTReturns hypergeometric distribution HYPGEOMDISTReturns hypergeometric distribution T.DISTReturns t-distribution TDISTReturns t-distribution T.DIST.RTReturns right-tailed t-distribution T.DIST.2TReturns two-tailed t-distribution T.INVReturns inverse of t-distribution T.INV.2TReturns inverse of two-tailed t-distribution T.TESTReturns probability from t-test TTESTReturns probability from t-test Z.TESTReturns one-tailed p-value of z-test ZTESTReturns one-tailed p-value of z-test CHISQ.DISTReturns chi-squared distribution CHIDISTReturns right-tailed chi-squared probability CHISQ.DIST.RTReturns right-tailed chi-squared distribution CHISQ.INVReturns inverse of chi-squared distribution CHIINVReturns inverse of right-tailed chi-squared CHISQ.INV.RTReturns inverse of right-tailed chi-squared CHISQ.TESTReturns chi-squared test result CHITESTReturns chi-squared test result F.DISTReturns F distribution FDISTReturns F distribution F.DIST.RTReturns right-tailed F distribution F.INVReturns inverse of F distribution FINVReturns inverse of F distribution F.INV.RTReturns inverse of right-tailed F distribution F.TESTReturns F-test result FTESTReturns F-test result GAMMALNReturns natural log of gamma function GAMMALN.PRECISEReturns natural log of gamma function GAMMA.DISTReturns gamma distribution GAMMADISTReturns gamma distribution GAMMA.INVReturns inverse of gamma distribution GAMMAINVReturns inverse of gamma distribution BETA.DISTReturns beta distribution BETADISTReturns beta cumulative distribution BETA.INVReturns inverse of beta distribution BETAINVReturns inverse of beta distribution WEIBULLReturns Weibull distribution WEIBULL.DISTReturns Weibull distribution COUNTIFCounts cells meeting a criterion COUNTIFSCounts cells meeting multiple criteria

IFERRORReturns value if no error, else alternate value ANDReturns TRUE if all arguments are TRUE ORReturns TRUE if any argument is TRUE NOTReverses the logic of its argument XORReturns TRUE if odd number of arguments are TRUE TRUEReturns the logical value TRUE FALSEReturns the logical value FALSE IFReturns one value if true, another if false IFNAReturns value if not #N/A, else alternate value

LENReturns the number of characters LEFTReturns leftmost characters RIGHTReturns rightmost characters MIDReturns characters from middle of text UPPERConverts text to uppercase LOWERConverts text to lowercase TRIMRemoves extra spaces from text CLEANRemoves non-printable characters CHARReturns character for a code UNICHARReturns Unicode character EXACTChecks if two texts are identical FIXEDFormats number as text with decimals DOLLARFormats number as currency text TReturns text if value is text NReturns number if value is number VALUETOTEXTConverts value to text ARRAYTOTEXTConverts array to text BASEConverts number to text in given base ROMANConverts number to Roman numeral CONCATENATEJoins text strings together TEXTFormats a number as text VALUEConverts text to a number FINDFinds text within text (case-sensitive) SEARCHFinds text within text (case-insensitive) SUBSTITUTESubstitutes new text for old text REPLACEReplaces characters within text PROPERCapitalizes first letter of each word

VLOOKUPLooks up value in first column and returns value in same row HLOOKUPLooks up value in first row and returns value in same column XLOOKUPSearches range and returns matching item XMATCHReturns relative position of item in array LOOKUPLooks up value in range MATCHReturns position of item in range INDEXReturns value at row and column in range OFFSETReturns reference offset from starting point ADDRESSReturns cell address as text ROWReturns row number of a reference COLUMNReturns column number of a reference ROWSReturns number of rows in a reference COLUMNSReturns number of columns in a reference FILTERFilters range based on criteria

ISBLANKReturns TRUE if cell is empty ISERRORReturns TRUE if value is any error ISNAReturns TRUE if value is #N/A ISNUMBERReturns TRUE if value is a number ISTEXTReturns TRUE if value is text ISNONTEXTReturns TRUE if value is not text ISLOGICALReturns TRUE if value is logical ISEVENReturns TRUE if number is even ISODDReturns TRUE if number is odd ISREFReturns TRUE if value is a reference ISFORMULAReturns TRUE if cell contains formula NAReturns the #N/A error value ERROR.TYPEReturns number for error type TYPEReturns a number indicating data type SHEETReturns sheet number of reference SHEETSReturns number of sheets in reference

TODAYReturns today's date NOWReturns current date and time DATECreates a date from year, month, day DATESTRINGFormats date as text string TIMECreates a time from hours, minutes, seconds YEARReturns the year from a date MONTHReturns the month from a date DAYReturns the day from a date WEEKDAYReturns day of week as number WEEKNUMReturns week number of year ISOWEEKNUMReturns ISO week number of year HOURReturns the hour from a time MINUTEReturns the minute from a time SECONDReturns the second from a time DAYSReturns days between two dates DAYS360Returns days between dates (360-day year) EDATEReturns date months before/after start EOMONTHReturns last day of month YEARFRACReturns year fraction between dates NETWORKDAYSReturns number of workdays between dates NETWORKDAYS.INTLReturns workdays with custom weekend WORKDAYReturns date after workdays WORKDAY.INTLReturns workday with custom weekend

PMTReturns periodic payment for loan PPMTReturns principal payment for period IPMTReturns interest payment for period CUMIPMTReturns cumulative interest paid CUMPRINCReturns cumulative principal paid FVReturns future value of investment PVReturns present value of investment NPVReturns net present value XNPVReturns NPV for irregular cash flows NPERReturns number of periods RATEReturns interest rate per period IRRReturns internal rate of return XIRRReturns IRR for irregular cash flows SLNReturns straight-line depreciation SYDReturns sum-of-years depreciation DBReturns declining balance depreciation DDBReturns double-declining depreciation EFFECTReturns effective annual interest rate NOMINALReturns nominal annual interest rate

SEQUENCEGenerates array of sequential numbers RANDARRAYGenerates array of random numbers UNIQUEReturns unique values from range SORTSorts array contents SORTBYSorts array by another array TRANSPOSETransposes rows and columns TOCOLReturns array in single column TOROWReturns array in single row TAKEReturns rows/columns from array start DROPRemoves rows/columns from array start EXPANDExpands array to specified dimensions VSTACKStacks arrays vertically HSTACKStacks arrays horizontally WRAPCOLSWraps row/column into columns WRAPROWSWraps row/column into rows CHOOSECOLSReturns specified columns from array CHOOSEROWSReturns specified rows from array MAKEARRAYCreates array using LAMBDA function LAMBDACreates custom reusable function LETAssigns names to calculation results MAPApplies LAMBDA to each value REDUCEReduces array to accumulated value SCANScans array with accumulator BYROWApplies LAMBDA to each row BYCOLApplies LAMBDA to each column

DELTATests whether two values are equal GESTEPTests if number >= step DEC2BINConverts decimal to binary DEC2HEXConverts decimal to hexadecimal DEC2OCTConverts decimal to octal BITANDReturns bitwise AND BITORReturns bitwise OR BITXORReturns bitwise XOR BITLSHIFTReturns number shifted left BITRSHIFTReturns number shifted right