Excel has a wide variety of formulas that can be used to perform various calculations and operations on data. There are many formulas available in Excel, here are some of the most used ones:
SUM:
Adds a range of cells. For example, =SUM(A1:A10) would add all the values in cells A1 through A10.
 |
| Figure 1 |
 |
| Figure 1.1 |
AVERAGE:
Returns the average of a range of cells. For example, =AVERAGE(A1:A10) would return the average of all the values in cells A1 through A10.
 |
| Figure 1.2 |
 |
| Figure 1.3 |
COUNT:
Returns the
number of cells in a range that contain numbers. For example, =COUNT(A1:A10)
would return the number of cells in the range A1:A10 that contain numbers.
 |
| Figure 1.4 |
 |
| Figure 1.5 |
MAXIMUM:
Returns the
largest value in a range of cells. For example, =MAX(A1:A10) would return the
largest value in cells A1 through A10.
 |
| Figure 1.6 |
 |
| Figure 1.7 |
MINIMUM:
Returns the smallest value in a range of cells. For example, =MIN(A1:A10) would return the smallest value in cells A1 through A10.
 |
| Figure 1.8 |
 |
| Figure 1.9 |
IF:
Returns a value
if a certain condition is true, and another value if the condition is false.
For example, =IF(A1>5, “Greater than 5”, “Less than or equal to 5”).
 |
| Figure 2.0 |
 |
| Figure 2.1 |
VLOOKUP:
The VLOOKUP
(Vertical Lookup) formula in MS Excel is used to look for a specific value in a
table and return a corresponding value from another column of the same row. The
syntax for the VLOOKUP formula is:
=VLOOKUP(lookup_value,
table_array, col_index_num, [range_lookup])
- "lookup_value" is the value that you want to look for in the first column of the table.
- "table_array" is the range of cells that make up the table you want to search.
- "col_index_num" is the column number in the table array from which the matching value must be returned.
- "range_lookup" is a optional parameter, If it is set as TRUE (default) or 1, it will look for an approximate match and return the closest match. If it is set as FALSE or 0, it will look for an exact match.
For example,
if you have a table of data in the range A1:C5, with the values in column A
being the unique identifier, and you want to return the corresponding value in
column B for a given identifier in cell D1. The formula will be
=VLOOKUP(D1,A1:C5,2,FALSE)
It will look
for the exact match of value in D1 in column A of range A1:C5, if it finds it
will return the value from column B of the same row.
 |
| Figure 2.2 |
 |
| Figure 2.3 |
 |
| Figure 2.4 |
CONCATENATE:
Combines the text from two or more cells into one cell. For example, =CONCATENATE(A1, B1) would combine the text from cells A1 and B1 into one cell.
 |
| Figure 2.5 |
 |
| Figure 2.6 |
Comments
Post a Comment