.png)
Examples of Formulas (for Microsoft Excel)
The table below provides the basic details as how the nature this formulas used in a program like Microsoft Excel.Examples of Formulas
|
=150*.05
|
Multiplies
150 times 0.05. This formula uses only values, and it always returns the same
result. You could just enter the value 7.5 into the cell.
|
|
=A3
|
Returns the
value in cell A3. No calculation is performed.
|
|
=A1+A2
|
Adds the
values in cells A1 and A2.
|
|
=Income–Expenses
|
Subtracts
the value in the cell named Expenses from the value in the cell named Income.
|
|
=SUM(A1:A12)
|
Adds the
values in the range A1:A12, using the SUM function.
|
|
=A1=C12
|
Compares
cell A1 with cell C12. If the cells are identical, the formula returns TRUE;
otherwise, it returns FALSE.
|
|
=”Part-”&”23A”
|
Joins
(concatenates) the two text strings to produce Part-23A.
|
|
=A1&A2
|
Concatenates
the contents of cell A1 with cell A2. Concatenation works with values as well
as text. If cell A1 contains 123 and cell A2 contains 456, this formula would
return the text 123456.
|
|
=6^3
|
Raises 6 to
the third power (216).
|
|
=216^(1/3)
|
Raises 216
to the 1⁄3 power. This is mathematically equivalent to calculating the cube
root of 216, which is 6.
|
|
=A1<A2
|
Returns
TRUE if the value in cell A1 is less than the value in cell A2. Otherwise, it
returns FALSE. Logical comparison operators also work with text. If A1
contains Bill and A2 contains Julia, the formula would return TRUE because
Bill comes before Julia in alphabetical order.
|
|
=A1<=A2
|
Returns
TRUE if the value in cell A1 is less than or equal to the value in cell A2.
Otherwise, it returns FALSE.
|
|
=A1<>A2
|
Returns
TRUE if the value in cell A1 is not equal to the value in cell A2. Otherwise,
it returns FALSE.
|
Excel
Error Values
|
Error Value
|
Explanation
|
|
#DIV/0!
|
The formula is trying to divide by zero. This also
occurs when the formula attempts to divide by what is in a cell that is empty.
|
|
#NAME?
|
The formula uses a name that Excel does not
recognize. This can happen if you delete a name that is used in the formula
or if you have unmatched quotes when using text.
|
|
#N/A
|
The formula is referring (directly or indirectly) to
a cell that uses the NA function to signal that data is not available. Some
functions (for example, VLOOKUP) can also return #N/A.
|
|
#NULL!
|
The formula uses an intersection of two ranges that
do not intersect.
|
|
#NUM!
|
A problem with a value exists; for example, you
specified a negative number where a positive number is expected.
|
|
#REF!
|
The formula refers to a cell that is not valid. This
can happen if the cell has been deleted from the worksheet.
|
|
#VALUE!
|
The formula includes an argument or operand of the
wrong type. (An operand is a value or cell reference that a formula uses to
calculate a result.)
|
Reference
Operators for Ranges
|
Operator
|
What It Does
|
|
: (colon)
|
Specifies a range.
|
|
, (comma)
|
Specifies the union of two ranges. This operator
combines multiple range references into a single reference.
|
|
Space
|
Specifies the intersection of two ranges. This
operator produces cells that are common to two ranges.
|
Examples
of Formulas Using the COUNTIF Function
|
=COUNTIF(Data,12)
|
Returns the number of cells containing the value 12.
|
|
=COUNTIF(Data,”<0”)
|
Returns the number of cells containing a negative
value.
|
|
=COUNTIF(Data,”<>0”)
|
Returns the number of cells not equal to 0.
|
|
=COUNTIF(Data,”>5”)
|
Returns the number of cells greater than 5.
|
|
=COUNTIF(Data,A1)
|
Returns the number of cells equal to the contents of
cell A1.
|
|
=COUNTIF(Data,”>”&A1)
|
Returns the number of cells greater than the value
in cell A1.
|
|
=COUNTIF(Data,”*”)
|
Returns the number of cells containing text.
|
|
=COUNTIF(Data,”???”)
|
Returns the number of text cells containing exactly
three characters.
|
|
=COUNTIF(Data,”budget”)
|
Returns the number of cells containing the single
word budget (not case sensitive).
|
|
=COUNTIF(Data,”*budget*”)
|
Returns the number of cells containing the text
budget anywhere within the text.
|
|
=COUNTIF(Data,”A*”)
|
Returns the number of cells containing text that
begins with the letter A (not case sensitive).
|
|
=COUNTIF(Data,TODAY())
|
Returns the number of cells containing the current
date.
|
|
=COUNTIF(Data,”>”&AVERAGE
(Data))
|
Returns the number of cells with a value greater
than the average of the values.
|
|
=COUNTIF(Data,”>”&AVERAGE
(Data)+STDEV(Data)*3)
|
Returns the number of values exceeding three
standard deviations above the mean.
|
|
=COUNTIF(Data,3)+COUNTIF
(Data,-3)
|
Returns the number of cells containing the value 3
or –3.
|
|
=COUNTIF(Data,TRUE)
|
Returns the number of cells containing the logical
value TRUE.
|
|
=COUNTIF(Data,TRUE)+
COUNTIF(Data,FALSE)
|
Returns the number of cells containing a logical
value (TRUE or FALSE).
|
|
=COUNTIF(Data,”#N/A”)
|
Returns the number of cells containing the #N/A
error value.
|
0 comments: