Description
Excel Utilities
Excel
Function
Dictionary
v1.0 Beta
Copyright © 1998 - 2001 Peter oneley
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Documentation
Page 2 of 206
!hat "# "n $he Dictionary %
This workbook contains 157 worksheets, each explaining the purpose and usage of
particular Excel functions.
There are also a number of sample worksheets which are simple models of common
applications, such as Timesheet and Date Calculations.
For&atting
Each worksheet uses the same type of formatting to indicate the various types of entry.
North Text headings are shown in grey.
100
100 Data is shown as purple text on a yellow background.
100
300 The results of Formula are shown as blue on yellow.
=SUM(C13:C15) The formula used in the calulations is shown as blue text.
The Arial font is used exclusivley throughout the workbook and should display correctly
with any installation of Windows.
Each sheet has been designed to be as simple as possible, with no fancy macros to
accomplish the desrired result.
Printing
Each worksheet is set to print on to A4 portrait.
The printouts will have the column headings of A,B,C... and the row numbers 1,2,3... which
will assist with the reading of the formula.
The ideal printer would be a laser set at 600dpi.
Ìf you are using a dot matrix or inkjet, it may be worth switching off the colours before printing,
as these will print as dark grey. (See the sheet dealing with Colour settings).
Protection
Each sheet is unprotected so that you will be able to change values and experiment
with the calculations.
'acro#
There are only a few very simple macros which are used by the various buttons to
naviagte through the sheets. These have been written very simply, and do not make any attempt
to change your current Toolbars and Menus.
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Ìnstructions
Page 3 of 206
!hat Do $he Button# Do %
View
View
This button will display the worksheet
containing the function example.
1. Click on the function name, then
2. Click on the (ie) button.
Sort
This button sorts the list of functions
into alphabetical order.
Sort
Category
This describes the category the
function is a member of.
Click this button to sort alphabetically.
Category
Location
This shows where the function is
stored in Excel.
Built-in indicates that the function
is part of Excel itself.
*naly#i# $oolPa+ indicates the
function is stored in the Analysis
ToolPak add-in.
Click this button to sort alphabetically.
Location
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Colours
Page 4 of 206
,#ing Di--erent 'onitor .etting#
Each sheet has been designed to fit within the visible width of monitors with a low resolution
of 640 x 480. This ensures that you do not need to scroll from left and right to see all the data.
The colours are best suited to monitors capable of 256 colours.
On monitors using just 16 colours the greys may look a bit rough!
You can switch colours off and on using the button below.
$hi# &ay ta+e a
-e) &inute# on
any co&puter /
Sample Colour Scheme
North South East West Total
Alan 100 100 100 100 400
Bob 100 100 100 100 400
Carol 100 100 100 100 400
Total 300 300 300 300 1200
Colour On
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Analysis ToolPak
Page 5 of 206
*naly#i# $oolPa+
!hat "# $he *naly#i# $oolPa+ %
The Analysis ToolPak is an add-in file containing
extra functions which are not built in to Excel.
The functions cover areas such as Date and
Mathematical operations.
The Analysis ToolPak must be added-in to Excel before
these functions will be available.
Any formula using these functions without the ToolPak loaded will show the #NAME error.
Check For Analysis ToolPak
Analysis ToolPak
Load the Analysis ToolPak
UnLoad the Analysis ToolPak
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FunctionList
Page 6 of 206
Age Calculation Sample Sample
AutoSum shortcut key Sample Sample
Brackets in formula Sample Sample Sample
FileName formula Sample Sample
Ìnstant Charts Sample Sample
Ordering Stock Sample Sample Stock Ordering
Percentages Sample Sample How to calculate various percentages
Project Dates Sample Sample Example using date calculation.
Show all formula Sample Sample
Split ForenameSurname Sample Sample
Time Calculation Sample Sample How to calculate time.
TimeSheet For Flexi Sample Sample Example flexi time sheet.
ABS Mathematical Built-in Returns the absolute value of a number
AND Logical Built-in Returns TRUE if all its arguments are TRUE
AVERAGE Statistical Built-in Returns the average of its arguments
BÌN2DEC Engineering Analysis ToolPak Converts a binary number to decimal
CEÌLÌNG Mathematical Built-in Rounds a number to the nearest integer or to the nearest multiple of significance
CELL Ìnformation Built-in Returns information about the formatting, location, or contents of a cell
CHAR Text Built-in Returns the character specified by the code number
CHOOSE Lookup Built-in Chooses a value from a list of values
CLEAN Text Built-in Removes all nonprintable characters from text
CODE Text Built-in Returns a numeric code for the first character in a text string
COMBÌN Mathematical Built-in Returns the number of combinations for a given number of objects
CONCATENATE Text Built-in Joins several text items into one text item
CONVERT Engineering Analysis ToolPak Converts a number from one measurement system to another
CORREL Statistical Built-in Returns the correlation coefficient between two data sets
COUNT Statistical Built-in Counts how many numbers are in the list of arguments
COUNTA Statistical Built-in Counts how many values are in the list of arguments
COUNTBLANK Ìnformation Built-in Counts the number of blank cells within a range
COUNTÌF Mathematical Built-in Counts the number of nonblank cells within a range that meet the given criteria
DATE Date Built-in Returns the serial number of a particular date
DATEDÌF Date Built-in Calculates the difference between two dates. Undocumented in v5/7/97
DATEVALUE Date Built-in Converts a date in the form of text to a serial number
DAVERAGE Database Built-in Returns the average of selected database entries
DAY Date Built-in Converts a serial number to a day of the month
DAYS360 Date Built-in Calculates the number of days between two dates based on a 360-day year
DB Financial Built-in Returns the depreciation of an asset for a specified period using the fixed-declining balance method
DCOUNT Database Built-in Counts the cells that contain numbers in a database
DCOUNTA Database Built-in Counts nonblank cells in a database
DEC2BÌN Engineering Analysis ToolPak Converts a decimal number to binary
DEC2HEX Engineering Analysis ToolPak Converts a decimal number to hexadecimal
DELTA Engineering Analysis ToolPak Tests whether two values are equal
DGET Database Built-in Extracts from a database a single record that matches the specified criteria
DMAX Database Built-in Returns the maximum value from selected database entries
DMÌN Database Built-in Returns the minimum value from selected database entries
DOLLAR Text Built-in Converts a number to text, using currency format
DSUM Database Built-in Adds the numbers in the field column of records in the database that match the criteria
EDATE Date Analysis ToolPak Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH Date Analysis ToolPak Returns the serial number of the last day of the month before or after a specified number of months
ERROR.TYPE Ìnformation Built-in Returns a number corresponding to an error type
EVEN Mathematical Built-in Rounds a number up to the nearest even integer
EXACT Text Built-in Checks to see if two text values are identical
FACT Mathematical Built-in Returns the factorial of a number
FÌND Text Built-in Finds one text value within another (case-sensitive)
FÌXED Text Built-in Formats a number as text with a fixed number of decimals
FLOOR Mathematical Built-in Rounds a number down, toward zero
FORECAST Statistical Built-in Returns a value along a linear trend
FREQUENCY Statistical Built-in Returns a frequency distribution as a vertical array
GCD Mathematical Analysis ToolPak Returns the greatest common divisor
GESTEP Engineering Analysis ToolPak Tests whether a number is greater than a threshold value
GROWTH Statistical Built-in Returns values along an exponential trend
HEX2DEC Engineering Analysis ToolPak Converts a hexadecimal number to decimal
HLOOKUP Lookup Built-in Looks in the top row of an array and returns the value of the indicated cell
HOUR Date Built-in Converts a serial number to an hour
ÌF Logical Built-in Specifies a logical test to perform
ÌNDEX Lookup Built-in Uses an index to choose a value from a reference or array
ÌNDÌRECT Lookup Built-in Returns a reference indicated by a text value
ÌNFO Ìnformation Built-in Returns information about the current operating environment
ÌNT Mathematical Built-in Rounds a number down to the nearest integer
ÌSBLANK Ìnformation Built-in Returns TRUE if the value is blank
ÌSERR Ìnformation Built-in Returns TRUE if the value is any error value except #N/A
ÌSERROR Ìnformation Built-in Returns TRUE if the value is any error value
ÌSEVEN Ìnformation Analysis ToolPak Returns TRUE if the number is even
ÌSLOGÌCAL Ìnformation Built-in Returns TRUE if the value is a logical value
ÌSNA Ìnformation Built-in Returns TRUE if the value is the #N/A error value
ÌSNONTEXT Ìnformation Built-in Returns TRUE if the value is not text
ÌSNUMBER Ìnformation Built-in Returns TRUE if the value is a number
ÌSODD Ìnformation Analysis ToolPak Returns TRUE if the number is odd
ÌSREF Ìnformation Built-in Returns TRUE if the value is a reference
ÌSTEXT Ìnformation Built-in Returns TRUE if the value is text
LARGE Statistical Built-in Returns the k-th largest value in a data set
LCM Mathematical Analysis ToolPak Returns the least common multiple
LEFT Text Built-in Returns the leftmost characters from a text value
LEN Text Built-in Returns the number of characters in a text string
LOOKUP (vector) Lookup Built-in Looks up values in a vector or array
LOWER Text Built-in Converts text to lowercase
Using DATEDÌF()
Using Alt and =
Using MÌD() CELL() and FÌND()
Using F11
Using Ctrl and `
Using LEFT() RÌGHT() FÌND() SUBSTÌTUTE()
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FunctionList
Page 7 of 206
MATCH Lookup Built-in Looks up values in a reference or array
MAX Statistical Built-in Returns the maximum value in a list of arguments
MEDÌAN Statistical Built-in Returns the median of the given numbers
MÌD Text Built-in Returns a specific number of characters from a text string starting at the position you specify
MÌN Statistical Built-in Returns the minimum value in a list of arguments
MÌNUTE Date Built-in Converts a serial number to a minute
MÌNVERSE Mathematical Built-in Returns the matrix inverse of an array
MMULT Mathematical Built-in Returns the matrix product of two arrays
MOD Mathematical Built-in Returns the remainder from division
MODE Statistical Built-in Returns the most common value in a data set
MONTH Date Built-in Converts a serial number to a month
MROUND Mathematical Analysis ToolPak Returns a number rounded to the desired multiple
N Ìnformation Built-in Returns a value converted to a number
NA Ìnformation Built-in Returns the error value #N/A
NETWORKDAYS Date Analysis ToolPak Returns the number of whole workdays between two dates
NOT Logical Built-in Reverses the logic of its argument
NOW Date Built-in Returns the serial number of the current date and time
ODD Mathematical Built-in Rounds a number up to the nearest odd integer
OR Logical Built-in Returns TRUE if any argument is TRUE
PERMUT Statistical Built-in Returns the number of permutations for a given number of objects
PÌ Mathematical Built-in Returns the value of Pi
POWER Mathematical Built-in Returns the result of a number raised to a power
PRODUCT Mathematical Built-in Multiplies its arguments
PROPER Text Built-in Capitalises the first letter in each word of a text value
QUARTÌLE Statistical Built-in Returns the quartile of a data set
QUOTÌENT Mathematical Analysis ToolPak Returns the integer portion of a division
RAND Mathematical Built-in Returns a random number between 0 and 1
RANDBETWEEN Mathematical Analysis ToolPak Returns a random number between the numbers you specify
RANK Statistical Built-in Returns the rank of a number in a list of numbers
REPLACE Text Built-in Replaces characters within text
REPT Text Built-in Repeats text a given number of times
RÌGHT Text Built-in Returns the rightmost characters from a text value
ROMAN Mathematical Built-in Converts an arabic numeral to roman, as text
ROUND Mathematical Built-in Rounds a number to a specified number of digits
ROUNDDOWN Mathematical Built-in Rounds a number down, toward zero
ROUNDUP Mathematical Built-in Rounds a number up, away from zero
SECOND Date Built-in Converts a serial number to a second
SÌGN Mathematical Built-in Returns the sign of a number
SLN Financial Built-in Returns the straight-line depreciation of an asset for one period
SMALL Statistical Built-in Returns the k-th smallest value in a data set
STDEV Statistical Built-in Estimates standard deviation based on a sample
STDEVP Statistical Built-in Calculates standard deviation based on the entire population
SUBSTÌTUTE Text Built-in Substitutes new text for old text in a text string
SUBTOTAL Mathematical Built-in Returns a subtotal in a list or database
SUM Mathematical Built-in Adds its arguments
SUM_as_Running_Total Mathematical Built-in Sample
SUM_using_names Sample Sample
SUM_with_OFFSET Lookup Built-in Sample
SUMÌF Mathematical Built-in Adds the cells specified by a given criteria
SUMPRODUCT Mathematical Built-in Returns the sum of the products of corresponding array components
SYD Financial Built-in Returns the sum-of-years' digits depreciation of an asset for a specified period
T Text Built-in Converts its arguments to text
TEXT Text Built-in Formats a number and converts it to text
TÌME Date Built-in Returns the serial number of a particular time
-Timesheet Sample Sample Sample
TÌMEVALUE Date Built-in Converts a time in the form of text to a serial number
TODAY Date Built-in Returns the serial number of today's date
TRANSPOSE Lookup Built-in Returns the transpose of an array
TREND Statistical Built-in Returns values along a linear trend
TRÌM Text Built-in Removes spaces from text
TRUNC Mathematical Built-in Truncates a number to an integer
TYPE Ìnformation Built-in Returns a number indicating the data type of a value
UPPER Text Built-in Converts text to uppercase
VALUE Text Built-in Converts a text argument to a number
VAR Statistical Built-in Estimates variance based on a sample
VARP Statistical Built-in Calculates variance based on the entire population
VLOOKUP Lookup Built-in Looks in the first column of an array and moves across the row to return the value of a cell
WEEKDAY Date Built-in Converts a serial number to a day of the week
WORKDAY Date Analysis ToolPak Returns the serial number of the date before or after a specified number of workdays
YEAR Date Built-in Converts a serial number to a year
YEARFRAC Date Analysis ToolPak Returns the year fraction representing the number of whole days between start_date and end_date
Using SUM(jan)
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Time Calculation
Page 8 of 206
$i&e Calculation
Excel can work with time very easily.
Time can be entered in various different formats and calculations performed.
There are one or two oddities, but nothing which should put you off working with it.
$yping ti&e
When time is entered into worksheet it should be entered with a colon between
1:30 12:30 20:15 22:45
Excel can cope with either the 24hour system or the am/pm system.
You must leave a space between the number and the text.
1:30 AM 1:30 PM 10:15 AM 10:15 PM
Fin0ing the 0i--erence 1et)een t)o ti&e#
You can subtract two time values to find the length of time between.
Start End Duration
1:30 2:30 1:00 =D24-C24
8:00 17:00 9:00 =D25-C25
8:00 AM 5:00 PM 9:00 AM Ìf the result is not shown correctly,
You may need to reformat the answer.
Look at the section about formatting
further in this worksheet.
*00ing ti&e
You can add time to find a total time.
This works well until the total time goes above 24 hours.
For totals greater than 24 hours you may need to apply some special formatting.
Start End Duration
1:30 2:30 1:00
8:00 17:00 9:00
7:30 AM 5:45 PM 10:15
20:15
For&atting ti&e
When time is added together the result may go beyond 24 hours.
Usually this gives an incorrect result, as in the example below.
To correct this error, the result needs to be formatted with a Custom format.
Exa&ple 1 2 "ncorrect -or&atting
Start End Duration
7:00 18:30 11:30
8:00 17:00 9:00
7:30 17:45 10:15
Total 6:45 =SUM(E49:E51)
Exa&ple 2 2 Correct -or&atting
Start End Duration
7:00 18:30 11:30
8:00 17:00 9:00
7:30 17:45 10:15
Total 30:45 =SUM(E56:E58)
3o) $o *pply Cu#to& For&atting
The custom format for time use a pair of square brackets [hh] on either side
of the hours indicators.
1. Click on the cell which needs the format.
See the $i&e.heet example for an example.
the hour and the minutes, such as 12240, rather than 12.40
To use the am/pm system you must enter the a& or p& after the time.
2. Choose the For&at menu.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Time Calculation
Page 9 of 206
3. Choose Cell#.
4. Click the u&1er tag at the top right.
5. Choose Cu#to&.
6. Click inside the $ype: box.
7. Type 5hh62&& as the format.
8. Click 78 to confirm.
A B C D E F G H I J
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TimeSheet For Flexi
Page 10 of 206
$i&e.heet -or Flexi
Week beginning Mon 05-Jan-98 Normal Hours 37:30
Day Arrive Lunch Out Lunch Ìn Depart Total
Mon 05 8:00 13:00 14:00 17:00 8:00 =(F6-C6)-(E6-D6)
Tue 06 8:45 12:30 13:30 17:00 7:15
Wed 07 9:00 13:00 14:00 18:00 8:00
Thu 08 8:30 13:00 14:00 17:00 7:30
Fri 09 8:00 12:00 13:00 17:00 8:00
Total Hours 38:45 =SUM(G6:G10)
Under worked by - =ÌF(G3-G11>0,G3-G11, "-")
Over worked by 1:15 =ÌF(G3-G11 87 W 112 p 137 ? 162 ? 187 ? 212 ? 237 ?
13 38 & 63 ? 88 X 113 q 138 ? 163 ? 188 ? 213 ? 238 ?
14 39 ' 64 @ 89 Y 114 r 139 ? 164 ? 189 ? 214 ? 239 ?
15 40 ( 65 A 90 Z 115 s 140 ? 165 190 ! 215 " 240
16 41 ) 66 B 91 [ 116 t 141 ? 166 # 191 ? 216 ? 241
17 42 * 67 C 92 \ 117 u 142 ? 167 $ 192 ? 217 ? 242
18 43 + 68 D 93 ] 118 v 143 ? 168 % 193 ? 218 & 243
19 44 , 69 E 94 ^ 119 w 144 169 ? 194 ' 219 ( 244
20 45 - 70 F 95 _ 120 x 145 ! 170 ) 195 * 220 + 245
21 46 . 71 G 96 ` 121 y 146 " 171 , 196 - 221 ? 246
22 47 / 72 H 97 a 122 z 147 # 172 . 197 / 222 ? 247
23 48 0 73 Ì 98 b 123 { 148 $ 173 0 198 1 223 ? 248
24 49 1 74 J 99 c 124 | 149 % 174 2 199 3 224 & 249 ?
25 50 2 75 K 100 d 125 } 150 ' 175 4 200 ? 225 ( 250 ?
ote
Number 32 does not show as it is the SPACEBAR character.
A B C D E F G H I J K L M N O P Q R S T U ! "
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
CHOOSE
Page 32 of 206
C377.E
Result
1 Alan =CHOOSE(C4,"Alan","Bob","Carol")
3 Carol =CHOOSE(C5,"Alan","Bob","Carol")
2 Bob =CHOOSE(C6,"Alan","Bob","Carol")
3 18% =CHOOSE(C7,10%,15%,18%)
1 10% =CHOOSE(C8,10%,15%,18%)
2 15% =CHOOSE(C9,10%,15%,18%)
!hat Doe# "t Do%
This function picks from a list of options based upon an Ìndex value given to by the user.
.yntax
=CHOOSE(UserValue, Ìtem1, Ìtem2, Ìtem3 through to Ìtem29)
For&atting
No special formatting is required.
Exa&ple
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 =ÌF() 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.
Name Time Position Medal
Alan 1:30 2 Silver =ÌF(D30=D43,C43*10%,C43*5%)
Bob 6000 5000 600 =ÌF(C44>=D44,C44*10%,C44*5%)
Carol 2000 4000 100 =ÌF(C45>=D45,C45*10%,C45*5%)
Exa&ple 4
This example uses the =AND() within the =ÌF() function.
A builders merchant gives 10% discount on certain product lines.
The discount is only given on products which are on Special Offer, when the Order Value
is £1000 or above.
the value of the order is above £1000.
Special Order
Product Offer Value Discount Total
Wood Yes £ 2,000 £ 200 £ 1,800
The =AND() function is used with the =ÌF() to check that the product is on offer an0 that
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
ÌF
Page 94 of 206
Glass No £ 2,000 £ - £ 2,000
Cement Yes £ 500 £ - £ 500
Turf Yes £ 3,000 £ 300 £ 2,700
=ÌF(AND(C61="Yes",D61>=1000),D61*10%,0)
A B C D E F G H I J
59
60
61
62
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
ÌNDEX
Page 95 of 206
"DEP
Holiday booking price list.
People
Weeks 1 2 3 4
1 £500 £300 £250 £200
2 £600 £400 £300 £250
3 £700 £500 £350 £300
How many weeks required : 2
How many people in the party : 4
Cost per person is : 250 =ÌNDEX(D7:G9,G11,G12)
!hat Doe# "t 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.
Ìt can be used with a single block of data, or non-continuos blocks.
.yntax
There are various forms of syntax for this function.
.yntax 1
=ÌNDEX(RangeToLookÌn,Coordinate)
This is used when the RangeToLookÌn is either a single column or row.
The Co-ordinate indicates how far down or across to look when picking the data from the range.
Both of the examples below use the same syntax, but the Co-ordinate refers to a row when
the range is vertical and a column when the range is horizontal.
Colours
Red
Green
Blue Size Large Medium Small
Type either 1, 2 or 3 : 2 Type either 1, 2 or 3 : 2
The colour is : Green The size is : Medium
=ÌNDEX(D32
34,D36) =ÌNDEX(G34:Ì34,H36)
.yntax 2
=ÌNDEX(RangeToLookÌn,RowCoordinate,ColumnColumnCordinate)
This syntax is used when the range is made up of rows and columns.
Country Currency Population Capitol
England Sterling 50 M London
France Franc 40 M Paris
Germany DM 60 M Bonn
Spain Peseta 30 M Barcelona
Type 1,2,3 or 4 for the country : 2
Type 1,2 or 3 for statistics : 3
The result is : Paris =ÌNDEX(D45:F48,F50,F51)
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
ÌNDEX
Page 96 of 206
.yntax 4
=ÌNDEX(NamedRangeToLookÌn,RowCoordinate,ColumnColumnCordinate,AreaToPickFrom)
Using this syntax the range to look in can be made up of multiple areas.
The easiest way to refer to these areas is to select them and give them a single name.
The AreaToPickFrom indicates which of the multiple areas should be used.
Ìn the following example the figures for North and South have been named as one
range called NorthAndSouth.
7H$3 Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,000 £2,000 £3,000 £4,000
Wood £5,000 £6,000 £7,000 £8,000
Glass £9,000 £10,000 £11,000 £12,000
.7,$3 Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,500 £2,500 £3,500 £4,500
Wood £5,500 £6,500 £7,500 £8,500
Glass £9,500 £10,500 £11,500 £12,500
Type 1, 2 or 3 for the product : 1
Type 1, 2, 3 or 4 for the Qtr : 3
Type 1 for North or 2 for South : 2
The result is : Err:504 =ÌNDEX(NorthAndSouth,F76,F77,F78)
Exa&ple
This is an extended version of the previous example.
Ìt 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 =ÌNDEX() function to look for the data.
E*.$ :tr1 :tr2 :tr4 :tr=
Bric+# £1,000 £2,000 £3,000 £4,000
!oo0 £5,000 £6,000 £7,000 £8,000
Ila## £9,000 £10,000 £11,000 £12,000
!E.$ Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,500 £2,500 £3,500 £4,500
Wood £5,500 £6,500 £7,500 £8,500
Glass £9,500 £10,500 £11,500 £12,500
Type 1, 2 or 3 for the product : )oo0
Type 1, 2, 3 or 4 for the Qtr : =0 but =100")
Total of item typed in following cell. service 450 =SUMÌF(C4:C12,E18,E4:E12)
!hat Doe# "t Do %
This function adds the value of items which match criteria set by the user.
.yntax
=SUMÌF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)
=SUMÌF(C4:C12,"Brakes",E4:E12) This examines the names of products in C4:C12.
Ìt then identifies the entries for Brakes.
Ìt then totals the respective figures in E4:E12
=SUMÌF(E4:E12,">=100") This examines the values in E4:E12.
Ìf the value is >=100 the value is added to the total.
For&atting
No special formatting is needed.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
SUMPRODUCT
Page 182 of 206
.,'PH7D,C$
Ìtem Sold price
Tyres 5 100
Filters 2 10
Bulbs 3 2
Total Sales Value : 526 =SUMPRODUCT(D46,E4:E6)
!hat Doe# "t Do %
This function uses at least two columns of values.
The values in the first column are multipled with the corresponding value in the second column.
The total of all the values is the result of the calculation.
.yntax
=SUMPRODUCT(Range1, Range, Range3 through to Range30)
For&atting
No special formatting is needed.
Exa&ple
The following table was used by a drinks merchant to keep track of stock.
The merchant needed to know the total purchase value of the stock, and the potential
value of the stock when it is sold, takinging into account the markup percentage.
The =SUMPRODUCT() function is used to multiply the Cases Ìn Stock with the Case Price to
calculate what the merchant spent in buying the stock.
The =SUMPRODUCT() function is used to multiply the Cases Ìn Stock with
the Bottles Ìn Case and the Bottle Setting Price, to calculate the potential value of the
stock if it is all sold.
Product Markup
Red Wine 10 £120 10 £12.00 25% £15.00
White Wine 8 £130 10 £13.00 25% £16.25
Champagne 5 £200 6 £33.33 80% £60.00
Beer 50 £24 12 £2.00 20% £2.40
Lager 100 £30 12 £2.50 25% £3.13
=D39/E39 =F39+F39*G39
Total Value Of Stock : £7,440 =SUMPRODUCT(C35:C39,D3539)
Total Selling Price Of Stock : £9,790 =SUMPRODUCT(C35:C39,E35:E39,H35:H39)
Profit : £2,350 =E44-E43
Cases Ìn
Stock
Case
Price
Bottles
Ìn Case
Bottle
Cost
Bottle Selling
Price
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
SYD
Page 183 of 206
.ND
Purchase Value Of A New Car £20,000
Second Hand Value £8,000
Number Of Years Ownership 6
Deprecation in year 1 £3,429 =SYD(F3,F4,F5,1)
Deprecation in year 2 £2,857 =SYD(F3,F4,F5,2)
Deprecation in year 3 £2,286 =SYD(F3,F4,F5,3)
Deprecation in year 4 £1,714 =SYD(F3,F4,F5,4)
Deprecation in year 5 £1,143 =SYD(F3,F4,F5,5)
Deprecation in year 6 £571 =SYD(F3,F4,F5,6)
Total Depreciation : £12,000 =SUM(F7:F12)
!hat Doe# "t Do %
This function calculates the depreciation of an item throughout its life, using the sum of the
years digits.
The depreciation is greatest in the earlier part of the items life.
!hat i# the .u& 7- $he Near# Digit# %
The sum of the years digits adds together the each of the years of the life.
A life of 3 years has a sum of 1+2+3 equalling 6.
Each of the years is then calculated as a percentage of the sum of the years.
Year 3 is 50% of 6, year 2 is 33% of 6, year 1 is 17% 6.
The total depreciation of the item is then allocated on the basis of these percentages.
A depreciation of £9000 is allocated as 50% being £4500, 33% being £3000, 17% being £1500.
£9,000
1 17% £1,500
2 33% £3,000
3 50% £4,500
As the greater part of the depreciation is allocated to the earliest years the values are
inverted, year 1 is $4500, year 2 is £3000 and year 1 is £1500.
Exa&ple 1
Purchase Price Of A Car : £10,000
Salvage Value : £1,000
Expected Life in Years : 3
As % Of Total Depreciation
Depreciation in Year 1 : £4,500 ===> 0.5
Depreciation in Year 2 : £3,000 ===> 0.333333
Depreciation in Year 3 : £1,500 ===> 0.166667
=SYD(E39,E40,E41,3)
1. Add together the digits of the Life to get the SumOfTheYearsDigits, 1+2+3=6.
2. Subtract the Salvage from the Purchase Price to get Total Deprectation, £10000-£1000=£9000.
3. Divide the Total Deprectation by the SumOfTheYearsDigits, £9000/6=£1500.
4. Ìnvert the year digits, 1,2,3 becomes 3,2,1.
5. Multiply 3,2,1 by £1500 to get £4500, £3000, £1500, these values are the depreciation
values for each of the three years in the life of the item.
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
SYD
Page 184 of 206
Exa&ple 2
The same example using 4 years.
Purchase Price Of A Car : £10,000
Salvage Value : £1,000
Expected Life in Years : 4
As % Of Total Depriciation
Depreciation in Year 1 : £3,600 0.4
Depreciation in Year 2 : £2,700 0.3
Depreciation in Year 3 : £1,800 0.2
Depreciation in Year 4 : £900 0.1
Total Depreciation : £9,000 100%
Exa&ple 4
This example will adjust itself to accommodate any number of years between 1 and 10.
Purchase Price Of A Car : £10,000
Salvage Value : £1,000
Expected Life in Years (1 to 10) : 7
As % Of Total Depriciation
Year 1 £2,250 25%
Year 2 £1,929 21%
Year 3 £1,607 18%
Year 4 £1,286 14%
Year 5 £964 11%
Year 6 £643 7%
Year 7 £321 4%
Year
Year
Year
£9,000 100%
.yntax
=SYD(OriginalCost,SalvageValue,Life,PeriodToCalculate)
For&atting
No special formatting is needed.
A B C D E F G H I
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
T
Page 185 of 206
$
Cell To Test Result
Hello Hello =T(D4)
10 =T(D5)
1-Jan-98 =T(D6)
=T(D7)
!hat Doe# "t Do %
This function examines an entry to determine whether it is text or not.
Ìf the value is text, then the text is the result of the function
Ìf the value is not text, the result is a blank.
The function is not specifically needed by Excel, but is included for compatibility with
other spreadsheet programs.
.yntax
=T(CellToTest)
For&atting
No special formatting is needed.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TEXT
Page 186 of 206
$EP$
10 10.00 =TEXT(C4,"0.00")
10 £10.00 =TEXT(C5,"£0.00")
10 10 =TEXT(C6,"0")
10 £10 =TEXT(C7,"£0")
10.25 10.3 =TEXT(C8,"0.0")
10.25 £10.3 =TEXT(C9,"£0.0")
!hat Doe# "t Do %
This function converts a number to a piece of text.
The formatting for the text needs to be specified in the function.
.yntax
=TEXT(NumberToConvert,FormatForConversion)
For&atting
No special formatting is required.
Original
Number
Converted
To Text
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TÌME
Page 187 of 206
$"'E
Hour Minute Second Time
14 30 59 14:30:59 =TÌME(C4,D4,E4)
14 30 59 2:30:59 PM =TÌME(C5,D5,E5)
14 30 59 0.60485 =TÌME(C6,D6,E6)
!hat Doe# "t Do%
This function will convert three separate numbers to an actual time.
.yntax
=TÌME(Hour,Minute,Second)
For&atting
The result will be shown as a time which can be formatted either as 12 or 24 hour style.
Ìf a normal number format is applied a decimal fraction is shown which represents the
time as a fraction of the day.
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TÌMEVALUE
Page 188 of 206
$"'E(*9,E
Text Time
14:30:59 0.604849537 =TÌMEVALUE(C4)
14:30:59 14:30:59 =TÌMEVALUE(C5)
14:30:59 2:30:59 PM =TÌMEVALUE(C6)
!hat Doe# "t Do%
This function will show an actual time based on a piece of text which looks
like a time. Ìt is useful when data is imported from other applications, such as
from mainframe computers, which convert all values to text.
.yntax
=TÌMEVALUE(Text)
For&atting
The result will be shown as a number representing the time a fraction of the day.
Formatting can be applied for either the 12 or 24 hour clock system.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TODAY
Page 189 of 206
$7D*N
Today Ìs
25-Jun-15 =TODAY()
!hat Doe# "t Do%
Use this to show the current date.
.yntax
=TODAY()
For&atting
The result will normally be displayed using the DD-MMM-YY format.
Exa&ple
The following example shows how the Today function is used to calculate the number
of days since a particular day.
Date Days Since
1-Jan-97 6749 =TODAY()-C20
10-Aug-97 6528 =TODAY()-C21
Note that the result is actually the number of days before todays date. To calculate
a result which includes the current date an extra 1 will need to be added.
Date Days Since
1-Jan-97 6750 =TODAY()-C28+1
10-Aug-97 6529 =TODAY()-C29+1
Exa&ple
The following example shows the number of days from today until the year 2000.
Year 2000 Days Until
01-Jan-2000 -5654 =C36-TODAY()
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TRANSPOSE
Page 190 of 206
$H*.P7.E
Jan Feb
Alan 10 30
Bob 40 50
Carol 70 80
Total 120 160
Alan Bob Carol Total
Jan 10 40 70 120
Feb 30 50 80 160
{=TRANSPOSE(C3:E7)}
As an array formula in all these cells
!hat Doe# "t Do %
This function copies data from a range, and places in it in a new range, turning it so
that the data originally in columns is now in rows, and the data originally in rows
is in columns.
The transpose range must be the same size as the original range.
The function needs to be entered as an array formula.
To enter an array formula you must first highlight all the cells where the formula is required.
Next type the formula, such as =TRANSPOSE(A1:A5).
Finally press Ctrl+Shift+Enter to confirm it.
Ìf changes need to be made to the formula, the entire array has to be highlighted, the edits
can then be made and the Ctrl+Shift+Enter used to confirm it.
.yntax
=TRANSPOSE(Range)
For&atting
No special formatting is needed.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TREND
Page 191 of 206
$HED !3*$ ". C7.$ 1 %
Historical Data Predicted Values
Month Sales Month Sales
1 £1,000 7 £4,940 {=TREND(C8:C13,B8:B13,E8:E13)}
2 £2,000 8 £5,551 {=TREND(C5:C10,B5:B10,E5:E10)}
3 £2,500 9 £6,163 {=TREND(C5:C10,B5:B10,E5:E10)}
4 £3,500 10 £6,774 {=TREND(C5:C10,B5:B10,E5:E10)}
5 £3,800 11 £7,386 {=TREND(C5:C10,B5:B10,E5:E10)}
6 £4,000 12 £7,997 {=TREND(C5:C10,B5:B10,E5:E10)}
!hat Doe# "t Do %
This function predicts values based upon three sets of related values.
The prediction is based upon the Linear Trend of the original values.
The function is an array function and must be entered using Ctrl+Shift+Enter.
.yntax
=TREND(KnownYs,KnownXs,RequiredXs,Constant)
The KnownYs is the range of values, such as Sales Figures.
The KnownXs is the intervals used when collecting the data, such as Months.
The RequiredXs is the range for which you want to make the prediction, such as Months.
For&atting
No special formatting is needed.
Exa&ple
The following tables were used by a company to predict when they would start to
make a profit.
Their bank manager had told the company that unless they could show a profit by the
end of the next year, the bank would no longer provide an overdraft facility.
To prove to the bank that, based upon the past years performance, the company would
start to make a profit at the end of the next year, the =TREND() function was used.
The historical data for the past year was entered, months 1 to 12.
The months to predict were entered, 13 to 24.
The =TREND() function shows that it will be month 22 before the company make a profit.
Historical Data Predicted Values
Month Profit Month Profit
1 -£5,000 13 -£2,226 {=TREND(C41:C52,B41:B52,E41:E52)}
2 -£4,800 14 -£1,968 The
3 -£4,600 15 -£1,709 same
4 -£4,750 16 -£1,451 !unction
5 -£4,800 17 -£1,193 used
6 -£4,500 18 -£935 in
7 -£4,000 19 -£676 all
8 -£3,800 20 -£418 cells
9 -£3,300 21 -£160 as
10 -£2,000 22 £98 an
11 -£2,500 23 £356 array
12 -£2,800 24 £615 !ormula
3o) $o Enter *n *rray For&ula
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TREND
Page 192 of 206
Select all the cells where the array is required, such as F41 to F52.
Type the formula such as =TREND(C41:C52,B41:B52,E41:E52), but do not press Enter.
Hold the Ctrl+Shift keys down.
Press Enter to enter the formula as an array.
A B C D E F G H I J
55
56
57
58
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TRÌM
Page 193 of 206
$H"'
Original Text Trimmed Text
ABCD ABCD =TRÌM(C4)
A B C D A B C D =TRÌM(C5)
Alan Jones Alan Jones =TRÌM(C6)
ABCD ABCD =TRÌM(C7)
!hat Doe# "t Do %
This function removes unwanted spaces from a piece of text.
The spaces before and after the text will be removed completely.
Multiple spaces within the text will be trimmed to a single space
.yntax
=TRÌM(TextToTrim)
For&atting
No special formatting is needed.
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TRUNC
Page 194 of 206
$H,C
Number
1.47589 0 1 =TRUNC(C4,D4)
1.47589 1 1.4 =TRUNC(C5,D5)
1.47589 2 1.47 =TRUNC(C6,D6)
-1.47589 1 -1.4 =TRUNC(C7,D7)
-1.47589 2 -1.47 =TRUNC(C8,D8)
13643.48 -1 13640 =TRUNC(C9,D9)
13643.48 -2 13600 =TRUNC(C10,D10)
13643.48 -3 13000 =TRUNC(C11,D11)
!hat Doe# "t Do %
This function removes the decimal part of a number, it does not actually round the number.
.yntax
=TRUNC(NumberToTuncate,Precision)
For&atting
No special formatting is needed.
Precision
For
Truncation
Truncated
Number
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
UPPER
Page 195 of 206
,PPEH
Original Text Upper Case
alan jones ALAN JONES =UPPER(C4)
bob smith BOB SMÌTH =UPPER(C5)
carOl wiLLiamS CAROL WÌLLÌAMS =UPPER(C6)
cardiff CARDÌFF =UPPER(C7)
abc123 ABC123 =UPPER(C8)
!hat Doe# "t Do %
This function converts all characters in a piece of text to upper case.
.yntax
=UPPER(TextToConvert)
For&atting
No special formatting is needed.
Exa&ple
See the example for FREQUENCY.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VALUE
Page 196 of 206
(*9,E
Text Containing A Number Value
Annual turnover was £5000 Err:502 =VALUE(MÌD(C4,SEARCH("£",C4),99))
There was a 2% increase in sales. #VALUE!
There was a 50% increase in sales. #VALUE!
A 100% increase was achieved. #VALUE!
Only a 2% increase in sales. #VALUE!
Approx 50% increase in sales. #VALUE!
There was a 100% increase in sales. #VALUE! * See explanation below.
The winning time was 1:30 seconds. #VALUE! =VALUE(MÌD(C14,SEARCH("??:??",C14),5))
The winning time was 1:30 seconds. #VALUE! =VALUE(MÌD(C15,SEARCH("??:??",C15),5))
The winning time was 10:30 seconds. #VALUE! =VALUE(MÌD(C16,SEARCH("??:??",C16),5))
The winning time was 0:30 seconds. #VALUE! =VALUE(MÌD(C17,SEARCH("??:??",C17),5))
!hat Doe# "t Do %
This function converts a piece of text which resembles a number into an actual value.
Ìf the number in the middle of a long piece of text it will have to be extracted using other
text functions such as =SEARCH(), =MÌD(), =FÌND(), =SUBSTÌTUTE, =LEFT() or =RÌGHT().
.yntax
=VALUE(TextToConvert)
For&atting
No special formatting is needed.
The result will be shown as a value, based upon the original text.
Ìf the £ sign is included in the text it will be ignored.
Ìf the % sign is included in the text, the result will be a decimal fraction which can then
be formatted as a percentage.
Ìf the original text format appears as a time hh:mm the result will be a time.
The same will be true for other recognised formats.
Explanation o- -or&ula #ho)n a1ove.
To extract the values from the following text is complicated!
The actual percentage value is of variable length, it can be either one, two or three digits long.
The only way to identify the value is the fact it always ends with the % sign.
There is no way to identify the beginning of the value, other than it is preceded by a space.
The main problem is calculating the length of the value to extract.
Ìf the extraction assumes the maximum length of three digits and the % sign, errors will occur
when the percentage is only one digit long, as alphabetic characters will be included.
To get around the problem the =SUBSTÌTUTE() function was used to increase the size of the
spaces in the text.
Now when the extraction takes place any unnecessary characters will be spaces which are
ignored by the =VALUE() function.
There was a 2% increase in sales. #VALUE!
There was a 50% increase in sales. #VALUE!
There was a 100% increase in sales. #VALUE!
=VALUE(MÌD(SUBSTÌTUTE(C52," "," "),SEARCH("???%",SUBSTÌTUTE(C52," "," ")),4))
=(*9,E(MÌD(SUBSTÌTUTE(C11," "," "),SEARCH("???%",SUBSTÌTUTE(C11," "," ")),4))
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VAR
Page 197 of 206
(*H
Values Values Values
10 10 10
10 10 11
9 11 9
10 10 12
0.25 0.25 1.6666667
=VAR(C4:C7) =VAR(E4:E7) =VAR(G4:G7)
!hat Doe# "t Do %
This function calculates the sample population variance of a list of values.
A sample population is used when the list of values represents a sample of a population.
.yntax
=VAR(Range1,Range2,Range3 through to Range30)
For&atting
No special formatting is needed.
Exa&ple
The table below was used by a company interested in buying a new machine
to pack washing powder.
Three machines were short listed and allow to run for a day.
At the end of the day four boxes of soap powder were picked at random from the production
of each machine.
The boxes were weighed and the =VAR() function used as these boxes only represented
a sample of the complete days production.
The machine with the smallest variance was the most consistent.
Soap Powder Box Filling Machine Test Results
Test 1 Test 2 Test 3 Test 4 Variance
Machine 1 1.4 1.5 1.6 1.5 0.0067 =VAR(D34:G34)
Machine 2 1.5 1.5 1.4 1.5 0.0025 =VAR(D35:G35)
Machine 3 1.5 1.6 1.7 1.8 0.0167 =VAR(D36:G36)
The smallest variance is : 0.0025 =MÌN(H34:H36)
The machine with the smallest variance is : Machine 2
=ÌNDEX(C34:C36,MATCH(MÌN(H34:H36),H34:H36,0))
Explanation o- -or&ula2
This finds the lowest value. =MÌN(H34:H36)
This finds the position of the lowest value. =MATCH(MÌN(H34:H36),H34:H36,0)
This looks down the Machine column to =ÌNDEX(C34:C36,MATCH(MÌN(H34:H36),H34:H36,0))
find the machine name.
A B C D E F G H I J K
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VARP
Page 198 of 206
(*HP
Values Values Values
10 10 10
10 10 11
9 11 9
10 10 12
0.1875 0.1875 1.25
=VARP(C4:C7) =VARP(E4:E7) =VARP(G4:G7)
!hat Doe# "t Do %
This function calculates the variance of a list of values.
The variance is calculated on the basis that the values represent the entire population.
.yntax
=VARP(Range1,Range2,Range3 through to Range30)
For&atting
No special formatting is needed.
Exa&ple
The table below was used by a company interested in buying a new machine
to pack washing powder.
A trial run a just four boxes per machine were produced.
The boxes were weighed and the =VARP() function used as these boxes
represented the entire test run.
The machine with the smallest variance was the most consistent.
Soap Powder Box Filling Machine Test Results
Test 1 Test 2 Test 3 Test 4 Variance
Machine 1 1.4 1.5 1.6 1.5 0.0050 =VARP(D32:G32)
Machine 2 1.5 1.5 1.4 1.5 0.0019 =VARP(D33:G33)
Machine 3 1.5 1.6 1.7 1.8 0.0125 =VARP(D34:G34)
The smallest variance is : 0.0019 =MÌN(H32:H34)
The machine with the smallest variance is : Machine 2
=ÌNDEX(C32:C34,MATCH(MÌN(H32:H34),H32:H34,0))
Explanation o- -or&ula2
This finds the lowest value. =(MÌN(H32:H34)
This finds the position of the lowest value. =MATCH(MÌN(H32:H34),H32:H34,0)
This looks down the Machine column to =ÌNDEX(C32:C34,MATCH(MÌN(H32:H34),H32:H34,0))
find the machine name.
A B C D E F G H I J K
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VLOOKUP
Page 199 of 206
(9778,P
The column numbers are not needed.
they are part o! the illustration.
col 1 col col " col 4 col # col $
Jan 10 20 30 40 50
Feb 80 90 100 110 120
Mar 97 69 45 51 77
Type a month to look for : Feb
Which column needs to be picked out : 4
The result is : 100
=VLOOKUP(G11,C6:H8,G12,FALSE)
!hat Doe# "t Do %
This function scans down the row headings at the side of a table to find a specified item.
When the item is found, it then scans across to pick a cell entry.
.yntax
=VLOOKUP(ÌtemToFind,RangeToLookÌn,ColumnToPickFrom,SortedOrUnsorted)
The ÌtemToFind is a single item specified by the user.
The RangeToLookÌn is the range of data with the row headings at the left hand side.
The ColumnToPickFrom is how far across the table the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.
For&atting
No special formatting is needed.
Exa&ple 1
This table is used to find a value based on a specified name and month.
The =VLOOKUP() is used to scan down to find the name.
The problem arises when we need to scan across to find the month column.
To solve the problem the =MATCH() function is used.
The =MATCH() looks through the list of names to find the month we require. Ìt then calculates
the position of the month in the list. Unfortunately, because the list of months is not as wide
as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is
added to compensate.
The =VLOOKUP() now uses this =MATCH() number to look across the columns and
picks out the correct cell entry.
The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the
row headings are not sorted.
Jan Feb Mar
Bob 10 80 97
Eric 20 90 69
Alan 30 100 45
Carol 40 110 51
David 50 120 77
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VLOOKUP
Page 200 of 206
Type a name to look for : eric
Type a month to look for : mar
The result is : 69
=VLOOKUP(F56,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)
Exa&ple 2
This example shows how the =VLOOKUP() is used to pick the cost of a spare part for
different makes of cars.
The =VLOOKUP() scans down row headings in column F for the spare part entered in column C.
When the make is found, the =VLOOKUP() then scans across to find the price, using the
result of the =MATCH() function to find the position of the make of car.
The functions use the absolute ranges indicated by the dollar symbol . This ensures that
when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do
not change.
Maker Spare Cost Lookup Table
Vauxhall Ìgnition £50 Vauxhall Ford VW
VW GearBox £600 GearBox 500 450 600
Ford Engine £1,200 Engine 1000 1200 800
VW Steering £275 Steering 250 350 275
Ford Ìgnition £70 Ìgnition 50 70 45
Ford CYHead £290 CYHead 300 290 310
Vauxhall GearBox £500
Ford Engine £1,200
=VLOOKUP(C81,F75:Ì79,MATCH(B81,G74:Ì74,0)+1,FALSE)
Exa&ple 4
Ìn the following example a builders merchant is offering discount on large orders.
The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the Total.
All the calculations take place in the Orders Table.
The name of the Ìtem is typed in column C of the Orders Table.
The Unit Cost of the item is then looked up in the Unit Cost Table.
The FALSE option has been used at the end of the function to indicate that the product
names down the side of the Unit Cost Table are not sorted.
Using the FALSE option forces the function to search for an exact match. Ìf a match is
not found, the function will produce an error.
=VLOOKUP(C126,C114116,2,FALSE)
The discount is then looked up in the Discount Table
Ìf the Quantity Ordered matches a value at the side of the Discount Table the =VLOOKUP will
look across to find the correct discount.
The TRUE option has been used at the end of the function to indicate that the values
down the side of the Discount Table are sorted.
Using TRUE will allow the function to make an approximate match. Ìf the Quantity Ordered does
not match a value at the side of the Discount Table, the next lowest value is used.
Trying to match an order of 125 will drop down to 100, and the discount from
A B C D E F G H I J
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VLOOKUP
Page 201 of 206
the 100 row is used.
=VLOOKUP(D126,F114:Ì116,MATCH(C126,G113:Ì113,0)+1,TRUE)
Discount Table
Unit Cost Table Bric+ !oo0 Ila##
Bric+ £2 1 0% 0% 0%
!oo0 £1 100 6% 3% 12%
Ila## £3 400 8% 5% 15%
Orders Table
Ìtem Units Unit Cost Discount Total
Brick 100 £2 6% £188
Wood 200 £1 3% £194
Glass 150 £3 12% £396
Brick 225 £2 6% £423
Wood 50 £1 0% £50
Glass 500 £3 15% £1,275
Formula for :
Unit Cost =VLOOKUP(C126,C114116,2,FALSE)
Discount =VLOOKUP(D126,F114:Ì116,MATCH(C126,G113:Ì113,0)+1,TRUE)
Total =(D126*E126)-(D126*E126*F126)
A B C D E F G H I J
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
WEEKDAY
Page 202 of 206
!EE8D*N
Date Weekday
Thu 01-Jan-98 5 =WEEKDAY(C4)
Thu 01-Jan-98 5 =WEEKDAY(C5)
Thu 01-Jan-98 5 =WEEKDAY(C6,1)
Thu 01-Jan-98 4 =WEEKDAY(C7,2)
Thu 01-Jan-98 3 =WEEKDAY(C8,3)
!hat Doe# "t Do%
This function shows the day of the week from a date.
.yntax
=WEEKDAY(Date,Type)
Type : This is used to indicate the week day numbering system.
1 : will set Sunday as 1 through to Saturday as 7
2 : will set Monday as 1 through to Sunday as 7.
3 : will set Monday as 0 through to Sunday as 6.
Ìf no number is specified, Excel will use 1.
For&atting
The result will be shown as a normal number.
Exa&ple
The following table was used by a hotel which rented a function room.
The hotel charged different rates depending upon which day of the week the booking was for.
The Booking Date is entered.
The Actual Day is calculated.
The Booking Cost is picked from a list of rates using the =LOOKUP() function.
Booking Date Actual Day Booking Cost
7-Jan-98 Wednesday £ 30.00
=LOOKUP(WEEKDAY(C34),C3945)
Booking Rates
Day Of Week Cost
1 £50
2 £25
3 £25
4 £30
5 £40
6 £50
7 £100
To show the result as the name of the day, use For&at, Cell#, Cu#to& and set
the $ype to 000 or 0000.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
WORKDAY
Page 203 of 206
!7H8D*N
StartDate Days Result
1-Jan-98 28 35836 =WORKDAY(D4,E4)
1-Jan-98 28 10-Feb-98 =WORKDAY(D5,E5)
!hat Doe# "t Do%
Use this function to calculate a past or future date based on a starting date and a
specified number of days. The function excludes weekends and holidays and can
therefore be used to calculate delivery dates or invoice dates.
.yntax
=WORKDAY(StartDate,Days,Holidays)
For&atting
The result will normally be shown as a number which can be formatted to a
normal date by using Format,Cells,Number,Date.
Exa&ple
The following example shows how the function can be used to calculate delivery dates
based upon an initial Order Date and estimated Delivery Days.
Order Date Delivery Days Delivery Date
Mon 02-Feb-98 2 Wed 04-Feb-98
Tue 15-Dec-98 28 Tue 26-Jan-99
=WORKDAY(D25,E25,D2832)
Holidays
Bank Holiday Fri 01-May-98
Xmas Fri 25-Dec-98
New Year Wed 01-Jan-97
New Year Thu 01-Jan-98
New Year Fri 01-Jan-99
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
YEAR
Page 204 of 206
NE*H
Date Year
### 1998 =YEAR(C4)
!hat Doe# "t Do%
This function extracts the year number from a date.
.yntax
=YEAR(Date)
For&atting
The result is shown as a number.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
YEARFRAC
Page 205 of 206
NE*HFH*C
Start Date End Date Fraction
1-Jan-98 1-Apr-98 0.25 =YEARFRAC(C4,D4)
1-Jan-98 31-Dec-98 1 =YEARFRAC(C5,D5)
1-Jan-98 1-Apr-98 25% =YEARFRAC(C6,D6)
!hat Doe# "t Do%
This function calculates the difference between two dates and expresses the result
as a decimal fraction.
.yntax
=YEARFRAC(StartDate,EndData,Basis)
Basis : Defines the calendar system to be used in the function.
0 : or omitted USA style 30 days per month divided by 360.
1 : 29 or 30 or 31 days per month divided by 365.
2 : 29 or 30 or 31 days per month divided by 360.
3 : 29 or 30 0r 31 days per month divided by 365.
4 : European 29 or 30 or 31 days divided by 360.
For&atting
The result will be shown as a decimal fraction, but can be formatted as a percent.
Exa&ple
The following table was used by a company which hired people on short term contracts
for a part of the year.
The Pro Rata Salary which represents the annual salary is entered.
The Start and End dates of the contract are entered.
The =YEARFRAC() function is used to calculate Actual Salary for the portion of the year.
Start End Pro Rata Salary Actual Salary
1-Jan-98 31-Dec-98 £12,000 £12,000 =YEARFRAC(B32,C32+1,4)*D32
1-Jan-98 31-Mar-98 £12,000 £3,000 =YEARFRAC(B33,C33+1,4)*D33
1-Jan-98 30-Jun-98 £12,000 £6,000 =YEARFRAC(B34,C34+1,4)*D34
Note
The extra 1 has been added to the End date to compensate for the fact that the =YEARFRAC()
function calculates from the Start date up to, but not including, the End date.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Project Dates
Page 206 of 206
ProCect Date# 3ou#e Buil0ing
Target Delivery Tue 27-Jan-98 Target Budget £ 12,000
Job Stage Start Date End Date Daily Cost Total
Survey Mon 05-Jan-98 5 Fri 09-Jan-98 £ 200 £ 1,000
Foundation Mon 12-Jan-98 4 Thu 15-Jan-98 £ 1,000 £ 4,000
Walls Fri 16-Jan-98 3 Tue 20-Jan-98 £ 800 £ 2,400
Roof Wed 21-Jan-98 6 Wed 28-Jan-98 £ 400 £ 2,400
Electrics Thu 29-Jan-98 4 Tue 03-Feb-98 £ 300 £ 1,200
Actual Delivery Tue 03-Feb-98 Total Cost £ 11,000
Against Target 5 days behind Budget % 92%
Total Days 22
Days
Required
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
doc_270069786.xls
Excel Utilities
Excel
Function
Dictionary
v1.0 Beta
Copyright © 1998 - 2001 Peter oneley
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Documentation
Page 2 of 206
!hat "# "n $he Dictionary %
This workbook contains 157 worksheets, each explaining the purpose and usage of
particular Excel functions.
There are also a number of sample worksheets which are simple models of common
applications, such as Timesheet and Date Calculations.
For&atting
Each worksheet uses the same type of formatting to indicate the various types of entry.
North Text headings are shown in grey.
100
100 Data is shown as purple text on a yellow background.
100
300 The results of Formula are shown as blue on yellow.
=SUM(C13:C15) The formula used in the calulations is shown as blue text.
The Arial font is used exclusivley throughout the workbook and should display correctly
with any installation of Windows.
Each sheet has been designed to be as simple as possible, with no fancy macros to
accomplish the desrired result.
Printing
Each worksheet is set to print on to A4 portrait.
The printouts will have the column headings of A,B,C... and the row numbers 1,2,3... which
will assist with the reading of the formula.
The ideal printer would be a laser set at 600dpi.
Ìf you are using a dot matrix or inkjet, it may be worth switching off the colours before printing,
as these will print as dark grey. (See the sheet dealing with Colour settings).
Protection
Each sheet is unprotected so that you will be able to change values and experiment
with the calculations.
'acro#
There are only a few very simple macros which are used by the various buttons to
naviagte through the sheets. These have been written very simply, and do not make any attempt
to change your current Toolbars and Menus.
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Ìnstructions
Page 3 of 206
!hat Do $he Button# Do %
View
View
This button will display the worksheet
containing the function example.
1. Click on the function name, then
2. Click on the (ie) button.
Sort
This button sorts the list of functions
into alphabetical order.
Sort
Category
This describes the category the
function is a member of.
Click this button to sort alphabetically.
Category
Location
This shows where the function is
stored in Excel.
Built-in indicates that the function
is part of Excel itself.
*naly#i# $oolPa+ indicates the
function is stored in the Analysis
ToolPak add-in.
Click this button to sort alphabetically.
Location
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Colours
Page 4 of 206
,#ing Di--erent 'onitor .etting#
Each sheet has been designed to fit within the visible width of monitors with a low resolution
of 640 x 480. This ensures that you do not need to scroll from left and right to see all the data.
The colours are best suited to monitors capable of 256 colours.
On monitors using just 16 colours the greys may look a bit rough!
You can switch colours off and on using the button below.
$hi# &ay ta+e a
-e) &inute# on
any co&puter /
Sample Colour Scheme
North South East West Total
Alan 100 100 100 100 400
Bob 100 100 100 100 400
Carol 100 100 100 100 400
Total 300 300 300 300 1200
Colour On
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Analysis ToolPak
Page 5 of 206
*naly#i# $oolPa+
!hat "# $he *naly#i# $oolPa+ %
The Analysis ToolPak is an add-in file containing
extra functions which are not built in to Excel.
The functions cover areas such as Date and
Mathematical operations.
The Analysis ToolPak must be added-in to Excel before
these functions will be available.
Any formula using these functions without the ToolPak loaded will show the #NAME error.
Check For Analysis ToolPak
Analysis ToolPak
Load the Analysis ToolPak
UnLoad the Analysis ToolPak
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FunctionList
Page 6 of 206
Age Calculation Sample Sample
AutoSum shortcut key Sample Sample
Brackets in formula Sample Sample Sample
FileName formula Sample Sample
Ìnstant Charts Sample Sample
Ordering Stock Sample Sample Stock Ordering
Percentages Sample Sample How to calculate various percentages
Project Dates Sample Sample Example using date calculation.
Show all formula Sample Sample
Split ForenameSurname Sample Sample
Time Calculation Sample Sample How to calculate time.
TimeSheet For Flexi Sample Sample Example flexi time sheet.
ABS Mathematical Built-in Returns the absolute value of a number
AND Logical Built-in Returns TRUE if all its arguments are TRUE
AVERAGE Statistical Built-in Returns the average of its arguments
BÌN2DEC Engineering Analysis ToolPak Converts a binary number to decimal
CEÌLÌNG Mathematical Built-in Rounds a number to the nearest integer or to the nearest multiple of significance
CELL Ìnformation Built-in Returns information about the formatting, location, or contents of a cell
CHAR Text Built-in Returns the character specified by the code number
CHOOSE Lookup Built-in Chooses a value from a list of values
CLEAN Text Built-in Removes all nonprintable characters from text
CODE Text Built-in Returns a numeric code for the first character in a text string
COMBÌN Mathematical Built-in Returns the number of combinations for a given number of objects
CONCATENATE Text Built-in Joins several text items into one text item
CONVERT Engineering Analysis ToolPak Converts a number from one measurement system to another
CORREL Statistical Built-in Returns the correlation coefficient between two data sets
COUNT Statistical Built-in Counts how many numbers are in the list of arguments
COUNTA Statistical Built-in Counts how many values are in the list of arguments
COUNTBLANK Ìnformation Built-in Counts the number of blank cells within a range
COUNTÌF Mathematical Built-in Counts the number of nonblank cells within a range that meet the given criteria
DATE Date Built-in Returns the serial number of a particular date
DATEDÌF Date Built-in Calculates the difference between two dates. Undocumented in v5/7/97
DATEVALUE Date Built-in Converts a date in the form of text to a serial number
DAVERAGE Database Built-in Returns the average of selected database entries
DAY Date Built-in Converts a serial number to a day of the month
DAYS360 Date Built-in Calculates the number of days between two dates based on a 360-day year
DB Financial Built-in Returns the depreciation of an asset for a specified period using the fixed-declining balance method
DCOUNT Database Built-in Counts the cells that contain numbers in a database
DCOUNTA Database Built-in Counts nonblank cells in a database
DEC2BÌN Engineering Analysis ToolPak Converts a decimal number to binary
DEC2HEX Engineering Analysis ToolPak Converts a decimal number to hexadecimal
DELTA Engineering Analysis ToolPak Tests whether two values are equal
DGET Database Built-in Extracts from a database a single record that matches the specified criteria
DMAX Database Built-in Returns the maximum value from selected database entries
DMÌN Database Built-in Returns the minimum value from selected database entries
DOLLAR Text Built-in Converts a number to text, using currency format
DSUM Database Built-in Adds the numbers in the field column of records in the database that match the criteria
EDATE Date Analysis ToolPak Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH Date Analysis ToolPak Returns the serial number of the last day of the month before or after a specified number of months
ERROR.TYPE Ìnformation Built-in Returns a number corresponding to an error type
EVEN Mathematical Built-in Rounds a number up to the nearest even integer
EXACT Text Built-in Checks to see if two text values are identical
FACT Mathematical Built-in Returns the factorial of a number
FÌND Text Built-in Finds one text value within another (case-sensitive)
FÌXED Text Built-in Formats a number as text with a fixed number of decimals
FLOOR Mathematical Built-in Rounds a number down, toward zero
FORECAST Statistical Built-in Returns a value along a linear trend
FREQUENCY Statistical Built-in Returns a frequency distribution as a vertical array
GCD Mathematical Analysis ToolPak Returns the greatest common divisor
GESTEP Engineering Analysis ToolPak Tests whether a number is greater than a threshold value
GROWTH Statistical Built-in Returns values along an exponential trend
HEX2DEC Engineering Analysis ToolPak Converts a hexadecimal number to decimal
HLOOKUP Lookup Built-in Looks in the top row of an array and returns the value of the indicated cell
HOUR Date Built-in Converts a serial number to an hour
ÌF Logical Built-in Specifies a logical test to perform
ÌNDEX Lookup Built-in Uses an index to choose a value from a reference or array
ÌNDÌRECT Lookup Built-in Returns a reference indicated by a text value
ÌNFO Ìnformation Built-in Returns information about the current operating environment
ÌNT Mathematical Built-in Rounds a number down to the nearest integer
ÌSBLANK Ìnformation Built-in Returns TRUE if the value is blank
ÌSERR Ìnformation Built-in Returns TRUE if the value is any error value except #N/A
ÌSERROR Ìnformation Built-in Returns TRUE if the value is any error value
ÌSEVEN Ìnformation Analysis ToolPak Returns TRUE if the number is even
ÌSLOGÌCAL Ìnformation Built-in Returns TRUE if the value is a logical value
ÌSNA Ìnformation Built-in Returns TRUE if the value is the #N/A error value
ÌSNONTEXT Ìnformation Built-in Returns TRUE if the value is not text
ÌSNUMBER Ìnformation Built-in Returns TRUE if the value is a number
ÌSODD Ìnformation Analysis ToolPak Returns TRUE if the number is odd
ÌSREF Ìnformation Built-in Returns TRUE if the value is a reference
ÌSTEXT Ìnformation Built-in Returns TRUE if the value is text
LARGE Statistical Built-in Returns the k-th largest value in a data set
LCM Mathematical Analysis ToolPak Returns the least common multiple
LEFT Text Built-in Returns the leftmost characters from a text value
LEN Text Built-in Returns the number of characters in a text string
LOOKUP (vector) Lookup Built-in Looks up values in a vector or array
LOWER Text Built-in Converts text to lowercase
Using DATEDÌF()
Using Alt and =
Using MÌD() CELL() and FÌND()
Using F11
Using Ctrl and `
Using LEFT() RÌGHT() FÌND() SUBSTÌTUTE()
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
FunctionList
Page 7 of 206
MATCH Lookup Built-in Looks up values in a reference or array
MAX Statistical Built-in Returns the maximum value in a list of arguments
MEDÌAN Statistical Built-in Returns the median of the given numbers
MÌD Text Built-in Returns a specific number of characters from a text string starting at the position you specify
MÌN Statistical Built-in Returns the minimum value in a list of arguments
MÌNUTE Date Built-in Converts a serial number to a minute
MÌNVERSE Mathematical Built-in Returns the matrix inverse of an array
MMULT Mathematical Built-in Returns the matrix product of two arrays
MOD Mathematical Built-in Returns the remainder from division
MODE Statistical Built-in Returns the most common value in a data set
MONTH Date Built-in Converts a serial number to a month
MROUND Mathematical Analysis ToolPak Returns a number rounded to the desired multiple
N Ìnformation Built-in Returns a value converted to a number
NA Ìnformation Built-in Returns the error value #N/A
NETWORKDAYS Date Analysis ToolPak Returns the number of whole workdays between two dates
NOT Logical Built-in Reverses the logic of its argument
NOW Date Built-in Returns the serial number of the current date and time
ODD Mathematical Built-in Rounds a number up to the nearest odd integer
OR Logical Built-in Returns TRUE if any argument is TRUE
PERMUT Statistical Built-in Returns the number of permutations for a given number of objects
PÌ Mathematical Built-in Returns the value of Pi
POWER Mathematical Built-in Returns the result of a number raised to a power
PRODUCT Mathematical Built-in Multiplies its arguments
PROPER Text Built-in Capitalises the first letter in each word of a text value
QUARTÌLE Statistical Built-in Returns the quartile of a data set
QUOTÌENT Mathematical Analysis ToolPak Returns the integer portion of a division
RAND Mathematical Built-in Returns a random number between 0 and 1
RANDBETWEEN Mathematical Analysis ToolPak Returns a random number between the numbers you specify
RANK Statistical Built-in Returns the rank of a number in a list of numbers
REPLACE Text Built-in Replaces characters within text
REPT Text Built-in Repeats text a given number of times
RÌGHT Text Built-in Returns the rightmost characters from a text value
ROMAN Mathematical Built-in Converts an arabic numeral to roman, as text
ROUND Mathematical Built-in Rounds a number to a specified number of digits
ROUNDDOWN Mathematical Built-in Rounds a number down, toward zero
ROUNDUP Mathematical Built-in Rounds a number up, away from zero
SECOND Date Built-in Converts a serial number to a second
SÌGN Mathematical Built-in Returns the sign of a number
SLN Financial Built-in Returns the straight-line depreciation of an asset for one period
SMALL Statistical Built-in Returns the k-th smallest value in a data set
STDEV Statistical Built-in Estimates standard deviation based on a sample
STDEVP Statistical Built-in Calculates standard deviation based on the entire population
SUBSTÌTUTE Text Built-in Substitutes new text for old text in a text string
SUBTOTAL Mathematical Built-in Returns a subtotal in a list or database
SUM Mathematical Built-in Adds its arguments
SUM_as_Running_Total Mathematical Built-in Sample
SUM_using_names Sample Sample
SUM_with_OFFSET Lookup Built-in Sample
SUMÌF Mathematical Built-in Adds the cells specified by a given criteria
SUMPRODUCT Mathematical Built-in Returns the sum of the products of corresponding array components
SYD Financial Built-in Returns the sum-of-years' digits depreciation of an asset for a specified period
T Text Built-in Converts its arguments to text
TEXT Text Built-in Formats a number and converts it to text
TÌME Date Built-in Returns the serial number of a particular time
-Timesheet Sample Sample Sample
TÌMEVALUE Date Built-in Converts a time in the form of text to a serial number
TODAY Date Built-in Returns the serial number of today's date
TRANSPOSE Lookup Built-in Returns the transpose of an array
TREND Statistical Built-in Returns values along a linear trend
TRÌM Text Built-in Removes spaces from text
TRUNC Mathematical Built-in Truncates a number to an integer
TYPE Ìnformation Built-in Returns a number indicating the data type of a value
UPPER Text Built-in Converts text to uppercase
VALUE Text Built-in Converts a text argument to a number
VAR Statistical Built-in Estimates variance based on a sample
VARP Statistical Built-in Calculates variance based on the entire population
VLOOKUP Lookup Built-in Looks in the first column of an array and moves across the row to return the value of a cell
WEEKDAY Date Built-in Converts a serial number to a day of the week
WORKDAY Date Analysis ToolPak Returns the serial number of the date before or after a specified number of workdays
YEAR Date Built-in Converts a serial number to a year
YEARFRAC Date Analysis ToolPak Returns the year fraction representing the number of whole days between start_date and end_date
Using SUM(jan)
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Time Calculation
Page 8 of 206
$i&e Calculation
Excel can work with time very easily.
Time can be entered in various different formats and calculations performed.
There are one or two oddities, but nothing which should put you off working with it.
$yping ti&e
When time is entered into worksheet it should be entered with a colon between
1:30 12:30 20:15 22:45
Excel can cope with either the 24hour system or the am/pm system.
You must leave a space between the number and the text.
1:30 AM 1:30 PM 10:15 AM 10:15 PM
Fin0ing the 0i--erence 1et)een t)o ti&e#
You can subtract two time values to find the length of time between.
Start End Duration
1:30 2:30 1:00 =D24-C24
8:00 17:00 9:00 =D25-C25
8:00 AM 5:00 PM 9:00 AM Ìf the result is not shown correctly,
You may need to reformat the answer.
Look at the section about formatting
further in this worksheet.
*00ing ti&e
You can add time to find a total time.
This works well until the total time goes above 24 hours.
For totals greater than 24 hours you may need to apply some special formatting.
Start End Duration
1:30 2:30 1:00
8:00 17:00 9:00
7:30 AM 5:45 PM 10:15
20:15
For&atting ti&e
When time is added together the result may go beyond 24 hours.
Usually this gives an incorrect result, as in the example below.
To correct this error, the result needs to be formatted with a Custom format.
Exa&ple 1 2 "ncorrect -or&atting
Start End Duration
7:00 18:30 11:30
8:00 17:00 9:00
7:30 17:45 10:15
Total 6:45 =SUM(E49:E51)
Exa&ple 2 2 Correct -or&atting
Start End Duration
7:00 18:30 11:30
8:00 17:00 9:00
7:30 17:45 10:15
Total 30:45 =SUM(E56:E58)
3o) $o *pply Cu#to& For&atting
The custom format for time use a pair of square brackets [hh] on either side
of the hours indicators.
1. Click on the cell which needs the format.
See the $i&e.heet example for an example.
the hour and the minutes, such as 12240, rather than 12.40
To use the am/pm system you must enter the a& or p& after the time.
2. Choose the For&at menu.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Time Calculation
Page 9 of 206
3. Choose Cell#.
4. Click the u&1er tag at the top right.
5. Choose Cu#to&.
6. Click inside the $ype: box.
7. Type 5hh62&& as the format.
8. Click 78 to confirm.
A B C D E F G H I J
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TimeSheet For Flexi
Page 10 of 206
$i&e.heet -or Flexi
Week beginning Mon 05-Jan-98 Normal Hours 37:30
Day Arrive Lunch Out Lunch Ìn Depart Total
Mon 05 8:00 13:00 14:00 17:00 8:00 =(F6-C6)-(E6-D6)
Tue 06 8:45 12:30 13:30 17:00 7:15
Wed 07 9:00 13:00 14:00 18:00 8:00
Thu 08 8:30 13:00 14:00 17:00 7:30
Fri 09 8:00 12:00 13:00 17:00 8:00
Total Hours 38:45 =SUM(G6:G10)
Under worked by - =ÌF(G3-G11>0,G3-G11, "-")
Over worked by 1:15 =ÌF(G3-G11 87 W 112 p 137 ? 162 ? 187 ? 212 ? 237 ?
13 38 & 63 ? 88 X 113 q 138 ? 163 ? 188 ? 213 ? 238 ?
14 39 ' 64 @ 89 Y 114 r 139 ? 164 ? 189 ? 214 ? 239 ?
15 40 ( 65 A 90 Z 115 s 140 ? 165 190 ! 215 " 240
16 41 ) 66 B 91 [ 116 t 141 ? 166 # 191 ? 216 ? 241
17 42 * 67 C 92 \ 117 u 142 ? 167 $ 192 ? 217 ? 242
18 43 + 68 D 93 ] 118 v 143 ? 168 % 193 ? 218 & 243
19 44 , 69 E 94 ^ 119 w 144 169 ? 194 ' 219 ( 244
20 45 - 70 F 95 _ 120 x 145 ! 170 ) 195 * 220 + 245
21 46 . 71 G 96 ` 121 y 146 " 171 , 196 - 221 ? 246
22 47 / 72 H 97 a 122 z 147 # 172 . 197 / 222 ? 247
23 48 0 73 Ì 98 b 123 { 148 $ 173 0 198 1 223 ? 248
24 49 1 74 J 99 c 124 | 149 % 174 2 199 3 224 & 249 ?
25 50 2 75 K 100 d 125 } 150 ' 175 4 200 ? 225 ( 250 ?
ote
Number 32 does not show as it is the SPACEBAR character.
A B C D E F G H I J K L M N O P Q R S T U ! "
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
CHOOSE
Page 32 of 206
C377.E
Result
1 Alan =CHOOSE(C4,"Alan","Bob","Carol")
3 Carol =CHOOSE(C5,"Alan","Bob","Carol")
2 Bob =CHOOSE(C6,"Alan","Bob","Carol")
3 18% =CHOOSE(C7,10%,15%,18%)
1 10% =CHOOSE(C8,10%,15%,18%)
2 15% =CHOOSE(C9,10%,15%,18%)
!hat Doe# "t Do%
This function picks from a list of options based upon an Ìndex value given to by the user.
.yntax
=CHOOSE(UserValue, Ìtem1, Ìtem2, Ìtem3 through to Ìtem29)
For&atting
No special formatting is required.
Exa&ple
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 =ÌF() 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.
Name Time Position Medal
Alan 1:30 2 Silver =ÌF(D30=D43,C43*10%,C43*5%)
Bob 6000 5000 600 =ÌF(C44>=D44,C44*10%,C44*5%)
Carol 2000 4000 100 =ÌF(C45>=D45,C45*10%,C45*5%)
Exa&ple 4
This example uses the =AND() within the =ÌF() function.
A builders merchant gives 10% discount on certain product lines.
The discount is only given on products which are on Special Offer, when the Order Value
is £1000 or above.
the value of the order is above £1000.
Special Order
Product Offer Value Discount Total
Wood Yes £ 2,000 £ 200 £ 1,800
The =AND() function is used with the =ÌF() to check that the product is on offer an0 that
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
ÌF
Page 94 of 206
Glass No £ 2,000 £ - £ 2,000
Cement Yes £ 500 £ - £ 500
Turf Yes £ 3,000 £ 300 £ 2,700
=ÌF(AND(C61="Yes",D61>=1000),D61*10%,0)
A B C D E F G H I J
59
60
61
62
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
ÌNDEX
Page 95 of 206
"DEP
Holiday booking price list.
People
Weeks 1 2 3 4
1 £500 £300 £250 £200
2 £600 £400 £300 £250
3 £700 £500 £350 £300
How many weeks required : 2
How many people in the party : 4
Cost per person is : 250 =ÌNDEX(D7:G9,G11,G12)
!hat Doe# "t 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.
Ìt can be used with a single block of data, or non-continuos blocks.
.yntax
There are various forms of syntax for this function.
.yntax 1
=ÌNDEX(RangeToLookÌn,Coordinate)
This is used when the RangeToLookÌn is either a single column or row.
The Co-ordinate indicates how far down or across to look when picking the data from the range.
Both of the examples below use the same syntax, but the Co-ordinate refers to a row when
the range is vertical and a column when the range is horizontal.
Colours
Red
Green
Blue Size Large Medium Small
Type either 1, 2 or 3 : 2 Type either 1, 2 or 3 : 2
The colour is : Green The size is : Medium
=ÌNDEX(D32
34,D36) =ÌNDEX(G34:Ì34,H36).yntax 2
=ÌNDEX(RangeToLookÌn,RowCoordinate,ColumnColumnCordinate)
This syntax is used when the range is made up of rows and columns.
Country Currency Population Capitol
England Sterling 50 M London
France Franc 40 M Paris
Germany DM 60 M Bonn
Spain Peseta 30 M Barcelona
Type 1,2,3 or 4 for the country : 2
Type 1,2 or 3 for statistics : 3
The result is : Paris =ÌNDEX(D45:F48,F50,F51)
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
ÌNDEX
Page 96 of 206
.yntax 4
=ÌNDEX(NamedRangeToLookÌn,RowCoordinate,ColumnColumnCordinate,AreaToPickFrom)
Using this syntax the range to look in can be made up of multiple areas.
The easiest way to refer to these areas is to select them and give them a single name.
The AreaToPickFrom indicates which of the multiple areas should be used.
Ìn the following example the figures for North and South have been named as one
range called NorthAndSouth.
7H$3 Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,000 £2,000 £3,000 £4,000
Wood £5,000 £6,000 £7,000 £8,000
Glass £9,000 £10,000 £11,000 £12,000
.7,$3 Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,500 £2,500 £3,500 £4,500
Wood £5,500 £6,500 £7,500 £8,500
Glass £9,500 £10,500 £11,500 £12,500
Type 1, 2 or 3 for the product : 1
Type 1, 2, 3 or 4 for the Qtr : 3
Type 1 for North or 2 for South : 2
The result is : Err:504 =ÌNDEX(NorthAndSouth,F76,F77,F78)
Exa&ple
This is an extended version of the previous example.
Ìt 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 =ÌNDEX() function to look for the data.
E*.$ :tr1 :tr2 :tr4 :tr=
Bric+# £1,000 £2,000 £3,000 £4,000
!oo0 £5,000 £6,000 £7,000 £8,000
Ila## £9,000 £10,000 £11,000 £12,000
!E.$ Qtr1 Qtr2 Qtr3 Qtr4
Bricks £1,500 £2,500 £3,500 £4,500
Wood £5,500 £6,500 £7,500 £8,500
Glass £9,500 £10,500 £11,500 £12,500
Type 1, 2 or 3 for the product : )oo0
Type 1, 2, 3 or 4 for the Qtr : =0 but =100")
Total of item typed in following cell. service 450 =SUMÌF(C4:C12,E18,E4:E12)
!hat Doe# "t Do %
This function adds the value of items which match criteria set by the user.
.yntax
=SUMÌF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)
=SUMÌF(C4:C12,"Brakes",E4:E12) This examines the names of products in C4:C12.
Ìt then identifies the entries for Brakes.
Ìt then totals the respective figures in E4:E12
=SUMÌF(E4:E12,">=100") This examines the values in E4:E12.
Ìf the value is >=100 the value is added to the total.
For&atting
No special formatting is needed.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
SUMPRODUCT
Page 182 of 206
.,'PH7D,C$
Ìtem Sold price
Tyres 5 100
Filters 2 10
Bulbs 3 2
Total Sales Value : 526 =SUMPRODUCT(D4
6,E4:E6)!hat Doe# "t Do %
This function uses at least two columns of values.
The values in the first column are multipled with the corresponding value in the second column.
The total of all the values is the result of the calculation.
.yntax
=SUMPRODUCT(Range1, Range, Range3 through to Range30)
For&atting
No special formatting is needed.
Exa&ple
The following table was used by a drinks merchant to keep track of stock.
The merchant needed to know the total purchase value of the stock, and the potential
value of the stock when it is sold, takinging into account the markup percentage.
The =SUMPRODUCT() function is used to multiply the Cases Ìn Stock with the Case Price to
calculate what the merchant spent in buying the stock.
The =SUMPRODUCT() function is used to multiply the Cases Ìn Stock with
the Bottles Ìn Case and the Bottle Setting Price, to calculate the potential value of the
stock if it is all sold.
Product Markup
Red Wine 10 £120 10 £12.00 25% £15.00
White Wine 8 £130 10 £13.00 25% £16.25
Champagne 5 £200 6 £33.33 80% £60.00
Beer 50 £24 12 £2.00 20% £2.40
Lager 100 £30 12 £2.50 25% £3.13
=D39/E39 =F39+F39*G39
Total Value Of Stock : £7,440 =SUMPRODUCT(C35:C39,D35
39)Total Selling Price Of Stock : £9,790 =SUMPRODUCT(C35:C39,E35:E39,H35:H39)
Profit : £2,350 =E44-E43
Cases Ìn
Stock
Case
Price
Bottles
Ìn Case
Bottle
Cost
Bottle Selling
Price
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
SYD
Page 183 of 206
.ND
Purchase Value Of A New Car £20,000
Second Hand Value £8,000
Number Of Years Ownership 6
Deprecation in year 1 £3,429 =SYD(F3,F4,F5,1)
Deprecation in year 2 £2,857 =SYD(F3,F4,F5,2)
Deprecation in year 3 £2,286 =SYD(F3,F4,F5,3)
Deprecation in year 4 £1,714 =SYD(F3,F4,F5,4)
Deprecation in year 5 £1,143 =SYD(F3,F4,F5,5)
Deprecation in year 6 £571 =SYD(F3,F4,F5,6)
Total Depreciation : £12,000 =SUM(F7:F12)
!hat Doe# "t Do %
This function calculates the depreciation of an item throughout its life, using the sum of the
years digits.
The depreciation is greatest in the earlier part of the items life.
!hat i# the .u& 7- $he Near# Digit# %
The sum of the years digits adds together the each of the years of the life.
A life of 3 years has a sum of 1+2+3 equalling 6.
Each of the years is then calculated as a percentage of the sum of the years.
Year 3 is 50% of 6, year 2 is 33% of 6, year 1 is 17% 6.
The total depreciation of the item is then allocated on the basis of these percentages.
A depreciation of £9000 is allocated as 50% being £4500, 33% being £3000, 17% being £1500.
£9,000
1 17% £1,500
2 33% £3,000
3 50% £4,500
As the greater part of the depreciation is allocated to the earliest years the values are
inverted, year 1 is $4500, year 2 is £3000 and year 1 is £1500.
Exa&ple 1
Purchase Price Of A Car : £10,000
Salvage Value : £1,000
Expected Life in Years : 3
As % Of Total Depreciation
Depreciation in Year 1 : £4,500 ===> 0.5
Depreciation in Year 2 : £3,000 ===> 0.333333
Depreciation in Year 3 : £1,500 ===> 0.166667
=SYD(E39,E40,E41,3)
1. Add together the digits of the Life to get the SumOfTheYearsDigits, 1+2+3=6.
2. Subtract the Salvage from the Purchase Price to get Total Deprectation, £10000-£1000=£9000.
3. Divide the Total Deprectation by the SumOfTheYearsDigits, £9000/6=£1500.
4. Ìnvert the year digits, 1,2,3 becomes 3,2,1.
5. Multiply 3,2,1 by £1500 to get £4500, £3000, £1500, these values are the depreciation
values for each of the three years in the life of the item.
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
SYD
Page 184 of 206
Exa&ple 2
The same example using 4 years.
Purchase Price Of A Car : £10,000
Salvage Value : £1,000
Expected Life in Years : 4
As % Of Total Depriciation
Depreciation in Year 1 : £3,600 0.4
Depreciation in Year 2 : £2,700 0.3
Depreciation in Year 3 : £1,800 0.2
Depreciation in Year 4 : £900 0.1
Total Depreciation : £9,000 100%
Exa&ple 4
This example will adjust itself to accommodate any number of years between 1 and 10.
Purchase Price Of A Car : £10,000
Salvage Value : £1,000
Expected Life in Years (1 to 10) : 7
As % Of Total Depriciation
Year 1 £2,250 25%
Year 2 £1,929 21%
Year 3 £1,607 18%
Year 4 £1,286 14%
Year 5 £964 11%
Year 6 £643 7%
Year 7 £321 4%
Year
Year
Year
£9,000 100%
.yntax
=SYD(OriginalCost,SalvageValue,Life,PeriodToCalculate)
For&atting
No special formatting is needed.
A B C D E F G H I
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
T
Page 185 of 206
$
Cell To Test Result
Hello Hello =T(D4)
10 =T(D5)
1-Jan-98 =T(D6)
=T(D7)
!hat Doe# "t Do %
This function examines an entry to determine whether it is text or not.
Ìf the value is text, then the text is the result of the function
Ìf the value is not text, the result is a blank.
The function is not specifically needed by Excel, but is included for compatibility with
other spreadsheet programs.
.yntax
=T(CellToTest)
For&atting
No special formatting is needed.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TEXT
Page 186 of 206
$EP$
10 10.00 =TEXT(C4,"0.00")
10 £10.00 =TEXT(C5,"£0.00")
10 10 =TEXT(C6,"0")
10 £10 =TEXT(C7,"£0")
10.25 10.3 =TEXT(C8,"0.0")
10.25 £10.3 =TEXT(C9,"£0.0")
!hat Doe# "t Do %
This function converts a number to a piece of text.
The formatting for the text needs to be specified in the function.
.yntax
=TEXT(NumberToConvert,FormatForConversion)
For&atting
No special formatting is required.
Original
Number
Converted
To Text
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TÌME
Page 187 of 206
$"'E
Hour Minute Second Time
14 30 59 14:30:59 =TÌME(C4,D4,E4)
14 30 59 2:30:59 PM =TÌME(C5,D5,E5)
14 30 59 0.60485 =TÌME(C6,D6,E6)
!hat Doe# "t Do%
This function will convert three separate numbers to an actual time.
.yntax
=TÌME(Hour,Minute,Second)
For&atting
The result will be shown as a time which can be formatted either as 12 or 24 hour style.
Ìf a normal number format is applied a decimal fraction is shown which represents the
time as a fraction of the day.
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TÌMEVALUE
Page 188 of 206
$"'E(*9,E
Text Time
14:30:59 0.604849537 =TÌMEVALUE(C4)
14:30:59 14:30:59 =TÌMEVALUE(C5)
14:30:59 2:30:59 PM =TÌMEVALUE(C6)
!hat Doe# "t Do%
This function will show an actual time based on a piece of text which looks
like a time. Ìt is useful when data is imported from other applications, such as
from mainframe computers, which convert all values to text.
.yntax
=TÌMEVALUE(Text)
For&atting
The result will be shown as a number representing the time a fraction of the day.
Formatting can be applied for either the 12 or 24 hour clock system.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TODAY
Page 189 of 206
$7D*N
Today Ìs
25-Jun-15 =TODAY()
!hat Doe# "t Do%
Use this to show the current date.
.yntax
=TODAY()
For&atting
The result will normally be displayed using the DD-MMM-YY format.
Exa&ple
The following example shows how the Today function is used to calculate the number
of days since a particular day.
Date Days Since
1-Jan-97 6749 =TODAY()-C20
10-Aug-97 6528 =TODAY()-C21
Note that the result is actually the number of days before todays date. To calculate
a result which includes the current date an extra 1 will need to be added.
Date Days Since
1-Jan-97 6750 =TODAY()-C28+1
10-Aug-97 6529 =TODAY()-C29+1
Exa&ple
The following example shows the number of days from today until the year 2000.
Year 2000 Days Until
01-Jan-2000 -5654 =C36-TODAY()
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TRANSPOSE
Page 190 of 206
$H*.P7.E
Jan Feb
Alan 10 30
Bob 40 50
Carol 70 80
Total 120 160
Alan Bob Carol Total
Jan 10 40 70 120
Feb 30 50 80 160
{=TRANSPOSE(C3:E7)}
As an array formula in all these cells
!hat Doe# "t Do %
This function copies data from a range, and places in it in a new range, turning it so
that the data originally in columns is now in rows, and the data originally in rows
is in columns.
The transpose range must be the same size as the original range.
The function needs to be entered as an array formula.
To enter an array formula you must first highlight all the cells where the formula is required.
Next type the formula, such as =TRANSPOSE(A1:A5).
Finally press Ctrl+Shift+Enter to confirm it.
Ìf changes need to be made to the formula, the entire array has to be highlighted, the edits
can then be made and the Ctrl+Shift+Enter used to confirm it.
.yntax
=TRANSPOSE(Range)
For&atting
No special formatting is needed.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TREND
Page 191 of 206
$HED !3*$ ". C7.$ 1 %
Historical Data Predicted Values
Month Sales Month Sales
1 £1,000 7 £4,940 {=TREND(C8:C13,B8:B13,E8:E13)}
2 £2,000 8 £5,551 {=TREND(C5:C10,B5:B10,E5:E10)}
3 £2,500 9 £6,163 {=TREND(C5:C10,B5:B10,E5:E10)}
4 £3,500 10 £6,774 {=TREND(C5:C10,B5:B10,E5:E10)}
5 £3,800 11 £7,386 {=TREND(C5:C10,B5:B10,E5:E10)}
6 £4,000 12 £7,997 {=TREND(C5:C10,B5:B10,E5:E10)}
!hat Doe# "t Do %
This function predicts values based upon three sets of related values.
The prediction is based upon the Linear Trend of the original values.
The function is an array function and must be entered using Ctrl+Shift+Enter.
.yntax
=TREND(KnownYs,KnownXs,RequiredXs,Constant)
The KnownYs is the range of values, such as Sales Figures.
The KnownXs is the intervals used when collecting the data, such as Months.
The RequiredXs is the range for which you want to make the prediction, such as Months.
For&atting
No special formatting is needed.
Exa&ple
The following tables were used by a company to predict when they would start to
make a profit.
Their bank manager had told the company that unless they could show a profit by the
end of the next year, the bank would no longer provide an overdraft facility.
To prove to the bank that, based upon the past years performance, the company would
start to make a profit at the end of the next year, the =TREND() function was used.
The historical data for the past year was entered, months 1 to 12.
The months to predict were entered, 13 to 24.
The =TREND() function shows that it will be month 22 before the company make a profit.
Historical Data Predicted Values
Month Profit Month Profit
1 -£5,000 13 -£2,226 {=TREND(C41:C52,B41:B52,E41:E52)}
2 -£4,800 14 -£1,968 The
3 -£4,600 15 -£1,709 same
4 -£4,750 16 -£1,451 !unction
5 -£4,800 17 -£1,193 used
6 -£4,500 18 -£935 in
7 -£4,000 19 -£676 all
8 -£3,800 20 -£418 cells
9 -£3,300 21 -£160 as
10 -£2,000 22 £98 an
11 -£2,500 23 £356 array
12 -£2,800 24 £615 !ormula
3o) $o Enter *n *rray For&ula
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TREND
Page 192 of 206
Select all the cells where the array is required, such as F41 to F52.
Type the formula such as =TREND(C41:C52,B41:B52,E41:E52), but do not press Enter.
Hold the Ctrl+Shift keys down.
Press Enter to enter the formula as an array.
A B C D E F G H I J
55
56
57
58
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TRÌM
Page 193 of 206
$H"'
Original Text Trimmed Text
ABCD ABCD =TRÌM(C4)
A B C D A B C D =TRÌM(C5)
Alan Jones Alan Jones =TRÌM(C6)
ABCD ABCD =TRÌM(C7)
!hat Doe# "t Do %
This function removes unwanted spaces from a piece of text.
The spaces before and after the text will be removed completely.
Multiple spaces within the text will be trimmed to a single space
.yntax
=TRÌM(TextToTrim)
For&atting
No special formatting is needed.
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
TRUNC
Page 194 of 206
$H,C
Number
1.47589 0 1 =TRUNC(C4,D4)
1.47589 1 1.4 =TRUNC(C5,D5)
1.47589 2 1.47 =TRUNC(C6,D6)
-1.47589 1 -1.4 =TRUNC(C7,D7)
-1.47589 2 -1.47 =TRUNC(C8,D8)
13643.48 -1 13640 =TRUNC(C9,D9)
13643.48 -2 13600 =TRUNC(C10,D10)
13643.48 -3 13000 =TRUNC(C11,D11)
!hat Doe# "t Do %
This function removes the decimal part of a number, it does not actually round the number.
.yntax
=TRUNC(NumberToTuncate,Precision)
For&atting
No special formatting is needed.
Precision
For
Truncation
Truncated
Number
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
UPPER
Page 195 of 206
,PPEH
Original Text Upper Case
alan jones ALAN JONES =UPPER(C4)
bob smith BOB SMÌTH =UPPER(C5)
carOl wiLLiamS CAROL WÌLLÌAMS =UPPER(C6)
cardiff CARDÌFF =UPPER(C7)
abc123 ABC123 =UPPER(C8)
!hat Doe# "t Do %
This function converts all characters in a piece of text to upper case.
.yntax
=UPPER(TextToConvert)
For&atting
No special formatting is needed.
Exa&ple
See the example for FREQUENCY.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VALUE
Page 196 of 206
(*9,E
Text Containing A Number Value
Annual turnover was £5000 Err:502 =VALUE(MÌD(C4,SEARCH("£",C4),99))
There was a 2% increase in sales. #VALUE!
There was a 50% increase in sales. #VALUE!
A 100% increase was achieved. #VALUE!
Only a 2% increase in sales. #VALUE!
Approx 50% increase in sales. #VALUE!
There was a 100% increase in sales. #VALUE! * See explanation below.
The winning time was 1:30 seconds. #VALUE! =VALUE(MÌD(C14,SEARCH("??:??",C14),5))
The winning time was 1:30 seconds. #VALUE! =VALUE(MÌD(C15,SEARCH("??:??",C15),5))
The winning time was 10:30 seconds. #VALUE! =VALUE(MÌD(C16,SEARCH("??:??",C16),5))
The winning time was 0:30 seconds. #VALUE! =VALUE(MÌD(C17,SEARCH("??:??",C17),5))
!hat Doe# "t Do %
This function converts a piece of text which resembles a number into an actual value.
Ìf the number in the middle of a long piece of text it will have to be extracted using other
text functions such as =SEARCH(), =MÌD(), =FÌND(), =SUBSTÌTUTE, =LEFT() or =RÌGHT().
.yntax
=VALUE(TextToConvert)
For&atting
No special formatting is needed.
The result will be shown as a value, based upon the original text.
Ìf the £ sign is included in the text it will be ignored.
Ìf the % sign is included in the text, the result will be a decimal fraction which can then
be formatted as a percentage.
Ìf the original text format appears as a time hh:mm the result will be a time.
The same will be true for other recognised formats.
Explanation o- -or&ula #ho)n a1ove.
To extract the values from the following text is complicated!
The actual percentage value is of variable length, it can be either one, two or three digits long.
The only way to identify the value is the fact it always ends with the % sign.
There is no way to identify the beginning of the value, other than it is preceded by a space.
The main problem is calculating the length of the value to extract.
Ìf the extraction assumes the maximum length of three digits and the % sign, errors will occur
when the percentage is only one digit long, as alphabetic characters will be included.
To get around the problem the =SUBSTÌTUTE() function was used to increase the size of the
spaces in the text.
Now when the extraction takes place any unnecessary characters will be spaces which are
ignored by the =VALUE() function.
There was a 2% increase in sales. #VALUE!
There was a 50% increase in sales. #VALUE!
There was a 100% increase in sales. #VALUE!
=VALUE(MÌD(SUBSTÌTUTE(C52," "," "),SEARCH("???%",SUBSTÌTUTE(C52," "," ")),4))
=(*9,E(MÌD(SUBSTÌTUTE(C11," "," "),SEARCH("???%",SUBSTÌTUTE(C11," "," ")),4))
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VAR
Page 197 of 206
(*H
Values Values Values
10 10 10
10 10 11
9 11 9
10 10 12
0.25 0.25 1.6666667
=VAR(C4:C7) =VAR(E4:E7) =VAR(G4:G7)
!hat Doe# "t Do %
This function calculates the sample population variance of a list of values.
A sample population is used when the list of values represents a sample of a population.
.yntax
=VAR(Range1,Range2,Range3 through to Range30)
For&atting
No special formatting is needed.
Exa&ple
The table below was used by a company interested in buying a new machine
to pack washing powder.
Three machines were short listed and allow to run for a day.
At the end of the day four boxes of soap powder were picked at random from the production
of each machine.
The boxes were weighed and the =VAR() function used as these boxes only represented
a sample of the complete days production.
The machine with the smallest variance was the most consistent.
Soap Powder Box Filling Machine Test Results
Test 1 Test 2 Test 3 Test 4 Variance
Machine 1 1.4 1.5 1.6 1.5 0.0067 =VAR(D34:G34)
Machine 2 1.5 1.5 1.4 1.5 0.0025 =VAR(D35:G35)
Machine 3 1.5 1.6 1.7 1.8 0.0167 =VAR(D36:G36)
The smallest variance is : 0.0025 =MÌN(H34:H36)
The machine with the smallest variance is : Machine 2
=ÌNDEX(C34:C36,MATCH(MÌN(H34:H36),H34:H36,0))
Explanation o- -or&ula2
This finds the lowest value. =MÌN(H34:H36)
This finds the position of the lowest value. =MATCH(MÌN(H34:H36),H34:H36,0)
This looks down the Machine column to =ÌNDEX(C34:C36,MATCH(MÌN(H34:H36),H34:H36,0))
find the machine name.
A B C D E F G H I J K
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VARP
Page 198 of 206
(*HP
Values Values Values
10 10 10
10 10 11
9 11 9
10 10 12
0.1875 0.1875 1.25
=VARP(C4:C7) =VARP(E4:E7) =VARP(G4:G7)
!hat Doe# "t Do %
This function calculates the variance of a list of values.
The variance is calculated on the basis that the values represent the entire population.
.yntax
=VARP(Range1,Range2,Range3 through to Range30)
For&atting
No special formatting is needed.
Exa&ple
The table below was used by a company interested in buying a new machine
to pack washing powder.
A trial run a just four boxes per machine were produced.
The boxes were weighed and the =VARP() function used as these boxes
represented the entire test run.
The machine with the smallest variance was the most consistent.
Soap Powder Box Filling Machine Test Results
Test 1 Test 2 Test 3 Test 4 Variance
Machine 1 1.4 1.5 1.6 1.5 0.0050 =VARP(D32:G32)
Machine 2 1.5 1.5 1.4 1.5 0.0019 =VARP(D33:G33)
Machine 3 1.5 1.6 1.7 1.8 0.0125 =VARP(D34:G34)
The smallest variance is : 0.0019 =MÌN(H32:H34)
The machine with the smallest variance is : Machine 2
=ÌNDEX(C32:C34,MATCH(MÌN(H32:H34),H32:H34,0))
Explanation o- -or&ula2
This finds the lowest value. =(MÌN(H32:H34)
This finds the position of the lowest value. =MATCH(MÌN(H32:H34),H32:H34,0)
This looks down the Machine column to =ÌNDEX(C32:C34,MATCH(MÌN(H32:H34),H32:H34,0))
find the machine name.
A B C D E F G H I J K
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VLOOKUP
Page 199 of 206
(9778,P
The column numbers are not needed.
they are part o! the illustration.
col 1 col col " col 4 col # col $
Jan 10 20 30 40 50
Feb 80 90 100 110 120
Mar 97 69 45 51 77
Type a month to look for : Feb
Which column needs to be picked out : 4
The result is : 100
=VLOOKUP(G11,C6:H8,G12,FALSE)
!hat Doe# "t Do %
This function scans down the row headings at the side of a table to find a specified item.
When the item is found, it then scans across to pick a cell entry.
.yntax
=VLOOKUP(ÌtemToFind,RangeToLookÌn,ColumnToPickFrom,SortedOrUnsorted)
The ÌtemToFind is a single item specified by the user.
The RangeToLookÌn is the range of data with the row headings at the left hand side.
The ColumnToPickFrom is how far across the table the function should look to pick from.
The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.
For&atting
No special formatting is needed.
Exa&ple 1
This table is used to find a value based on a specified name and month.
The =VLOOKUP() is used to scan down to find the name.
The problem arises when we need to scan across to find the month column.
To solve the problem the =MATCH() function is used.
The =MATCH() looks through the list of names to find the month we require. Ìt then calculates
the position of the month in the list. Unfortunately, because the list of months is not as wide
as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is
added to compensate.
The =VLOOKUP() now uses this =MATCH() number to look across the columns and
picks out the correct cell entry.
The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the
row headings are not sorted.
Jan Feb Mar
Bob 10 80 97
Eric 20 90 69
Alan 30 100 45
Carol 40 110 51
David 50 120 77
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VLOOKUP
Page 200 of 206
Type a name to look for : eric
Type a month to look for : mar
The result is : 69
=VLOOKUP(F56,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)
Exa&ple 2
This example shows how the =VLOOKUP() is used to pick the cost of a spare part for
different makes of cars.
The =VLOOKUP() scans down row headings in column F for the spare part entered in column C.
When the make is found, the =VLOOKUP() then scans across to find the price, using the
result of the =MATCH() function to find the position of the make of car.
The functions use the absolute ranges indicated by the dollar symbol . This ensures that
when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do
not change.
Maker Spare Cost Lookup Table
Vauxhall Ìgnition £50 Vauxhall Ford VW
VW GearBox £600 GearBox 500 450 600
Ford Engine £1,200 Engine 1000 1200 800
VW Steering £275 Steering 250 350 275
Ford Ìgnition £70 Ìgnition 50 70 45
Ford CYHead £290 CYHead 300 290 310
Vauxhall GearBox £500
Ford Engine £1,200
=VLOOKUP(C81,F75:Ì79,MATCH(B81,G74:Ì74,0)+1,FALSE)
Exa&ple 4
Ìn the following example a builders merchant is offering discount on large orders.
The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the Total.
All the calculations take place in the Orders Table.
The name of the Ìtem is typed in column C of the Orders Table.
The Unit Cost of the item is then looked up in the Unit Cost Table.
The FALSE option has been used at the end of the function to indicate that the product
names down the side of the Unit Cost Table are not sorted.
Using the FALSE option forces the function to search for an exact match. Ìf a match is
not found, the function will produce an error.
=VLOOKUP(C126,C114
116,2,FALSE)The discount is then looked up in the Discount Table
Ìf the Quantity Ordered matches a value at the side of the Discount Table the =VLOOKUP will
look across to find the correct discount.
The TRUE option has been used at the end of the function to indicate that the values
down the side of the Discount Table are sorted.
Using TRUE will allow the function to make an approximate match. Ìf the Quantity Ordered does
not match a value at the side of the Discount Table, the next lowest value is used.
Trying to match an order of 125 will drop down to 100, and the discount from
A B C D E F G H I J
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
VLOOKUP
Page 201 of 206
the 100 row is used.
=VLOOKUP(D126,F114:Ì116,MATCH(C126,G113:Ì113,0)+1,TRUE)
Discount Table
Unit Cost Table Bric+ !oo0 Ila##
Bric+ £2 1 0% 0% 0%
!oo0 £1 100 6% 3% 12%
Ila## £3 400 8% 5% 15%
Orders Table
Ìtem Units Unit Cost Discount Total
Brick 100 £2 6% £188
Wood 200 £1 3% £194
Glass 150 £3 12% £396
Brick 225 £2 6% £423
Wood 50 £1 0% £50
Glass 500 £3 15% £1,275
Formula for :
Unit Cost =VLOOKUP(C126,C114
116,2,FALSE)Discount =VLOOKUP(D126,F114:Ì116,MATCH(C126,G113:Ì113,0)+1,TRUE)
Total =(D126*E126)-(D126*E126*F126)
A B C D E F G H I J
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
WEEKDAY
Page 202 of 206
!EE8D*N
Date Weekday
Thu 01-Jan-98 5 =WEEKDAY(C4)
Thu 01-Jan-98 5 =WEEKDAY(C5)
Thu 01-Jan-98 5 =WEEKDAY(C6,1)
Thu 01-Jan-98 4 =WEEKDAY(C7,2)
Thu 01-Jan-98 3 =WEEKDAY(C8,3)
!hat Doe# "t Do%
This function shows the day of the week from a date.
.yntax
=WEEKDAY(Date,Type)
Type : This is used to indicate the week day numbering system.
1 : will set Sunday as 1 through to Saturday as 7
2 : will set Monday as 1 through to Sunday as 7.
3 : will set Monday as 0 through to Sunday as 6.
Ìf no number is specified, Excel will use 1.
For&atting
The result will be shown as a normal number.
Exa&ple
The following table was used by a hotel which rented a function room.
The hotel charged different rates depending upon which day of the week the booking was for.
The Booking Date is entered.
The Actual Day is calculated.
The Booking Cost is picked from a list of rates using the =LOOKUP() function.
Booking Date Actual Day Booking Cost
7-Jan-98 Wednesday £ 30.00
=LOOKUP(WEEKDAY(C34),C39
45)Booking Rates
Day Of Week Cost
1 £50
2 £25
3 £25
4 £30
5 £40
6 £50
7 £100
To show the result as the name of the day, use For&at, Cell#, Cu#to& and set
the $ype to 000 or 0000.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
WORKDAY
Page 203 of 206
!7H8D*N
StartDate Days Result
1-Jan-98 28 35836 =WORKDAY(D4,E4)
1-Jan-98 28 10-Feb-98 =WORKDAY(D5,E5)
!hat Doe# "t Do%
Use this function to calculate a past or future date based on a starting date and a
specified number of days. The function excludes weekends and holidays and can
therefore be used to calculate delivery dates or invoice dates.
.yntax
=WORKDAY(StartDate,Days,Holidays)
For&atting
The result will normally be shown as a number which can be formatted to a
normal date by using Format,Cells,Number,Date.
Exa&ple
The following example shows how the function can be used to calculate delivery dates
based upon an initial Order Date and estimated Delivery Days.
Order Date Delivery Days Delivery Date
Mon 02-Feb-98 2 Wed 04-Feb-98
Tue 15-Dec-98 28 Tue 26-Jan-99
=WORKDAY(D25,E25,D28
32)Holidays
Bank Holiday Fri 01-May-98
Xmas Fri 25-Dec-98
New Year Wed 01-Jan-97
New Year Thu 01-Jan-98
New Year Fri 01-Jan-99
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
YEAR
Page 204 of 206
NE*H
Date Year
### 1998 =YEAR(C4)
!hat Doe# "t Do%
This function extracts the year number from a date.
.yntax
=YEAR(Date)
For&atting
The result is shown as a number.
A B C D E F G H I J
1
2
3
4
5
6
7
8
9
10
11
12
13
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
YEARFRAC
Page 205 of 206
NE*HFH*C
Start Date End Date Fraction
1-Jan-98 1-Apr-98 0.25 =YEARFRAC(C4,D4)
1-Jan-98 31-Dec-98 1 =YEARFRAC(C5,D5)
1-Jan-98 1-Apr-98 25% =YEARFRAC(C6,D6)
!hat Doe# "t Do%
This function calculates the difference between two dates and expresses the result
as a decimal fraction.
.yntax
=YEARFRAC(StartDate,EndData,Basis)
Basis : Defines the calendar system to be used in the function.
0 : or omitted USA style 30 days per month divided by 360.
1 : 29 or 30 or 31 days per month divided by 365.
2 : 29 or 30 or 31 days per month divided by 360.
3 : 29 or 30 0r 31 days per month divided by 365.
4 : European 29 or 30 or 31 days divided by 360.
For&atting
The result will be shown as a decimal fraction, but can be formatted as a percent.
Exa&ple
The following table was used by a company which hired people on short term contracts
for a part of the year.
The Pro Rata Salary which represents the annual salary is entered.
The Start and End dates of the contract are entered.
The =YEARFRAC() function is used to calculate Actual Salary for the portion of the year.
Start End Pro Rata Salary Actual Salary
1-Jan-98 31-Dec-98 £12,000 £12,000 =YEARFRAC(B32,C32+1,4)*D32
1-Jan-98 31-Mar-98 £12,000 £3,000 =YEARFRAC(B33,C33+1,4)*D33
1-Jan-98 30-Jun-98 £12,000 £6,000 =YEARFRAC(B34,C34+1,4)*D34
Note
The extra 1 has been added to the End date to compensate for the fact that the =YEARFRAC()
function calculates from the Start date up to, but not including, the End date.
A B C D E F G H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Excel Function Dictionary
© 1998 - 2000 Peter Noneley
Project Dates
Page 206 of 206
ProCect Date# 3ou#e Buil0ing
Target Delivery Tue 27-Jan-98 Target Budget £ 12,000
Job Stage Start Date End Date Daily Cost Total
Survey Mon 05-Jan-98 5 Fri 09-Jan-98 £ 200 £ 1,000
Foundation Mon 12-Jan-98 4 Thu 15-Jan-98 £ 1,000 £ 4,000
Walls Fri 16-Jan-98 3 Tue 20-Jan-98 £ 800 £ 2,400
Roof Wed 21-Jan-98 6 Wed 28-Jan-98 £ 400 £ 2,400
Electrics Thu 29-Jan-98 4 Tue 03-Feb-98 £ 300 £ 1,200
Actual Delivery Tue 03-Feb-98 Total Cost £ 11,000
Against Target 5 days behind Budget % 92%
Total Days 22
Days
Required
A B C D E F G H I
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
doc_270069786.xls