R237 EXCEL FORMULAS
COUNTIF (range, criteria)
Range: is the range of cells from which you want to count cells
Criteria: is the criteria in the form of a number, expression, or text that defines which cells will be counted.
=COUNTIF(A2:A26, A32) ... this example looks in the group of cells (A2:A26) for whatever is
contained in A32 and gives you a count of how many times it occurs.
SUMIF(range, criteria, sum_range)
Range: is the range of cells you want evaluated
Criteria: is the criteria in the form of a number, expression, or text that defines which cells will be added.
Sum_range: is the actual cells to sum. The cells in sum_range are summed only if their corresponding
celIs in range match the criteria
=SUMIF(A2:A26,A32,B2:B26)…this example identifies the amount of times the information in
A32 shows up in the range of A2:A26 and then adds the information in the corresponding cell of
IF(test, value if true, value if false)
The IF function checks a condition that must be either true or false. If the condition is true, the function
returns one value; if the condition is false, the function returns another value.
Test: any value or expression that can be evaluated to True or False
Value if true: the value returned if the test is True
Value if false: the value returned if the test is False
=IF(B4<200,10%,15%)…this example says that if the value in B4 is less than 200, then 10% is
entered into the cell, if false, 15% is entered.
SUM(number 1, number 2... )
Adds all the numbers in a range of cells.
=SUM(3, 2) ... equals 5
=SUM(B4:B7) ... adds the data contained in the range of cells between B4 and B7
=SUM(B4, C5)…add the data contained in the cells B4 and C5
AVERAGE(number 1, number 2…)
=AVERAGE(A1:A5)…averages the data within this range of cells
=AVERAGE(A1:A5, 5)…averages the data within this range of cells while also averaging
in the number 5.
MEDIAN(number 1, number 2 ... )
The median is the number in the middle of a set of numbers ... half the numbers have values that
are greater than the median, and half have values that are less.
=MEDIAN(B3:B9)…gives the median number for this range of cells.
MODE(number 1, number 2.. ),
The mode is the most frequently occurring, or repetitive number in the range of cells.
=MODE(B4:B12) ... gives you the number that is repeated most frequently ... if there is no
number repeated twice, a value of #N/A appear.
COUNT(value 1, value 2.. )
Counts the number of cells that contain numbers.
=COUNT(AI:A7) .. tells you how many numbers are present within this range of cells.
Rounds a number to a specified number of digits.
=ROUND(2.15,1) ... will round to 2.2
=ROUND(B4,2) will round the number in cell B4 to the hundredth decimal point
Rounds a number up, away from zero. When using zero, the number will be rounded to the
nearest integer. When using a positive number greater than zero, the number will be rounded to
the nearest decimal point. When using a negative number, the number is rounded to the left of
the decimal point.
=ROUNDUP(3.2, O) ... rounds up to 4
=ROUNDUP(3.14159, 3)… rounds up to 3.142
ROUNDDOWN(number 1, num digits)
Rounds a number down, toward zero.
=ROUNDDOWN(3.2, O) ... rounds down to 3
=ROUNDDOWN(3.14159, 3) ... rounds down to 3.141