REPT Function
This function will repeat a given text any number of times you say
Column A are the results. Column B is the formula.
Text this is the value or text we want repeated
Number of times this tells Excel how many times you want it repeated.
In ce
REPLACE Function
This function will find a character or string in a cell and replace it with what you specify.
Old Text this is the cell that we
want to change
Start num this is the character
that we will start the replace at
Num chars this is how many
ch
RANDBETWEEN Function
This function will give you a whole random number between two numbers you specify.
Bottom This is the lowest number of the range that you want to get the random number
Top This is the highest number of the range that you want to get t
PRODUCT Function
This function will give you the product (multiply) of a group of numbers you supply.
Number1,2,3 These are the
numbers you want the product of.
This will multiply all these numbers.
They can be a reference to a cell or an
actual number.
E
RAND Function
This function will give you a random number between 0 and 1.
This function doesnt require any variables added.
Cell A1 has the formula =Rand() which will give you a random number from 0 to 1. You can
then press the delete key in any other ce
PROPER Function
This function will convert all the letters in a string to the proper case, where the first letter of
each word is capitalized and the rest at small letters.
Text This is the string you want to
change to proper letters
We use 2 example here
OR Function
This is a logical function that only returns TRUE or FALSE. If any of the criteria asked in the
function are TRUE, then this returns a TRUE. Otherwise it returns a FALSE.
In this example, we want to see if each person scored over 200 in ANY ga
OFFSET Function
This function will return a cell or range of cells
Reference This is our starting point.
Can be one cell or an array.
Rows This is how many rows we want to
move the cursor up or down
Cols - This is how many rows we want to
move the cursor
NOW Function
This function will put todays date and time into a cell. It is dynamic as it will change as the day
and time changes.
The Now function will put in todays date and time. There are no variables that need to go
into it. This will change when the
MIN Function
This function will find the smallest value in a group of cells. This group of cells can be numbers
or arrays or dates. Opposite of the MAX function.
In this example, we have a group of numbers and dates.
Column B: We use the formula =MIN(B2:B
MID Function
This function will pull in any number of characters from the left side of a text string.
Text This is the string you want to pull the characters from
Start_num This is the character you want to start pulling
from. If you put 7, you start at t
LEN Function
This function will tell you how many characters are in a cell.
Text This is the string you want
to count the characters
We use 2 examples here. The result is in column B and the function is next to it in column C.
Row 2 The formula we use in
MAX Function
This function will find the largest value in a group of cells. This group of cells can be numbers
or arrays or dates.
In this example, we have a group of numbers and dates.
Column B: We use the formula =MAX(B2:B11) so Excel will look in the c
MATCH Function
This function will return the relative position of a value in an array. You can choose to find an
exact match or next closest match.
Lookup_value This is what we are looking for. It can be text or numbers. Can also use an *
or ? as a wildca
LOWER Function
This function will convert all the letters in a string to upper case.
Text This is the string you
want to change to lower
letters
We use 1 example here. The result is in column B and the function is next to it in column C.
Row 2 The formula
ISBLANK Function
This function will tell you if a cell is blank by displaying TRUE.
Text This is the cell you want to
check if it is blank
The result is in column B and the function is next to it in column C.
In cell B2, we have the formula =ISBLANK(A2).
LARGE Function
This function will find the nth smallest value in a group of cells. That means you tell it if you
want the 2nd smallest, 3rd, 4th and so on.
Array: The range of cells we are looking in
K: This is the nth largest value we want to
pull. If we
INDEX Function
This function will return a cell or range of cells within a given array
Array This is the array we
want to look in to find our
cell or cells
Row num This is what row
in the array we want to pull
Column num - This is
what column in the array
ISERROR Function
This function will tell you if you have an error message in a cell by displaying TRUE.
Text This is the string you want
to check for an error
The result is in column B and the function is next to it in column C.
In cell B2 we have the for
LEFT Function
This function will pull in any number of characters from the left side of a text string.
Text This is the string you want to pull the characters from
Num_chars: How many characters do you want to pull from
the left side of the string
We use
ISNA Function
This function will tell you if you have an #N/A result in a cell. This basically tells you there is an
error in the formula.
Text This is the string you want to
check for #N/A
The result is in column B and the function is next to it in colum
FIXED Function
This function will turn a number in text and round it to the number of decimal places you
specify
Number: This is the number you want rounded
Decimals: How many decimal spots you want to
round the number to
No_commas: True if you dont want
COUNTIFS Function
This function will Count the cells in your range that match multiple criteria.
Criteria_range1 This is the first range of what Excel is to look at. A2:A10. We want to look at
Student names
Criteria1 This is the criteria we want to find i
HLOOKUP Function
This function will search a table by row for an item you choose and then pull in a
corresponding cell related to it.
In this example we want
to pull Joes English score
from the main table
array of student names
on the top and classes on
t
IF Function
This function will let you choose a True/False criteria and then give you a different result,
depending on if the answer is True or False
Here we have 3 students with Math and English grades. In column D we want to tell if their
Math score or
EXACT Function
This function will test two strings and tell you if they Exactly match or not.
Col C is the result and Col D is the formula thats in Col C
In C1, we want to check if A1 matches B1. Since joe equals joe, then the result is true.
Everything m
Find Function
This function will find a character or string in a cell and return its position. It IS case sensitive.
Use SEARCH function if you want it NOT Case Sensitive.
Column B are the results. Column C is the formula.
Find_Text this is the character
COUNTA Function
This function will count how many cells are not blank.
Value1 This is the range of cells you want check. If you
had another range of cells, you would put it in Value2.
In our example, we have our formula in A8, which is =COUNTA(A1:A7). Thi
CONCATENATE Function
This function will combine multiple cells of your choice into one cell. However, it is easier to
just use the & symbol to combine. I will give examples of both.
We use 3 examples here.
Row 2: CONCATENATE(A2,"-",B2,"-",C2) The wizard l
COUNTBLANK Function
This function will count how many cells are blank.
RANGE This is the range of cells you want
check.
In our example, we have our formula in A8, which is =COUNTBLANK(A1:A7). This will look at
those cells and return the number of cells th