A To Z of Excel Functions | Series 2
A To Z of Excel Functions - Series 2
31. DEC2HEX FUNCTION
What Does It Do ?
This function converts a decimal number to its hexadecimal equivalent.
It can only cope with decimals ranging from -549,755,813,888 to 549,755,813,887.
The result can be padded with leading 0 zeros, although this is ignored for negatives.
Syntax
=DEC2HEX(DecimalNumber,PlacesToPad)
The PlacesToPad is optional.
Formatting
No special formatting is needed.
32. DELTA FUNCTION
What Does It Do ?
This function compares two values and tests whether they are exactly the same.
If the numbers are the same the result will be 1, otherwise the result is 0.
It only works with numbers, text values produce a result of #VALUE.
The formatting of the number is not significant, so numbers which appear rounded due
to the removal of decimal places will still match correctly with non rounded values.
Syntax
=DELTA(FirstNumber,SecondNumber)
Formatting
No special formatting is needed.
Example
The following table is used to determine how may pairs of similar numbers are in a list.
The =DELTA() function tests each pair and then the =SUM() function totals them.
 |
| CLICK TO LEARN MORE & DOWNLOAD! |
33. DGET FUNCTION
What Does It Do?
This function examines a list of information and produces one result.
If more than one record matches the criteria the error #NUM is shown.
If no records match the criteria the error #VALUE is shown.
Syntax
=DGET(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 Get, 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 which needs to be selected, such
as Horizon as a brand name, or 100 as the wattage.
Formatting
No special formatting is needed.
34. DMAX FUNCTION
What Does It Do?
This function examines a list of information and produces the largest value from a specified column.
Syntax
=DMAX(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 pick the Max from, 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.
35. DMIN FUNCTION
What Does It Do ?
This function examines a list of information and produces smallest value from a specified column.
Syntax
=DMIN(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 pick the Min from, 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
or 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.
36. DOLLAR FUNCTION
What Does It Do?
This function converts a number into a piece of text formatted as currency.
Syntax
=DOLLAR(Number,DecimalPlaces)
Number: This is the number which needs to be converted.
DecimalPlaces: This is the amount of decimal places needed in the converted number.
Formatting
No special formatting is needed.
The result will be shown as a text entry.
37. DSUM FUNCTION
What Does It Do?
This function examines a list of information and produces the total.
Syntax
=DSUM(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 totalled, 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.
38. EDATE FUNCTION
What Does It Do?
This function is used to calculate a date which is a specific number of months in the past or
in the future.
Syntax
=EDATE(StartDate,Months)
Formatting
The result will normally be expressed as a number, this can be formatted to represent
a date by using the Format,Cells,Number,Date command.
Example
This example was used by a company hiring contract staff.
The company needed to know the end date of the employment.
The Start date is entered.
The contract Duration is entered as months.
The =EDATE() function has been used to calculate the end of the contract.
39. EOMONTH FUNCTION
What Does It Do?
This function will show the last day of the month which is a specified number of months
before or after a given date.
Syntax
=EOMONTH(StartDate,Months)
Formatting
The result will normally be expressed as a number, this can be formatted to represent
a date by using the Format,Cells,Number,Date command.
40. ERROR.TYPE FUNCTION
What Does It Do?
This function will show a number which corresponds to an error produced by a formula.
Syntax
=ERROR.TYPE(Error)
Error is the cell reference where the error occurred.
Formatting
The result will be formatted as a normal number.
41. EVEN FUNCTION
What Does It Do?
This function round a number up the nearest even whole number.
Syntax
=EVEN(Number)
Formatting
No special formatting is needed.
Example
The following table is used by a garage which repairs cars.
The garage is repairing a fleet of cars from three manufactures.
Each manufacturer uses a different type of windscreen wiper which are only supplied in pairs.
Table below was used to enter the number of wipers required for each type of car
and then show how many pairs need to be ordered.
42. EXACT FUNCTION
What Does It Do?
This function compares two items of text and determine whether they are exactly the same.
The case of the characters is taken into account, only words which are spelt the same and
which have upper and lower case characters in the same position will be considered as equal.
Syntax
=EXACT(Text1,Text2)
Only two items of text can be compared.
Formatting
If the two items of text are exactly the same the result of TRUE will be shown.
If there is any difference in the two items of text the result of FALSE will be shown.
43. FACT FUNCTION
What Does It Do?
This function calculates the factorial of a number.
The factorial is calculated as 1*2*3*4..etc.
The factorial of 5 is calculated as 1*2*3*4*5, which results in 120.
Decimal fractions of the number are ignored.
Syntax
=FACT(Number)
Formatting
No special formatting is required.
44. FIND FUNCTION
What Does It Do?
This function looks for a specified letter inside another piece of text.
When the letter is found the position is shown as a number.
If the text contains more than one reference to the letter, the first occurrence is used.
An additional option can be used to start the search at a specific point in the text, thus
enabling the search to find duplicate occurrences of the letter.
If the letter is not found in the text, the result #VALUE is shown.
Syntax
=FIND(LetterToLookFor,TextToLookInside,StartPosition)
LetterToLookFor: This needs to be a single character.
TextToLookInside: This is the piece of text to be searched through.
StartPosition: This is optional, it specifies at which point in the text the search should begin.
Formatting
No special formatting is needed, the result will be shown as a number.
45. FIXED FUNCTION
What Does It Do?
This function converts a numeric value to text.
During the conversion the value can be rounded to a specific number of decimal places,
and commas can be inserted at the 1,000's.
Syntax
=FIXED(NumberToConvert,DecimalPlaces,Commas)
If DecimalPlaces places is not specified the function will assume 2.
The Commas option can be TRUE for commas or FALSE for no commas.
If the Commas is not specified the function will assume TRUE.
Formatting
No special formatting is needed.
Note that any further formatting with the Format, Cells, Number command will not have any effect.
46. FLOOR FUNCTION
What Does It Do?
This function rounds a value down to the nearest multiple specified by the user.
Syntax
=FLOOR(NumberToRound,SignificantValue)
Formatting
No special formatting is needed.
Example
The following table was used to calculate commission for members of a sales team.
Commission is only paid for every £1000 of sales.
The =FLOOR() function has been used to round down the Actual Sales to the
nearest 1000, which is then used as the basis for Commission.
 |
| Learn More & Download Grading & Result App |
47. FORECAST FUNCTION
What Does It Do?
This function uses two sets of values to predict a single value.
The predicted value is based on the relationship between the two original sets of values.
If the values are sales figures for months 1 to 6, (Jan to Jun), you can use the function
to predict what the sales figure will be in any other month.
The way in which the prediction is calculated is based upon the assumption of a Linear Trend.
Syntax
=FORECAST(ItemToForeCast,RangeY,RangeX)
ItemToForecast is the point in the future, (or past), for which you need the forecast.
RangeY is the list of values which contain the historical data to be used as the basis
of the forecast, such as Sales figures.
RangeX is the intervals used when recording the historical data, such as Month number.
Formatting
No special formatting is needed.
48. FREQUENCY FUNCTION
What Does It Do?
This function compares a range of data against a list of intervals.
The result shows how many items in the range of data fall between the intervals.
The function is entered in the cells as an array, that is why it is enclosed in { } braces.
Syntax
=FREQUENCY(RangeOfData,ListOfIntervals)
Formatting
No special formatting is needed.
49. GCD FUNCTION
What Does It Do?
This function calculates the largest number which can be used to divided all the
values specified.
The result is always a whole number.
Where there is no common divisor the value of 1 is used.
Decimal fractions are ignored.
Syntax
=GCD(Number1,Number2,Number3... through to Number29)
Formatting
No special formatting is needed.
50. GESTEP FUNCTION
What Does It Do?
This function test a number to see if it is greater than or equal to another number.
If the number is greater than or equal, the result of 1 will be shown, otherwise 0 is shown.
Syntax
=GESTEP(NumberToTest,NumberToTestAgainst)
Formatting
No special formatting is needed.
51. HEX2DEC FUNCTION
What Does It Do?
This function converts a hexadecimal number to its decimal equivalent.
Syntax
=HEX2DEC(HexaDecimalNumber)
Formatting
No special formatting is needed.
52. HLOOKUP FUNCTION
What Does It Do?
This function scans across the column headings at the top of a table to find a specified item.
When the item is found, it then scans down the column to pick a cell entry.
Syntax
=HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted)
- The ItemToFind is a single item specified by the user.
- The RangeToLookIn is the range of data with the column headings at the top.
- The RowToPickFrom is how far down the column the function should look to pick from.
- The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.
Formatting
No special formatting is needed.
53. HOUR FUNCTION
What Does It Do?
The function will show the hour of the day based upon a time or a number.
Syntax
=HOUR(Number)
Formatting
The result will be shown as a normal number between 0 and 23.
54. IF FUNCTION
What Does It Do?
This function tests a condition.
If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.
Syntax
=IF(Condition,ActionIfTrue,ActionIfFalse)
The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.
Formatting
No special formatting is needed.
Example 1
The following table shows the Sales figures and Targets for sales reps.
Each has their own target which they must reach.
The =IF() function is used to compare the Sales with the Target.
If the Sales are greater than or equal to the Target the result of Achieved is shown.
If the Sales do not reach the target the result of Not Achieved is shown.
Note that the text used in the =IF() function needs to be placed in double quotes "Achieved".
55. INDEX FUNCTION
What Does It Do?
This function picks a value from a range of data by looking down a specified number
of rows and then across a specified number of columns.
It can be used with a single block of data, or non-continuos blocks.
Syntax
=INDEX(RangeToLookIn,Coordinate)
=INDEX(RangeToLookIn,RowCoordinate,ColumnColumnCordinate)
=INDEX(NamedRangeToLookIn,RowCoordinate,ColumnColumnCordinate,AreaToPickFrom)
Example
This is an extended version of the previous example.
It allows the names of products and the quarters to be entered.
The =MATCH() function is used to find the row and column positions of the names entered.
These positions are then used by the =INDEX() function to look for the data.
56. INDIRECT FUNCTION
What Does It Do?
This function converts a plain piece of text which looks like a cell address into a usable
cell reference.
The address can be either on the same worksheet or on a different worksheet.
Syntax
=INDIRECT(Text)
Formatting
The result will be shown as a number.
Example 1
This example shows how data can be picked form other worksheets by using
the worksheet name and a cell address.
The example uses three other worksheets named NORTH, SOUTH and EAST.
The data on these three sheets is laid out in the same cells on each sheet.
When a reference to a sheet is made the exclamation symbol ! needs to be placed
between the sheet name and cell address acting as punctuation.
The =INDIRECT() created a reference to =NORTH!C8
57. INFO FUNCTION
What Does It Do?
This function provides information about the operating environment of the computer.
Syntax
=INFO(text)
text : This is the name of the item you require information about.
Formatting
The results will be shown as text or a number depending upon what was requested.
58. INT FUNCTION
What Does It Do?
This function rounds a number down to the nearest whole number.
Syntax
=INT(Number)
Formatting
No special formatting is needed.
59. ISBLANK FUNCTION
What Does It Do?
This function will determine if there is an entry in a particular cell.
It can be used when a spreadsheet has blank cells which may cause errors, but which
will be filled later as the data is received by the user.
Usually the function is used in conjunction with the =IF() function which can test the result
of the =ISBLANK()
Syntax
=ISBLANK(CellToTest)
Formatting
Used by itself the result will be shown as TRUE or FALSE.
60. ISERR FUNCTION
What Does It Do?
This function tests a cell and shows TRUE if there is an error value in the cell.
It will show FALSE if the contents of the cell calculate without an error, or if the error
is the #NA message.
Syntax
=ISERR(CellToTest)
The CellToTest can be a cell reference or a calculation.
Formatting
No special formatting is needed.
👈Go To Series 1
👉Go To Series 3
👉Go To Series 4
Comments
Post a Comment