A To Z of Excel Functions | Series 1
INTRODUCTION
Excel is a spreadsheet program that helps calculate and organize data. It has many functions that allow you to do more than just calculations with your data.
There are so many different functions in Excel that it is impossible to cover all of them in one article. But we will cover all in 5 different sections. We will show their functionalities, as well as how to use them for a range of purposes from data calculations to data visualizations.
What is a Function? How to Find and Insert Functions on a Spreadsheet
A function is a set of instructions for the spreadsheet that allows you to perform calculations on data. Functions can be inserted into a spreadsheet by typing them in, or selecting from a list.
A function is typically made up of three components: a name, an argument, and an instruction. The name identifies what type of calculation the function will perform. Arguments are values that correspond to the data you want to use within the function. Instructions tell the spreadsheet how to handle those arguments and what calculation to carry out with them.
A good example of a function would be "SUM" for adding numbers together, where it would take in two arguments (the cell references) and add them together as instructed (by default it would sum up all cells in between).
Excel functions are a set of tools that can be used to manipulate and calculate data in a spreadsheet. There are many different types of functions that can be utilized to provide more detail, or make tasks simpler.
Below are some common ones:
FORMULA: =IFERROR(A1,A2)
FORMULA: MAX(B1:B2)
FORMULA: SUM()
A To Z of Excel Functions | Series 1
1. ABS FUNCTION
What does it do?
This function calculates the value of a number, irrespective of whether it is positive or negative.
Syntax
=ABS(CellAddress or Number)
Formatting
The result will be shown as a number, no special formatting is needed.
The following table was used by a company testing a machine which cuts timber.
The machine needs to cut timber to an exact length.
Three pieces of timber were cut and then measured.
In calculating the difference between the Required Length and the Actual Length it does
not matter if the wood was cut too long or short, the measurement needs to be expressed as
an absolute value.
Table 1 shows the original calculations.
The Difference for Test 3 is shown as negative, which has a knock on effect
when the Error Percentage is calculated.
Whether the wood was too long or short, the percentage should still be expressed
as an absolute value.
 |
| Table 1 |
Table 2 shows the same data but using the =ABS() function to correct the calculations.
 |
| Table 2 |
2. ADDRESS FUNCTION
What Does It Do ?
This function creates a cell reference as a piece of text, based on a row and column
numbers given by the user.
This type of function is used in macros rather than on the actual worksheet.
Syntax
=ADDRESS(RowNumber,ColNumber,Absolute,A1orR1C1,SheetName)
The RowNumber is the normal row number from 1 to 16384.
The ColNumber is from 1 to 256, cols A to IV.
The Absolute can be 1,2,3 or 4.
- When 1 the reference will be in the form $A$1, column and row absolute.
- When 2 the reference will be in the form A$1, only the row absolute.
- When 3 the reference will be in the form $A1, only the column absolute.
- When 4 the reference will be in the form A1, neither col or row absolute.
The A1orR1C1 is either TRUE of FALSE.
- When TRUE the reference will be in the form A1, the normal style for cell addresses.
- When FALSE the reference will be in the form R1C1, the alternative style of cell address.
The SheetName is a piece of text to be used as the worksheet name in the reference.
The SheetName does not actually have to exist.
 |
