Jul132010

## Formulas and Functions

Formula

Description

Syntax

Simple Example

& combines cells together into one cell =(1st cell combining) & (second cell combining) =A1&B1
ABS Absolute Value =ABS(Cell or value) =abs(B1)
AND Returns true if all statements are true, false if any of them are not =AND(1st criteria, second criteria) =and(A1="company",B1="Yes")
AVERAGE Returns the average =AVERAGE(Range of cells) =average(A1:A50)
CHOOSE Returns a different cell/value based upon the value of another cell =CHOOSE(cell determining which you want chosen must be #, value1, value2, â€¦) =choose(A1,B1,B2,B3)
CLEAN Removes all non-printable characters from a cell =CLEAN(cell you are referencing) =clean(A1))
COLUMN Returns the column rumber of the reference =COLUMN(cell you are referencing) =column(D1)
COUNT Counts the number of cells included =COUNT(cells you are counting) =count(B5:K5)
COUNTA counts non-blank cells =COUNTA(cells you want to count the non-blank cells in) =counta(A1:A50)
COUNTBLANK counts blank cells =COUNTBLANK(cells you want to count the blank cells in) =countblank(A1:A50)
COUNTIF counts the number of cells that meet a criteria =COUNTIF(cells you are looking for criteria in, criteria) =countif(A1:A50,"company")
DATE Creates a date format cell based upon day, month, year inputs =DATE(year,month, day) =date(A1,B1,C1)
DAY Returns the day of a given date cell =DAY(date cell reference) =day(A1)
DB Depreciation function =DB( cost, salvage, life, period, number months ) =db(A1,B1,5, 3,6)
DDB Double declining balance depreciation =DDB( cost, salvage, life, period) =ddb(1000,500,4,2)
EXACT Tells you if 2 cells are identical in formatting and text. Returns true if they are, false if different =EXACT(1st cell, 2nd cell) =exact(A1,B1)
FIND Finds a value/text in a cell and returns where it is located within cell =FIND(text/value searching for, cell searching, starting point in cell <- optional) =find("company",A1)
HLOOKUP Finding information in a table based on an identifier in another row =HLOOKUP(Value matching, range it is looking in, number of rows down, exact "0" or close "1" ) =hlookup("company",A1:B50,2,0)
IF Looks to see if a statement is true. If it is, it does one thing, if not, another =IF(statement you want to check, what to do if true, what to do if false) =if(A1=company,"company","Not company")
INDEX returns a cell value from an array of data =INDEX(range of data, row number, column number) =INDEX(A1:D50,20,3)
ISERROR if the cell referenced is any sort of error, returns true, otherwise false =ISERROR(cell you are checking for errors) =iserror(A5)
ISNA if the cell referenced is #N/A returns true, otherwise false =ISNA(cell you are checking for #N/A) =isna(A5)
ISNUMBER if the cell referenced is a number returns true, otherwise false =ISNUMBER(cell you are checking) =isnumber(A5)
ISBLANK if the cell referenced is a number returns true, otherwise false =ISBLANK(cell you are checking for blanks) =isblank(A5)
ISTEXT if the cell referenced is text returns true, otherwise false =ISTEXT(cell you are checking) =istext(A5)
LEFT Returns the left part of a cell =LEFT(cell you are pulling from, number of characters you want to bring from cell starting on the left) =left(A1,5)
LEN Counts # of characters in a cell =LEN(cell you are counting) =len(A1)
LOWER Makes all letters in the cell lower case =LOWER(cell you are adjusting) =lower(A1)
MATCH Searches for a value in a range and returns its location =MATCH(value looking for, range looking in, 0 for exact match or 1 for closest) =match(2008,A1:A50)
MATCH Maximum Value =MAX(Range of cells) =max(A1:B50)
MID Combination of right and left functions, returns a specified portion from the middle of the cell =MID(cell you are pulling from, number of characters from the left side you want to start at, number of characters you want to bring over) =mid(A1,2,5)
MIN Minimum Value =MIN(Range of cells) =min(A1:B50)
MONTH Returns the month of a given date cell =MONTH(date cell reference) =month(A1)
NOW Populates with current time and date =NOW() =now()
NPV Calculates net present value =NPV(discount rate, value1, value2, value3â€¦) =npv(8%,A1:A10)
OFFSET returns a cell value based on its physical relation to a reference cell =OFFSET(starting point, rows, columns) =offset(A1,3,5)
OR Returns true if either statement is true, false if neither =OR(1st criteria,second criteria) =or(A1="company",A1="ABC")
PROPER Puts a cell in "proper format" with capital first letters of each word, lower case the rest =PROPER(Cell you are adjusting) =proper(A1)
RAND Returns a random number between 0 and 1 =RAND() =rand()
RANK Ranks values in an array =RANK(Cell you are ranking, cells you are ranking it in, order <=optional) =rank(C5,C1:C20,0)
REPLACE Replaces characters in a reference cell =REPLACE(old text, start of replacement, number of_chars, new text ) =replace("GRB",1,3,"CB")
RIGHT Returns the right part of a cell =RIGHT(Cell you are pulling from, number of characters you want to bring from Cell starting on the right) =right(A1,5)
ROUND Rounds a numeric value in a cell =ROUND(cell you are referencing, number of digits to round to) =round(A1,2)
ROUNDDOWN Rounds a numeric value down =ROUNDDOWN(cell you are referencing, number of digits to round to) =rounddown(A1,0)
ROUNDUP Rounds a numeric value up =ROUNDUP(cell you are referencing, number of digits to round to) =roundup(A1,0)
ROW Returns the row number of a cell =ROW(Cell you want to see row # for) =row(B5)
SEARCH Finds a value/text in a cell and returns where it is located within cell =SEARCH(text/value searching for, cell searching, starting point in cell <- optional) =search("company",A1)
STDDEV Calculates standard deviation =STDDEV(Range of cells) =stddev(A1:A50)
SUMIF Summing rows, columns or groups of data if they meet certain criteria =SUMIF(Data being used to match criteria, criteria,Data potentially being summed) =sumif(A:A,"company","B:B")
TEXT Adjusts the format of text =TEST(cell you are adjusting," format you want it in in quotes") =text(A1,"mmddyy")
TRIM removes any spaces at the beginning or end of a cell =TRIM(cell you are referencing) =trim(A1)
UPPER Capitalizes all letters in the cell =UPPER(cell you are adjusting) =upper(A1)
VLOOKUP Finding information in a table based on an identifier in another column =VLOOKUP(value matching, range it is looking in, number of columns over, exact (0) or close (1) =vlookup("company",A1:B50,2,0)
WEEKDAY Returns the day of the week for a given date =WEEKDAY(date cell reference) =weekday(A1)
YEAR Returns the year of a given date cell =YEAR(date cell reference) =year(A1)

Need Support or Help?

Send us an Email, we'll get back to you asap.