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) |