| CLICK TO LEARN MORE & DOWNLOAD! |
3. AND FUNCTION
What Does It Do?
This function tests two or more conditions to see if they are all true.
It can be used to test that a series of numbers meet certain conditions.
It can be used to test that a number or a date falls between an upper and lower limit.
Normally the AND() function would be used in conjunction with a function such as =IF().
Syntax
=AND(Test1,Test2)
Note that there can be up to 30 possible tests.
Formatting
When used by itself it will show TRUE or FALSE.
Example 1
The following example shows a list of examination results.
The teacher wants to find the pupils who scored above average in all three exams.
The =AND() function has been used to test that each score is above the average.
The result of TRUE is shown for pupils who have scored above average in all three exams.
=AND(C38>=AVERAGE($C$29:$C$38),D38>=AVERAGE($D$29:$D$38),E38>=AVERAGE($E$29:$E$38))
4. AREA FUNCTION
What Does It Do?
This function tests a range to determine whether it is a single block of data, or whether
it is a multiple selection.
If it is a single block the result will be 1.
If it is a multiple block the result will be the number of ranges selected.
The function is designed to be used in macros.
Syntax
=AREAS(RangeToTest)
Formatting
The result will be shown as a number.
Example
The example at the top of this page shows two ranges coloured pink and green.
These ranges have been given the name STUDENTS.
The =AREAS(STUDENTS) gives a result of 2 indicating that there are two separate
selections which form the STUDENTS range.
Note
To name multiple ranges the CTRL key must be used.
In the above example the pink range was selected as normal, then the Ctrl key
was held down before selecting the green range.
When a Range Name is created it will consider both Pink and Green as being one range.
5. AVERAGE FUNCTION
What Does It Do ?
This function calculates the average from a list of numbers.
If the cell is blank or contains text, the cell will not be used in the average calculation.
If the cell contains zero 0, the cell will be included in the average calculation.
Syntax
=AVERAGE(Range1,Range2,Range3... through to Range30)
Formatting
No special formatting is needed.
Note
To calculate the average of cells which contain text or blanks use =SUM() to get the total and
then divide by the count of the entries using =COUNTA().
6. BIN2DEC FUNCTION
What Does It Do?
This function converts a binary number to decimal.
Negative numbers are represented using two's-complement notation.
Syntax
=BIN2DEC(BinaryNumber)
The binary number has a limit of ten characters.
Formatting
No special formatting is needed.
7. CEILING FUNCTION
What Does It Do?
This function rounds a number up to the nearest multiple specified by the user.
Syntax
=CEILING(ValueToRound,MultipleToRoundUpTo)
The ValueToRound can be a cell address or a calculation.
Formatting
No special formatting is needed.
Example 1
The following table was used by a estate agent renting holiday apartments.
The properties being rented are only available on a weekly basis.
When the customer supplies the number of days required in the property the =CEILING()
function rounds it up by a multiple of 7 to calculate the number of full weeks to be billed.
Example 2
The following table was used by a builders merchant delivering products to a construction site.
The merchant needs to hire trucks to move each product.
Each product needs a particular type of truck of a fixed capacity.
Table 1 calculates the number of trucks required by dividing the Units To Be Moved by
the Capacity of the truck.
This results of the division are not whole numbers, and the builder cannot hire just part
of a truck.
Table 2 shows how the =CEILING() function has been used to round up the result of
the division to a whole number, and thus given the exact amount of trucks needed.
Example 3
The following tables were used by a shopkeeper to calculate the selling price of an item.
The shopkeeper buys products by the box.
The cost of the item is calculated by dividing the Box Cost by the Box Quantity.
The shopkeeper always wants the price to end in 99 pence.
Table 1 shows how just a normal division results in varying Item Costs.
Table 2 shows how the =CEILING() function has been used to raise the Item Cost to
always end in 99 pence.
Explanation
=INT(E83) - Calculates the integer part of the price.
=MOD(E83,1) - Calculates the decimal part of the price.
=CEILING(MOD(E83),0.99) - Raises the decimal to 0.99
8. CELL FUNCTION
What Does It Do?
This function examines a cell and displays information about the contents, position and formatting.
Syntax
=CELL("TypeOfInfoRequired",CellToTest)
The TypeOfInfoRequired is a text entry which must be surrounded with quotes " ".
Formatting
No special formatting is needed.
Codes used to show the formatting of the cell.
Example
The following example uses the =CELL() function as part of a formula which extracts the filename.
The name of the current file is : FORMULA BANK.xlsx
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
9. CHAR FUNCTION
What Does It Do?
This function converts a normal number to the character it represent in the ANSI
character set used by Windows.
Syntax
=CHAR(Number)
The Number must be between 1 and 255.
Formatting
The result will be a character with no special formatting.
Example
The following is a list of all 255 numbers and the characters they represent.
Note that most Windows based program may not display some of the special characters, these will be displayed as a small box.
Note:
Number 32 does not show as it is the SPACEBAR character.
10. CHOOSE FUNCTION
What Does It Do?
This function picks from a list of options based upon an Index value given to by the user.
Syntax
=CHOOSE(UserValue, Item1, Item2, Item3 through to Item29)
Formatting
No special formatting is required.
Example
The following table was used to calculate the medals for athletes taking part in a race.
The Time for each athlete is entered.
The =RANK() function calculates the finishing position of each athlete.
The =CHOOSE() then allocates the correct medal.
The =IF() has been used to filter out any positions above 3, as this would cause
the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it.
11. CLEAN FUNCTION
What Does It Do?
This function removes any nonprintable characters from text.
These nonprinting characters are often found in data which has been imported
from other systems such as database imports from mainframes.
Syntax
=CLEAN(TextToBeCleaned)
Formatting
No special formatting is needed. The result will show as normal text.
12. CODE FUNCTION
What Does It Do?
This function shows the ANSI value of a single character, or the first character in a piece
of text.
The ANSI character set is used by Windows to identify each keyboard character by using
a unique number.
There are 255 characters in the ANSI set.
Syntax
=CODE(Text)
Formatting
No special formatting is needed, the result will be shown as a number between 1 and 255.
Example
See the example for FREQUENCY.
13. COMBIN FUNCTION
What Does It Do?
This function calculates the highest number of combinations available based upon
a fixed number of items.
The internal order of the combination does not matter, so AB is the same as BA.
Syntax
=COMBIN(HowManyItems,GroupSize)
Formatting
No special formatting is required.
Example 1
This example calculates the possible number of pairs of letters available
from the four characters ABCD.
14. CONCATENATE FUNCTION
What Does It Do?
This function joins separate pieces of text into one item.
Syntax
=CONCATENATE(Text1,Text2,Text3...Text30)
Up to thirty pieces of text can be joined.
Formatting
No special formatting is needed, the result will be shown as normal text.
Note
You can achieve the same result by using the & operator.
15. CONVERT FUNCTION
What Does It Do?
This function converts a value measure in one type of unit, to the same value expressed
in a different type of unit, such as Inches to Centimetres.
Syntax
=CONVERT(AmountToConvert,UnitToConvertFrom,UnitToConvertTo)
Formatting
No special formatting is needed.
Example
The following table was used by an Import / Exporting company to convert the weight
and size of packages from old style UK measuring system to European system.
16. CORREL FUNCTION
What Does It Do?
This function examines two sets of data to determine the degree of relationship
between the two sets.
The result will be a decimal between 0 and 1.
The larger the result, the greater the correlation.
In Table 1 the Monthly temperature is compared against the Sales of air conditioning units.
The correlation shows that there is an 0.864 realtionship between the data.
In Table 2 the Cost of advertising has been compared to Sales.
It can be formatted as percentage % to show a more meaning full result.
The correlation shows that there is an 28% realtionship between the data.
Syntax
=CORREL(Range1,Range2)
Formatting
The result will normally be shown in decimal format.
 |
| Learn More & Download Grading & Result App |
17. COUNT FUNCTION
What Does It Do?
This function counts the number of numeric entries in a list.
It will ignore blanks, text and errors.
Syntax
=COUNT(Range1,Range2,Range3... through to Range30)
Formatting
No special formatting is needed.
Example
The following table was used by a builders merchant to calculate the number of sales
for various products in each month.
18.COUNTA FUNCTION
What Does It Do?
This function counts the number of numeric or text entries in a list.
It will ignore blanks.
Syntax
=COUNTA(Range1,Range2,Range3... through to Range30)
Formatting
No special formatting is needed.
Example
The following table was used by a school to keep track of the examinations taken by each pupil.
Each exam passed was graded as 1, 2 or 3.
A failure was entered as Fail.
The school needed to known how many pupils sat each exam.
The school also needed to know how many exams were taken by each pupil.
The =COUNTA() function has been used because of its ability to count text and numeric entries.
19. COUNTBLANK FUNCTION
What Does It Do?
This function counts the number of blank cells in a range.
Syntax
=COUNTBLANK(RangeToTest)
Formatting
No special formatting is needed.
Example
The following table was used by a company which was balloting its workers on whether
the company should have a no smoking policy.
Each of the departments in the various factories were questioned.
The response to the question could be Y or N.
As the results of the vote were collated they were entered in to the table.
The =COUNTBLANK() function has been used to calculate the number of departments which
have no yet registered a vote.
20. COUNTIF FUNCTION
What Does It Do?
This function counts the number of items which match criteria set by the user.
Syntax
=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)
The criteria can be typed in any of the following ways.
To match a specific number type the number, such as =COUNTIF(A1:A5,100)
To match a piece of text type the text in quotes, such as =COUNTIF(A1:A5,"Hello")
To match using operators surround the expression with quotes, such as =COUNTIF(A1:A5,">100")
Formatting
No special formatting is needed.
21. DATE FUNCTION
What Does It Do?
This function creates a real date by using three normal numbers typed into separate cells.
Syntax
=DATE(year,month,day)
Formatting
The result will normally be displayed in the dd/mm/yy format.
By using the Format,Cells,Number,Date command the format can be changed.
22. DATEDIF FUNCTION
What Does It Do?
This function calculates the difference between two dates.
It can show the result in weeks, months or years.
Syntax
=DATEDIF(FirstDate,SecondDate,"Interval")
FirstDate : This is the earliest of the two dates.
SecondDate : This is the most recent of the two dates.
"Interval" : This indicates what you want to calculate.
These are the available intervals.
"d" Days between the two dates.
"m" Months between the two dates.
"y" Years between the two dates.
"yd" Days between the dates, as if the dates were in the same year.
"ym" Months between the dates, as if the dates were in the same year.
"md" Days between the two dates, as if the dates were in the same month and year.
Formatting
No special formatting is needed.
You can put this all together in one calculation, which creates a text version.
Age is 61 Years, 7 Months and 9 Days
="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"
23. DATEVALUE FUNCTION
What Does It Do?
The function is used to convert a piece of text into a date which can be used in calculations.
Dates expressed as text are often created when data is imported from other programs, such as
exports from mainframe computers.
Syntax
=DATEVALUE(text)
Formatting
The result will normally be shown as a number which represents the date. This number can
be formatted to any of the normal date formats by using Format,Cells,Number,Date.
Example
The example uses the =DATEVALUE and the =TODAY functions to calculate the number of days remaining on a property lease.
The =DATEVALUE function was used because the date has been entered in the cell as
a piece of text, probably after being imported from an external program.
24. DAVERAGE FUNCTION
What Does It Do?
This function examines a list of information and produces and average.
Syntax
=DAVERAGE(DatabaseRange,FieldName,CriteriaRange)
The DatabaseRange is the entire list of information you need to examine, including the
field names at the top of the columns.
The FieldName is the name, or cell, of the values to be averaged, such as "Unit Cost" or F3.
The CriteriaRange is made up of two types of information.
The first set of information is the name, or names, of the Fields(s) to be used as the basis for selecting the records, such as the category Brand or Wattage.
The second set of information is the actual record, or records, which are to be selected, such as Horizon as a brand name, or 100 as the wattage.
Formatting
No special formatting is needed.
25. DAY FUNCTION
What Does It Do?
This function extracts the day of the month from a complete date.
Syntax
=DAY(value)
Formatting
Normally the result will be a number, but this can be formatted to show the actual
day of the week by using Format,Cells,Number,Custom and using the code ddd or dddd.
26. DAYS360 FUNCTION
What Does It Do?
Shows the number of days between two dates based on a 360-day year (twelve 30-day months).
Use this function if your accounting system is based on twelve 30-day months.
Syntax
=DAYS360(StartDate,EndDate,TRUE of FALSE)
TRUE : Use this for European accounting systems.
FALSE : Use this for USA accounting systems.
Formatting
The result will be shown as a number.
Note
The calculation does not include the last day. The result of using 1-Jan-98 and 5-Jan-98 will give a result of 4. To correct this add 1 to the result. =DAYS360(Start,End,TRUE)+1
27. DB FUNCTION
What Does It Do?
This function calculates deprecation based upon a fixed percentage.
The first year is depreciated by the fixed percentage.
The second year uses the same percentage, but uses the original value of the item less
the first years depreciation.
Any subsequent years use the same percentage, using the original value of the item less
the depreciation of the previous years.
The percentage used in the depreciation is not set by the user, the function calculates
the necessary percentage, which will be vary based upon the values inputted by the user.
An additional feature of this function is the ability to take into account when the item was
originally purchased.
If the item was purchased part way through the financial year, the first years depreciation
will be based on the remaining part of the year.
Syntax
=DB(PurchasePrice,SalvageValue,Life,PeriodToCalculate,FirstYearMonth)
The FirstYearMonth is the month in which the item was purchased during the
first financial year. This is an optional value, if it not used the function will assume 12 as
the value.
Formatting
No special formatting is needed.
28. DCOUNT
What Does It Do?
This function examines a list of information and counts the values in a specified column.
It can only count values, the text items and blank cells are ignored.
Syntax
=DCOUNT(DatabaseRange,FieldName,CriteriaRange)
The DatabaseRange is the entire list of information you need to examine, including the
field names at the top of the columns.
The FieldName is the name, or cell, of the values to Count, such as "Value Of Stock" or I3.
The CriteriaRange is made up of two types of information.
The first set of information is the name, or names, of the Fields(s) to be used as the basis
for selecting the records, such as the category Brand or Wattage.
The second set of information is the actual record, or records, which are to be selected, such as Horizon as a brand name, or 100 as the wattage.
Formatting
No special formatting is needed.
29. DCOUNTA FUNCTION
What Does It Do?
This function examines a list of information and counts the non blank cells in a specified column.
It counts values and text items, but blank cells are ignored.
Syntax
=DCOUNTA(DatabaseRange,FieldName,CriteriaRange)
The DatabaseRange is the entire list of information you need to examine, including the
field names at the top of the columns.
The FieldName is the name, or cell, of the values to Count, such as "Value Of Stock" or I3.
The CriteriaRange is made up of two types of information.
The first set of information is the name, or names, of the Fields(s) to be used as the basis
for selecting the records, such as the category Brand or Wattage.
The second set of information is the actual record, or records, which are to be selected, such as Horizon as a brand name, or 100 as the wattage.
Formatting
No special formatting is needed.
30. DEC2BIN FUNCTION
What Does It Do?
This function converts a decimal number to its binary equivalent.
It can only cope with decimals ranging from -512 to 511.
The result can be padded with leading 0 zeros, although this is ignored for negatives.
Syntax
=DEC2BIN(DecimalNumber,PlacesToPad)
The PlacesToPad is optional.
Formatting
No special formatting is needed.
Comments
Post a Comment