MS Excel 2007 Formula Guide

Labs Exercises


You can calculate a persons age based on their birthday and todays date.


The calculation uses the DATEDIF() function.


The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.


(Makes you wonder what else Microsoft forgot to tell us!)



Birth date : 29-Apr-73



Years lived : 36 =DATEDIF(C8,TODAY(),"y")


and the months : 2 =DATEDIF(C8,TODAY(),"ym")


and the days : 10 =DATEDIF(C8,TODAY(),"md")



You can put this all together in one calculation, which creates a text version.


Age is 36 Years, 2 Months and 10 Days


="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and
"&DATEDIF(C8,TODAY(),"md")&" Days"




Another way to calculate age


This method gives you an age which may potentially have decimal places representing the months.


If the age is 20.5, the .5 represents 6 months.



Birth date : 1-Jan-60



Age is : 49.52 =(TODAY()-C23)/365.25




Number Absolute

Value

10 10 =ABS(C4)


-10 10 =ABS(C5)


1.25 1.25 =ABS(C6)


-1.25 1.25 =ABS(C7)



What Does it Do
?


This function calculates the value of a number, irrespective of whether it is positive
or negative.



Syntax


=ABS(CellAddress or Number)



Formatting


The result will be shown as a number, no special formatting is
needed.




Item
s To
Test
Resu
lt


500 800
TRU
E =AND(C4>=100,D4>=100)


500 25
FAL
SE =AND(C5>=100,D5>=100)


25 500
FAL
SE =AND(C6>=100,D6>=100)


12
TRU
E =AND(D7>=1,D7<=52)



What Does It
Do?


This function tests two or more conditions to see if they are all true.


It can be used to test that a series of numbers meet certain conditions.


It can be used to test that a number or a date falls between an upper and lower limit.


Normally the AND() function would be used in conjunction with a function such as =IF().



Syntax



=AND(Test1,T
est2)


Note that there can be up to 30 possible tests.



Formatting


When used by itself it will show TRUE or FALSE.



Example 1


The following example shows a list of examination results.


The teacher wants to find the pupils who scored above average in all three exams.


The =AND() function has been used to test that each score is above the average.


The result of TRUE is shown for pupils who have scored above average in all three exams.



Name
Mat
hs
Engli
sh
Physi
cs Passed


Alan 80 75 85 TRUE


Bob 50 30 40 FALSE


Carol 60 70 50 FALSE


David 90 85 95 TRUE


Eric 20 30
Abse
nt FALSE


Fred 40 60 80 FALSE


Gail 10 90 80 FALSE


Harry 80 70 60 TRUE


Ian 30 10 20 FALSE


Janice 10 20 30 FALSE


=AND(C38>=AVERAGE($C$29:$C$38),D38>=AVERAGE($D$29:$D$38),E3
8>=AVERAGE($E$29:$E$38))



Averag
es 47 54 60




Instead of using the AutoSum button from
the toolbar,


you can press Alt and = to achieve the
same result.



Try it here :


Move to a blank cell in the Total row or column,
then press Alt and =.


or


Select a row, column or all cells and then press Alt
and =.



Jan Feb Mar Total


North 10 50 90 150


South 20 60 100 180


East 30 70 200 330


West 40 80 300 420


Total 100 260 690 1050






Mon Tue Wed Thu Fri Sat Sun Average


Temp 30 31 32 29 26 28 27 29 =AVERAGE(D4:J4)


Rain 0 0 0 4 6 3 1 2 =AVERAGE(D5:J5)



Mon Tue Wed Thu Fri Sat Sun Average


Temp 30 32 29 26 28 27 28.667 =AVERAGE(D8:J8)


Rain 0 0 4 6 3 1 2.3333 =AVERAGE(D9:J9)



Mon Tue Wed Thu Fri Sat Sun Average


Temp 30 No 32 29 26 28 27 28.667 =AVERAGE(D12:J12)


Rain 0 Reading 0 4 6 3 1 2.3333 =AVERAGE(D13:J13)



What Does It Do ?


This function calculates the average from a list of numbers.


If the cell is blank or contains text, the cell will not be used in the average calculation.


If the cell contains zero 0, the cell will be included in the average calculation.



Syntax


=AVERAGE(Range1,Range2,Range3... through to Range30)



Formatting


No special formatting is needed.



Note


To calculate the average of cells which contain text or blanks use =SUM() to get the total and


then divide by the count of the entries using =COUNTA().



Mon Tue Wed Thu Fri Sat Sun Average


Temp 30 No 32 29 26 28 27 24.571

=SUM(D31:J31)/COUNTA(D31:J31)


Rain 0 Reading 0 4 6 3 1 2

=SUM(D32:J32)/COUNTA(D32:J32)



Mon Tue Wed Thu Fri Sat Sun Average


Temp 30 32 29 26 28 27 28.667

=SUM(D35:J35)/COUNTA(D35:J35)


Rain 0 0 4 6 3 1 2.3333

=SUM(D36:J36)/COUNTA(D36:J36)




Further Usage





Sometimes you will need to use brackets, (also known as 'braces'),
in formula.


This is to ensure that the calculations are performed in the order that
you need.


The need for brackets occurs when you mix plus or minus with divide
or multiply.



Mathematically speaking the * and / are more important than + and -
.


The * and / operations will be calculated before + and - .



Example 1 : The wrong answer !



10


20


2


50 =C12+C13*C14



You may expect that 10 + 20 would equal 30


And then 30 * 2 would equal 60



But because the * is calculated first Excel sees the


calculation as 20 * 2 resulting in 40


And then 10 + 40 resulting in 50




Example 2 : The correct answer.



10


20


2


60 =(C27+C28)*C29



By placing brackets around (10+20) Excel performs this


part of the calulation first, resulting in 30


Then the 30 is multipled by 2 resulting in 60




This is the cell and
contents to test. 17.50%



The cell address. $D$3 =CELL("address",D3)


The column number. 4 =CELL("col",D3)


The row number. 3 =CELL("row",D3)


The actual contents of
the cell.
0.175 =CELL("contents",D3)


The type of entry in
the cell.
Shown as b for blank,
l for text, v for value.
v =CELL("type",D3)


The alignment of the
cell.
Shown as ' for left, ^
for centre, " for right.
Nothing is shown for
numeric entries.
=CELL("prefix",D3)


The width of the cell. 12 =CELL("width",D3)


The number format fo
the cell.
(See the table shown
below)
P2 =CELL("format",D3)


Formatted for braces (
) on positive values.
0 =CELL("parentheses",D3)

1 for yes, 0 for no.

Formatted for
coloured negatives.
1 for yes, 0 for no.
0 =CELL("color",D3)


The type of cell
protection.
1 for a locked, 0 for
unlocked.
1 =CELL("protect",D3)


The filename
containing the cell.
F:\NIM\[EXCEL_Formulae01.xls]CELL


=CELL("filename",D3)


What Does It Do ?


This function examines a cell and displays information about the contents, position and formatting.



Syntax


=CELL("TypeOfInfoRequired",CellToTest)


The TypeOfInfoRequired is a text entry which must be surrounded with quotes " ".



Formatting


No special formatting is needed.



Codes used to show the formatting of
the cell.



Numeric Format Code


General G


0 F0


#,##0 ,0


0.00 F2


#,##0.00 ,2


$#,##0_);($#,##0) C0


$#,##0_);[Red]($#,##0) C0-


$#,##0.00_);($#,##0.00) C2


$#,##0.00_);[Red]($#,##0.00) C2-


0% P0


0.00% P2


0.00E+00 S2


# ?/? or # ??/?? G


m/d/yy or m/d/yy h:mm or
mm/dd/yy. D4


d-mmm-yy or dd-mmm-yy D1


d-mmm or dd-mmm D2


mmm-yy D3


mm/dd D5


h:mm AM/PM D7


h:mm:ss AM/PM D6


h:mm D9


h:mm:ss D8




Example


The following example uses the =CELL() function as part of a formula which extracts the filename.



The name of the current file is : EXCEL_Formulae01.xls


=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)





ANSI
Number Character




65 A



=CHAR(G4)




66 B



=CHAR(G5)


169

=CHAR(G6)



What Does It Do?


This function converts a normal number to the character it represent in the ANSI


character set used by Windows.



Syntax


=CHAR(Number)


The Number must be between 1 and 255.



Formatting


The result will be a character with no special formatting.



Example


The following is a list of all 255 numbers and the characters they represent.


Note that most Windows based program may not display some of the special characters,

these will be displayed as a small box.



1 26

51 3 76 L 101 e 126 ~ 151 176 201 226 251


2 27

52 4 77 M 102 f 127 152 177 202 227 252


3 28

53 5 78 N 103 g 128 153 178 203 228 253


4 29

54 6 79 O 104 h 129 154 179 204 229 254


5 30 - 55 7 80 P 105 i 130 155 180 205 230 255


6 31 56 8 81 Q 106 j 131 156 181 206 231


7 32 57 9 82 R 107 k 132 157 182 207 232


8 33 ! 58 : 83 S 108 l 133 158 183 208 233


9

34 " 59 ; 84 T 109 m 134 159 184 209 234


10

35 # 60 < 85 U 110 n 135 160 185 210 235


11

36 $ 61 = 86 V 111 o 136 161 186 211 236


12 37 % 62 > 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 I 98 b 123 { 148 173 198 223 248


24

49 1 74 J 99 c 124 | 149 174 199 224 249


25

50 2 75 K 100 d 125 } 150 175 200 225 250



Note


Number 32 does not show as it is the SPACEBAR character.



Index
Value 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%)




What Does It
Do?


This function picks from a list of options based upon an Index value given to by the user.



Syntax


=CHOOSE(UserValue, Item1, Item2, Item3 through to Item29)



Formatting


No special formatting is required.



Example


The following table was used to calculate the medals for athletes taking part in a race.


The Time for each athlete is entered.


The =RANK() function calculates the finishing position of each athlete.


The =CHOOSE() then allocates the correct medal.


The =IF() has been used to filter out any positions above 3, as this would cause


the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it.



Name Time Position Medal


Alan 1:30 2 Silver

=IF(D30<=3,CHOOSE(D30,"Gold","Silver","Bronze"),"unplaced")


Bob 1:15 4 unplaced

=IF(D31<=3,CHOOSE(D31,"Gold","Silver","Bronze"),"unplaced")


Carol 2:45 1 Gold

=IF(D32<=3,CHOOSE(D32,"Gold","Silver","Bronze"),"unplaced")


David 1:05 5 unplaced

=IF(D33<=3,CHOOSE(D33,"Gold","Silver","Bronze"),"unplaced")


Eric 1:20 3 Bronze

=IF(D34<=3,CHOOSE(D34,"Gold","Silver","Bronze"),"unplaced")


=RANK(C34,C30:C34)



Nam
e 1
Name
2
Concaten
ated Text


Alan Jones
AlanJone
s

=CONCATENAT
E(C4,D4)


Bob
Willia
ms
BobWillia
ms

=CONCATENAT
E(C5,D5)


Caro
l
Davie
s
CarolDavi
es

=CONCATENAT
E(C6,D6)


Alan Jones
Alan
Jones
=CONCATENATE(C7,"
",D7)


Bob
Willia
ms
Williams,
Bob
=CONCATENATE(D8,",
",C8)


Caro
l
Davie
s
Davies,
Carol
=CONCATENATE(D9,",
",C9)







What Does
It Do?


This function joins separate pieces of text
into one item.



Synt
ax



=CONCATENATE(Text1,Text2,T
ext3...Text30)


Up to thirty pieces of text can be
joined.



Formatting


No special formatting is needed, the result will be
shown as normal text.



Note


You can achieve the same result by using
the & operator.



Nam
e 1
Name
2
Concaten
ated Text


Alan Jones
AlanJone
s

=C25&D2
5


Bob
Willia
ms
BobWillia
ms

=C26&D2
6


Caro
l
Davie
s
CarolDavi
es

=C27&D2
7


Alan Jones
Alan
Jones
=C28&"
"&D28


Bob Willia Williams, =D29&",

ms Bob "&C29

Caro
l
Davie
s
Davies,
Carol
=D30&",
"&C30






Amount
To
Convert
Converting
From
Converting
To
Converted
Amount


1 in cm 2.54 =CONVERT(C4,D4,E4)


1 ft m 0.3048 =CONVERT(C5,D5,E5)


1 yd m 0.9144 =CONVERT(C6,D6,E6)



1 yr day 365.25 =CONVERT(C8,D8,E8)


1 day hr 24 =CONVERT(C9,D9,E9)


1.5 hr mn 90 =CONVERT(C10,D10,E10)

0.5 mn sec 30 =CONVERT(C11,D11,E11)
What Does It Do ?
This function converts a value measure in one type of unit, to the same value expressed
in a different type of unit, such as Inches to Centimetres.

Syntax
=CONVERT(AmountToConvert,UnitToConvertFrom,UnitToConvertTo)

Formatting
No special formatting is needed.

Example
The following table was used by an Import / Exporting company to convert the weight
and size of packages from old style UK measuring system to European system.



Pounds Ounces Kilograms


Weight 5 3 2.35301



=CONVERT(D28,"lbm","kg")+CONVERT(E28,"ozm","kg")



Feet Inches Metres


Height 12 6 3.81


Length 8 3 2.5146


Width 5 2 1.5748


=CONVERT(D34,"ft","m")+CONVERT(E34,"in","m")

Abbreviations
This is a list of all the possible abbreviations which can be used to denote measuring systems.

Weight & Mass

Distance
Gram g

Meter m
Kilogram kg

Statute mile mi

Slug sg

Nautical
mile Nmi
Pound mass lbm

Inch in
U (atomic
mass) u

Foot ft
Ounce mass ozm

Yard yd


Angstrom ang

Time

Pica (1/72
in.) Pica
Year yr
Day day

Pressure
Hour hr

Pascal Pa
Minute mn

Atmosphere atm

Second sec

mm of
Mercury mmHg

Temperature

Liquid
Degree Celsius C

Teaspoon tsp

Degree
Fahrenheit F

Tablespoon tbs
Degree Kelvin K

Fluid ounce oz


Cup cup
Force

Pint pt
Newton N

Quart qt
Dyne dyn

Gallon gal
Pound force lbf

Liter l

Energy

Power
Joule J

Horsepower HP
Erg e

Watt W
Thermodynamic
calorie c
IT calorie cal

Magnetism
Electron volt eV

Tesla T
Horsepower-
hour HPh

Gauss ga
Watt-hour Wh
Foot-pound flb
BTU BTU


These characters can be used as a prefix to access further units of measure.
Using "c" as a prefix to meters "m" will allow centimetres "cm" to be calculated.

Prefix Multiplier Abbreviation

Prefix Multiplier Abbreviation
exa 1.00E+18 E

deci 1.00E-01 d
peta 1.00E+15 P

centi 1.00E-02 c
tera 1.00E+12 T

milli 1.00E-03 m
giga 1.00E+09 G

micro 1.00E-06 u
mega 1.00E+06 M

nano 1.00E-09 n
kilo 1.00E+03 k

pico 1.00E-12 p
hecto 1.00E+02 h

femto 1.00E-15 f

dekao 1.00E+01 e

atto 1.00E-18 a





Entries To Be Counted Count


10 20 30 3 =COUNT(C4:E4)


10 0 30 3 =COUNT(C5:E5)


10 -20 30 3 =COUNT(C6:E6)


10 1-Jan-88 30 3 =COUNT(C7:E7)


10 21:30 30 3 =COUNT(C8:E8)


10 0.756813 30 3 =COUNT(C9:E9)


10 30 2

=COUNT(C10:E10)


10 Hello 30 2

=COUNT(C11:E11)


10 #DIV/0! 30 2

=COUNT(C12:E12)



What Does It Do
?


This function counts the number of numeric entries in a list.


It will ignore blanks, text and errors.



Syntax


=COUNT(Range1,Range2,Range3... through to Range30)



Formatting


No special formatting is needed.



Example


The following table was used by a builders merchant to calculate the number of sales


for various products in each month.



Item Jan Feb Mar


Bricks 1,000


Wood 5,000


Glass 2,000 1,000


Metal 1,000


Count 3 2 0



=COUNT(D29:D32)




Entries To Be Counted Count


10 20 30 3 =COUNTA(C4:E4)


10 0 30 3 =COUNTA(C5:E5)


10 -20 30 3 =COUNTA(C6:E6)


10 1-Jan-88 30 3 =COUNTA(C7:E7)


10 21:30 30 3 =COUNTA(C8:E8)


10 0.068395 30 3 =COUNTA(C9:E9)


10 30 2 =COUNTA(C10:E10)


10 Hello 30 3 =COUNTA(C11:E11)


10 #DIV/0! 30 3 =COUNTA(C12:E12)



What Does It Do
?


This function counts the number of numeric or text entries in a list.


It will ignore
blanks.



Syntax


=COUNTA(Range1,Range2,Range3... through to Range30)



Formatting


No special formatting is needed.



Example


The following table was used by a school to keep track of the examinations taken by each pupil.


Each exam passed was graded as 1, 2 or 3.


A failure was entered as Fail.



The school needed to known how many pupils sat each exam.


The school also needed to know how many exams were taken by each pupil.



The =COUNTA() function has been used because of its ability to count text and numeric entries.



Maths English Art History

Exams
Taken
By Each
Pupil


Alan Fail 1

2


Bob 2 1 3

3


Carol 1 1 1

3


David Fail Fail

2


Elaine 1 3 2 Fail

4



=COUNTA(D39:G39)


How many pupils sat each Exam.


Maths English Art History


4 3 5 2



=COUNTA(D35:D39)






Range To
Test

Blanks


1

2 =COUNTBLANK(C4:C11)


Hello


3


0





1-Jan-98





5



What Does It Do ?


This function counts the number of blank cells in a range.



Syntax


=COUNTBLANK(RangeToTest)



Formatting


No special formatting is needed.



Example


The following table was used by a company which was balloting its workers on whether


the company should have a no smoking policy.


Each of the departments in the various factories were questioned.


The response to the question could be Y or N.


As the results of the vote were collated they were entered in to the table.


The =COUNTBLANK() function has been used to calculate the number of departments which


have no yet registered a vote.



Admin Accounts Production Personnel


Factory
1 Y N


Factory
2 Y Y N


Factory
3


Factory
4 N N N


Factory Y Y

5

Factory
6 Y Y Y N


Factory
7 N Y


Factory
8 N N Y Y


Factory
9 Y


Factory
10 Y N Y




Votes not vet
registered : 16 =COUNTBLANK(C32:F41)



Votes for Yes : 14 =COUNTIF(C32:F41,"Y")



Votes for No : 10 =COUNTIF(C32:F41,"N")





Item Date Cost


Brakes 1-Jan-98 80


Tyres
10-May-
98 25


Brakes
1-Feb-
98 80


Service
1-Mar-
98 150


Service 5-Jan-98 300


Window 1-Jun-98 50


Tyres 1-Apr-98 200


Tyres 1-Mar- 100

98

Clutch
1-May-
98 250



How many Brake Shoes Have been
bought. 2 =COUNTIF(C4:C12,"Brakes")


How many Tyres have been
bought. 3 =COUNTIF(C4:C12,"Tyres")


How many items cost 100 or
above. 5 =COUNTIF(E4:E12,">=100")



Type the name of the item to
count. service 2 =COUNTIF(C4:C12,E18)




What Does It Do ?


This function counts the number of items which match criteria set by the user.



Syntax


=COUNTIF(RangeOfThingsToBeCounted,CriteriaToBeMatched)


The criteria can be typed in any of the following
ways.


To match a specific number type the number, such as =COUNTIF(A1:A5,100)


To match a piece of text type the text in quotes, such as
=COUNTIF(A1:A5,"Hello")


To match using operators surround the expression with quotes, such as
=COUNTIF(A1:A5,">100")


Formatting


No special formatting is
needed.




Day Month Year Date


25 12 99 12/25/99

=DATE(E4,D4,C4)


25 12 99 25-Dec-99

=DATE(E5,D5,C5)


33 12 99 January 2, 2000

=DATE(E6,D6,C6)



What Does It Do?


This function creates a real date by using three normal numbers typed into
separate cells.



Syntax



=DATE(year,month,day)



Formatting


The result will normally be displayed in the dd/mm/yy format.


By using the Format,Cells,Number,Date command the format can be
changed.




FirstDate SecondDate Interval Difference


1-Jan-60 10-May-70 days 3782

=DATEDIF(C4,D4,"d")


1-Jan-60 10-May-70 months 124 =DATEDIF(C5,D5,"m")

1-Jan-60 10-May-70 years 10

=DATEDIF(C6,D6,"y")


1-Jan-60 10-May-70 yeardays 130 =DATEDIF(C7,D7,"yd")

1-Jan-60 10-May-70 yearmonths 4 =DATEDIF(C8,D8,"ym")

1-Jan-60 10-May-70 monthdays 9 =DATEDIF(C9,D9,"md")


What Does It Do?


This function calculates the difference between two dates.


It can show the result in weeks, months or years.



Syntax


=DATEDIF(FirstDate,SecondDate,"Interval")


FirstDate : This is the earliest of the two dates.


SecondDate : This is the most recent of the two
dates.


"Interval" : This indicates what you want to
calculate.


These are the available intervals.


"d"
Days between the two
dates.


"m" Months between the two dates.


"y"
Years between the two
dates.


"yd"
Days between the dates, as if the dates were in the same
year.


"ym"
Months between the dates, as if the dates were in the same
year.


"md"
Days between the two dates, as if the dates were in the same month
and year.


Formatting


No special formatting is needed.






Birth date : 1-Jan-60



Years lived
: 49 =DATEDIF(C8,TODAY(),"y")


and the
months : 6 =DATEDIF(C8,TODAY(),"ym")


and the 7 =DATEDIF(C8,TODAY(),"md")

days :


You can put this all together in one calculation, which creates a text version.


Age is 49 Years, 6 Months and 7 Days


="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and
"&DATEDIF(C8,TODAY(),"md")&" Days"





Date Date Value


25-dec-99 36519

=DATEVALUE(C4)


25/12/99 #VALUE!

=DATEVALUE(C5)


25-dec-99 36519

=DATEVALUE(C6)


25/12/99 #VALUE!

=DATEVALUE(C7)



What Does It Do?


The function is used to convert a piece of text into a date which can be used in
calculations.


Dates expressed as text are often created when data is imported from other programs,
such as


exports from mainframe computers.



Syntax



=DATEVALUE(text)



Formatting


The result will normally be shown as a number which represents the date. This number
can


be formatted to any of the normal date formats by using

Format,Cells,Number,Date.


Example


The example uses the =DATEVALUE and the =TODAY functions to calculate the number
of


days remaining on a property lease.



The =DATEVALUE function was used because the date has been entered in the cell as


a piece of text, probably after being imported from an external program.



Property Ref. Expiry Date Days Until Expiry


BC100 25-dec-99 -3483


FG700 10-july/99 -3651


TD200 13-sep-98 -3951


HJ900 30/5/2000 #VALUE!


=DATEVALUE(E32)-
TODAY()




This is
the
Database
range.


Product Wattage Life Hours Brand
Unit
Cost
Box
Quantity
Boxes
In
Stock
Value Of
Stock


Bulb 200 3000 Horizon 4.50 4 3 54.00


Neon 100 2000 Horizon 2.00 15 2 60.00


Spot 60 0.00


Other 10 8000 Sunbeam 0.80 25 6 120.00


Bulb 80 1000 Horizon 0.20 40 3 24.00


Spot 100 unknown Horizon 1.25 10 4 50.00


Spot 200 3000 Horizon 2.50 15 0 0.00


Other 25 unknown Sunbeam 0.50 10 3 15.00


Bulb 200 3000 Sunbeam 5.00 3 2 30.00


Neon 100 2000 Sunbeam 1.80 20 5 180.00


Bulb 100 unknown Sunbeam 0.25 10 5 12.50


Bulb 10 800 Horizon 0.20 25 2 10.00


Bulb 60 1000 Sunbeam 0.15 25 0 0.00


Bulb 80 1000 Sunbeam 0.20 30 2 12.00


Bulb 100 2000 Horizon 0.80 10 5 40.00


Bulb 40 1000 Horizon 0.10 20 5 10.00



To calculate the Average cost of a particular Brand of bulb.



Brand These two cells are the Criteria range.


Type the brand name
: sunbeam



The Average cost of
sunbeam is : 1.24 =DAVERAGE(B3:I19,F3,E23:E24)



What Does It Do?

This function examines a list of information and produces and average.



Syntax

=DAVERAGE(DatabaseRange,FieldName,CriteriaRange)


The DatabaseRange is the entire list of information you need to examine, including the


field names at the top of the columns.


The FieldName is the name, or cell, of the values to be averaged, such as "Unit Cost" or F3.

The CriteriaRange is made up of two types of information.


The first set of information is the name, or names, of the Fields(s) to be used as the basis

for selecting the records, such as the category Brand or Wattage.


The second set of information is the actual record, or records, which are to be selected, such

as Horizon as a brand name, or 100 as the wattage.



Formatting

No special formatting is needed.



Examples


The average Unit Cost of a particular Product of a particular Brand.



Product Brand


Bulb Horizon



The average of
Horizon Bulb is : 1.16 =DAVERAGE(B3:I19,F3,E49:F50)



This is the same calculation but using the actual name "Unit Cost" instead of the cell address.


1.16
=DAVERAGE(B3:I19,"Unit
Cost",E49:F50)



The average Unit Cost of a Bulb equal to a particular Wattage.


Product Wattage


Bulb 100



Average of Bulb 100
is : 0.53
=DAVERAGE(B3:I19,"Unit
Cost",E60:F61)



The average Unit Cost of a Bulb less then a particular Wattage.


Product Wattage


Bulb <100



Average of Bulb
<100 is : 0.17
=DAVERAGE(B3:I19,"Unit
Cost",E67:F68)




Full Date The Day


25-Dec-
98 25 =DAY(C4)


8-Jul-09 Sun 8 =DAY(C5)


8-Jul-09 8 =DAY(C6)



What Does
It Do?


This function extracts the day of the month from a complete date.



Syntax



=DAY(value)



Formatting


Normally the result will be a number, but this can be formatted to show the actual


day of the week by using Format,Cells,Number,Custom and using the code ddd or
dddd.



Example


The =DAY function has been used to calculate the name of the day for your birthday.




Please enter your date of birth in
the format dd/mm/yy : 3/25/1962


You were born on :
Wednesday
25

=DAY(F21)





StartDate EndDate Days * See the Note below.

Between

1-Jan-98 5-Jan-98 4 =DAYS360(C4,D4,TRUE)


1-Jan-98 1-Feb-98 30 =DAYS360(C5,D5,TRUE)


1-Jan-98
31-Mar-
98 89 =DAYS360(C6,D6,TRUE)


1-Jan-98
31-Dec-
98 359 =DAYS360(C7,D7,TRUE)



What Does It Do?


Shows the number of days between two dates based on a 360-day year (twelve 30-day
months).

Use this function if your accounting system is based on twelve 30-day months.



Syntax


=DAYS360(StartDate,EndDate,TRUE of FALSE)


TRUE : Use this for European accounting
systems.


FALSE : Use this for USA accounting systems.



Formatting


The result will be shown as a
number.



Note


The calculation does not include the last day. The result of using 1-Jan-98 and
5-Jan-98 will


give a result of 4. To correct this add 1 to the result.
=DAYS360(Start,End,TRUE)+1



This is
the
Database
range.


Product Wattage Life Hours Brand
Unit
Cost
Box
Quantity
Boxes
In
Stock
Value Of
Stock


Bulb 200 3000 Horizon 4.50 4 3 54.00


Neon 100 2000 Horizon 2.00 15 2 60.00


Spot 60 0.00


Other 10 8000 Sunbeam 0.80 25 6 120.00


Bulb 80 1000 Horizon 0.20 40 3 24.00


Spot 100 unknown Horizon 1.25 10 4 50.00


Spot 200 3000 Horizon 2.50 15 1 37.50


Other 25 unknown Sunbeam 0.50 10 3 15.00


Bulb 200 3000 Sunbeam 5.00 3 2 30.00


Neon 100 2000 Sunbeam 1.80 20 5 180.00


Bulb 100 unknown Sunbeam 0.25 10 5 12.50


Bulb 10 800 Horizon 0.20 25 2 10.00


Bulb 60 1000 Sunbeam 0.15 25 1 3.75
` Bulb 80 1000 Sunbeam 0.20 30 2 12.00


Bulb 100 2000 Horizon 0.80 10 5 40.00


Bulb 40 1000 Horizon 0.10 20 5 10.00



Count the number of products of a particular Brand which have a Life Hours rating.



Brand These two cells are the Criteria range.


Type the brand name : Horizon



The COUNT value of
Horizon is : 7

=DCOUNT(B3:I19,D3,E23:E24)



What Does It Do ?


This function examines a list of information and counts the values in a specified column.


It can only count values, the text items and blank cells are ignored.



Syntax


=DCOUNT(DatabaseRange,FieldName,CriteriaRange)


The DatabaseRange is the entire list of information you need to examine, including the


field names at the top of the columns.


The FieldName is the name, or cell, of the values to Count, such as "Value Of Stock" or I3.


The CriteriaRange is made up of two types of information.


The first set of information is the name, or names, of the Fields(s) to be used as the basis


for selecting the records, such as the category Brand or Wattage.


The second set of information is the actual record, or records, which are to be selected, such


as Horizon as a brand name, or 100 as the wattage.



Formatting


No special formatting is needed.



Examples



The count of a particular product, with a specific number of boxes in stock.



Product
Boxes In
Stock


Bulb 5



The number of products
is : 3

=DCOUNT(B3:I19,H3,E50:F51)



This is the same calculation but using the name "Boxes In Stock" instead of the cell address.



3 =DCOUNT(B3:I19,"Boxes In Stock",E50:F51)



The count of the number of Bulb products equal to a particular Wattage.



Product Wattage


Bulb 100



The count is : 2 =DCOUNT(B3:I19,"Boxes In Stock",E61:F62)



The count of Bulb products between two Wattage values.



Product Wattage Wattage


Bulb >=80 <=100



The count is : 4 =DCOUNT(B3:I19,"Boxes In Stock",E68:G69)






Product Wattage Life Hours Brand
Unit
Cost
Box
Quantity
Boxes
In
Stock
Value
Of
Stock


Bulb 200 3000 Horizon 4.50 4 3 54.00


Neon 100 2000 Horizon 2.00 15 2 60.00


Spot 60 0.00


Other 10 8000 Sunbeam 0.80 25 6 120.00


Bulb 80 1000 Horizon 0.20 40 3 24.00


Spot 100 unknown Horizon 1.25 10 4 50.00


Spot 200 3000 Horizon 2.50 15 1 37.50


Other 25 unknown Sunbeam 0.50 10 3 15.00


Bulb 200 3000 Sunbeam 5.00 3 2 30.00


Neon 100 2000 Sunbeam 1.80 20 5 180.00


Bulb 100 unknown Sunbeam 0.25 10 5 12.50


Bulb 10 800 Horizon 0.20 25 2 10.00


Bulb 60 1000 Sunbeam 0.15 25 1 3.75


Bulb 80 1000 Sunbeam 0.20 30 2 12.00


Bulb 100 2000 Horizon 0.80 10 5 40.00


Bulb 40 1000 Horizon 0.10 20 5 10.00



Count the number of products of a particular Brand.



Brand These two cells are the Criteria range.


Type the brand name : Horizon



The COUNT value of
Horizon is : 8 =DCOUNTA(B3:I19,E3,E23:E24)



What Does It Do ?


This function examines a list of information and counts the non blank cells in a specified column.


It counts values and text items, but blank cells are ignored.



Syntax


=DCOUNTA(DatabaseRange,FieldName,CriteriaRange)


The DatabaseRange is the entire list of information you need to examine, including the


field names at the top of the columns.


The FieldName is the name, or cell, of the values to Count, such as "Value Of Stock" or I3.


The CriteriaRange is made up of two types of information.


The first set of information is the name, or names, of the Fields(s) to be used as the basis


for selecting the records, such as the category Brand or Wattage.


The second set of information is the actual record, or records, which are to be selected, such


as Horizon as a brand name, or 100 as the wattage.



Formatting


No special formatting is needed.



Examples



The count of a product with an unknown Life Hours value.



Product
Life
Hours


Bulb unknown



The number of products 1 =DCOUNTA(B3:I19,D3,E50:F51)

is :


This is the same calculation but using the name "Life Hours" instead of the cell address.



1
=DCOUNTA(B3:I19,"Life
Hours",E50:F51)



The count of the number of particular product of a specific brand.



Product Brand


Bulb Horizon



The count is : 5

=DCOUNTA(B3:I19,"Product",E61:F62)



The count of particular products from specific brands.



Product Brand


Spot Horizon


Neon Sunbeam



The count is : 3

=DCOUNTA(B3:I19,"Product",E68:F70)




This is
the
Database
range.

Product Wattage Life Hours Brand
Unit
Cost
Box
Quantity
Boxes
In
Stock
Value Of
Stock

Bulb 200 3000 Horizon 4.50 4 3 54.00

Neon 100 2000 Horizon 2.00 15 2 60.00

Spot 60 0.00

Other 10 8000 Sunbeam 0.80 25 6 120.00

Bulb 80 1000 Horizon 0.20 40 3 24.00

Spot 100 unknown Horizon 1.25 10 4 50.00

Spot 200 3000 Horizon 2.50 15 0 0.00

Other 25 unknown Sunbeam 0.50 10 3 15.00

Bulb 200 3000 Sunbeam 5.00 3 2 30.00

Neon 100 2000 Sunbeam 1.80 20 5 180.00

Bulb 100 unknown Sunbeam 0.25 10 5 12.50

Bulb 10 800 Horizon 0.20 25 2 10.00

Bulb 60 1000 Sunbeam 0.15 25 0 0.00

Bulb 80 1000 Sunbeam 0.20 30 2 12.00

Bulb 100 2000 Horizon 0.80 10 5 40.00

Bulb 40 1000 Horizon 0.10 20 5 10.00


To calculate largest Value Of Stock of a particular Brand of bulb.



Brand These two cells are the Criteria range.

Type the brand name
: Horizon



The MAX value of
Horizon is : 60.00 =DMAX(B3:I19,I3,E23:E24)



What Does It Do ?

This function examines a list of information and produces the largest value from a specified
column.


Syntax

=DMAX(DatabaseRange,FieldName,CriteriaRange)


The DatabaseRange is the entire list of information you need to examine, including the

field names at the top of the columns.


The FieldName is the name or cell, of the values to pick the Max from, such as "Value Of Stock"
or I3.

The CriteriaRange is made up of two types of information.


The first set of information is the name, or names, of the Fields(s) to be used as the basis

for selecting the records, such as the category Brand or Wattage.


The second set of information is the actual record, or records, which are to be selected, such

as Horizon as a brand name, or 100 as the wattage.



Formatting

No special formatting is needed.



Examples


The largest Value Of Stock of a particular Product of a particular Brand.


Product Brand


Bulb sunbeam



The largest value is : 30.00 =DMAX(B3:I19,I3,E49:F50)



This is the same calculation but using the name "Value Of Stock" instead of the cell address.


30.00
=DMAX(B3:I19,"Value Of
Stock",E49:F50)


The largest Value Of Stock of a Bulb equal to a particular Wattage.


Product Wattage


Bulb 100



The largest Value Of
Stock is : 40.00
=DMAX(B3:I19,"Value Of
Stock",E60:F61)


The largest Value Of Stock of a Bulb less than a particular Wattage.


Product Wattage


Bulb <100



The largest Value Of
Stock is : 24.00
=DMAX(B3:I19,"Value Of
Stock",E67:F68)




This is
the
Database
range.



Product Wattage Life Hours Brand
Unit
Cost
Box
Quantity
Boxes
In
Stock
Value Of
Stock


Bulb 200 3000 Horizon 4.50 4 3 54.00


Neon 100 2000 Horizon 2.00 15 2 60.00


Spot 60 0.00


Other 10 8000 Sunbeam 0.80 25 6 120.00


Bulb 80 1000 Horizon 0.20 40 3 24.00


Spot 100 unknown Horizon 1.25 10 4 50.00


Spot 200 3000 Horizon 2.50 15 0 0.00


Other 25 unknown Sunbeam 0.50 10 3 15.00


Bulb 200 3000 Sunbeam 5.00 3 2 30.00


Neon 100 2000 Sunbeam 1.80 20 5 180.00


Bulb 100 unknown Sunbeam 0.25 10 5 12.50


Bulb 10 800 Horizon 0.20 25 2 10.00


Bulb 60 1000 Sunbeam 0.15 25 0 0.00


Bulb 80 1000 Sunbeam 0.20 30 2 12.00


Bulb 100 2000 Horizon 0.80 10 5 40.00


Bulb 40 1000 Horizon 0.10 20 5 10.00



To calculate the total Value Of Stock of a particular Brand of bulb.



Brand These two cells are the Criteria range.


Type the brand
name : Horizon



The stock value of
Horizon is : 248.00 =DSUM(B3:I19,I3,E23:E24)



What Does It Do ?

This function examines a list of information and produces the total.



Syntax

=DSUM(DatabaseRange,FieldName,CriteriaRange)


The DatabaseRange is the entire list of information you need to examine, including the


field names at the top of the columns.


The FieldName is the name, or cell, of the values to be totalled, such as "Value Of Stock" or I3.

The CriteriaRange is made up of two types of information.


The first set of information is the name, or names, of the Fields(s) to be used as the basis

for selecting the records, such as the category Brand or Wattage.


The second set of information is the actual record, or records, which are to be selected, such

as Horizon as a brand name, or 100 as the wattage.



Formatting

No special formatting is needed.



Examples


The total Value Of Stock of a particular Product of a particular Brand.



Product Brand


Bulb sunbeam



Total stock value is
: 54.50 =DSUM(B3:I19,I3,E49:F50)



This is the same calculation but using the name "Value Of Stock" instead of the cell address.


54.50
=DSUM(B3:I19,"Value Of
Stock",E49:F50)



The total Value Of Stock of a Bulb equal to a particular Wattage.


Product Wattage


Bulb 100



Total Value Of
Stock is : 52.50
=DSUM(B3:I19,"Value Of
Stock",E60:F61)



The total Value Of Stock of a Bulb less than a particular Wattage.


Product Wattage


Bulb <100



Total Value Of
Stock is : 56.00
=DSUM(B3:I19,"Value Of
Stock",E67:F68)






Start Date
Plus
Months End Date


1-Jan-98 3 1-Apr-98 =EDATE(C4,D4)


2-Jan-98 3 2-Apr-98 =EDATE(C5,D5)


2-Jan-98 -3 2-Oct-97 =EDATE(C6,D6)



What Does It Do?


This function is used to calculate a date which is a specific number of months in the past
or


in the future.



Syntax



=EDATE(StartDate,Months)



Formatting


The result will normally be expressed as a number, this can be formatted to represent


a date by using the Format,Cells,Number,Date
command.



Example


This example was used by a company hiring contract
staff.


The company needed to know the end date of the employment.


The Start date is entered.


The contract Duration is entered as months.


The =EDATE() function has been used to calculate the end of the contract.



Start Duration End


Tue 06-Jan-
98 3
Mon 06-Apr-
98

=EDATE(C27,D27)


Mon 12-Jan-
98 3
Sun 12-Apr-
98

=EDATE(C28,D28)


Fri 09-Jan-
98 4
Sat 09-May-
98

=EDATE(C29,D29)


Fri 09-Jan-
98 3 Thu 09-Apr-98

=EDATE(C30,D30)


Mon 19-Jan-
98 3
Sun 19-Apr-
98

=EDATE(C31,D31)


Mon 26-Jan-
98 3
Sun 26-Apr-
98

=EDATE(C32,D32)


Mon 12-Jan-
98 3
Sun 12-Apr-
98

=EDATE(C33,D33)




The company decide not to end contracts on Saturday or Sunday.


The =WEEKDAY() function has been used to identify the actaul weekday number of the
end date.


If the week day number is 6 or 7, (Sat or Sun), then 5 is subtracted from the =EDATE() to


ensure the end of contract falls on a Friday.



Start Duration End


Tue 06-Jan-
98 3
Mon 06-Apr-
98


Mon 12-Jan-
98 3 Fri 10-Apr-98


Fri 09-Jan-
98 4 Fri 08-May-98


Fri 09-Jan-
98 3 Thu 09-Apr-98


Mon 19-Jan-
98 3 Fri 17-Apr-98


Mon 26-Jan-
98 3 Fri 24-Apr-98


Mon 12-Jan-
98 3 Fri 10-Apr-98



=EDATE(C48,D48)-
IF(WEEKDAY(EDATE(C48,D48),2)>5,WEEKDAY(EDATE(C48,D48),2)-5,0)





Jan Feb Mar


North 5,000 6,000 4,500


South 5,800 7,000 3,000


East 3,500 2,000 10,000


West 12,000 4,000 6,000




Sales 4,000
and below. 4,000 4 {=FREQUENCY(D4:F7,E9:E11)}



Sales above
4,000 up to
6,000 6,000 5 {=FREQUENCY(D4:F7,E9:E11)}



Sales above
6,000 999,999 3 {=FREQUENCY(D4:F7,E9:E11)}



What Does It Do ?


This function compares a range of data against a list of intervals.


The result shows how many items in the range of data fall between the intervals.


The function is entered in the cells as an array, that is why it is enclosed in { } braces.



Syntax


=FREQUENCY(RangeOfData,ListOfIntervals)



Formatting


No special formatting is needed.



Example 1


The following tables were used to record the weight of a group of children.


The =FREQUENCY() function was then used to calculate the number of children whose


weights fell between specified intervals.



Weight Kg


Number
Of
Children:


Child 1 20.47


Between
0 - 15
Kg 2


Child 2 22.83


Above
15 but less
than or equal
to 20 Kg 4


Child 3 15.74


Above
20 Kg 3


Child 4 10.80


{=FREQUENCY(C30:C38,C41:C43)}


Child 5 8.28


{=FREQUENCY(C30:C38,C41:C43)}


Child 6 20.66


{=FREQUENCY(C30:C38,C41:C43)}


Child 7 17.36


Child 8 16.67


Child 9 18.01



Kg Weight Intervals


15


20


100




Example 2


This example uses characters instead of values.


A restaurant has asked 40 customers for their rating of the food in the
restaurant.


The ratings were entered into a table as a single letter, E, V, A, P or
D.


The manager now wants to calculate how many responses fell into each
category.


Unfortunately, the =FREQUENCY() function ignores text entries, so how can the frequency


of text be calculated?



The answer is to use the =CODE() and =UPPER() functions.


The =UPPER() forces all the text entries to be considered as capital letters.


The =CODE() function calculates the unique ANSI code for each character.


As this code is a numeric value, the =FREQUENCY() function can then be used!



Rating Frequency


Excellent E 6 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}


Very Good V 8 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}


Average A 9 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}


Poor P 8 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}


Disgusting D 9 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}



Customer Ratings


V D V A p A D D


V P a D A P V d


A V E P p E D A


A E d V D P a E


V e P P A V E D





Date Year


25-
Dec-
98 1998

=YEAR(C4)



What Does It
Do?

This function extracts the year number from a
date.



Syntax

=YEAR(Date)



Formatting

The result is shown as a number.





StartDate Days Result


1-Jan-98 28 35836

=WORKDAY(D4,E4)


1-Jan-98 28 10-Feb-98

=WORKDAY(D5,E5)



What Does It 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.



Syntax


=WORKDAY(StartDate,Days,Holidays)



Formatting

The result will normally be shown as a number which can be formatted to a


normal date by using Format,Cells,Number,Date.



Example

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- 2 Wed 04-

Feb-98 Feb-98

Tue 15-
Dec-98 28
Tue 26-Jan-
99


=WORKDAY(D25,E25,D28:D32)


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




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)


What Does It Do?
This function shows the day of the week from a date.


Syntax

=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.
If no number is specified, Excel will use 1.


Formatting
The result will be shown as a normal number.
To show the result as the name of the day, use Format, Cells, Custom and set
the Type to ddd or dddd.


Example
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:D45)



Booking Rates


Day Of
Week Cost


1 50


2 25


3 25


4 30


5 40


6 50


7 100






The column numbers are not
needed.

they are part of the illustration.

col 1 col 2 col 3 col 4 col 5 col 6


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)

What Does It 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.


Syntax
=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)
The ItemToFind is a single item specified by the user.
The RangeToLookIn 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.

Formatting
No special formatting is
needed.


Example 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. It 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



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)


Example 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 Ignition 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 Ignition 70

Ignition 50 70 45

Ford CYHead 290

CYHead 300 290 310

Vauxhall GearBox 500

Ford Engine 1,200


=VLOOKUP(C81,F75:I79,MATCH(B81,G74:I74,0)+1,FALSE)



Example 3
In 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 Item 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. If a match is
not found, the function will produce an error.
=VLOOKUP(C126,C114:D116,2,FALSE)

The discount is then looked up in the Discount Table
If 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. If 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

the 100 row is used.
=VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1,TRUE)



Discount Table


Unit Cost Table

Brick Wood Glass


Brick 2

1 0% 0% 0%


Wood 1

100 6% 3% 12%


Glass 3

300 8% 5% 15%




Orders Table


Item 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:D116,2,FALSE)
Discount =VLOOKUP(D126,F114:I116,MATCH(C126,G113:I113,0)+1,TRUE)
Total =(D126*E126)-(D126*E126*F126)





Original Text Upper Case


alan jones ALAN JONES =UPPER(C4)


bob smith BOB SMITH =UPPER(C5)


carOl wiLLiamS
CAROL
WILLIAMS =UPPER(C6)


cardiff CARDIFF =UPPER(C7)


abc123 ABC123 =UPPER(C8)


What Does It Do ?
This function converts all characters in a piece of text to upper case.


Syntax
=UPPER(TextToConvert)


Formatting
No special formatting is needed.


Example
See the example for FREQUENCY.






Original Text
Trimmed
Text


ABCD ABCD =TRIM(C4)


A B C D A B C D =TRIM(C5)


Alan
Jones Alan Jones =TRIM(C6)


ABCD ABCD =TRIM(C7)


What Does It 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


Syntax
=TRIM(TextToTrim)


Formatting
No special formatting is needed.







Today Is


11-Jul-09 =TODAY()


What Does It Do?
Use this to show the current date.


Syntax
=TODAY()


Formatting
The result will normally be displayed using the DD-MMM-YY
format.


Example
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 4574 =TODAY()-C20


10-Aug-
97 4353 =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 4575
=TODAY()-
C28+1


10-Aug-
97 4354
=TODAY()-
C29+1



Example
The following example shows the number of days from today until the year 2000.


Year 2000 Days Until


01-Jan-
2000 -3479 =C36-TODAY()




Hour Minute Second Time


14 30 59 14:30:59 =TIME(C4,D4,E4)


14 30 59 2:30:59 PM =TIME(C5,D5,E5)


14 30 59 0.60485 =TIME(C6,D6,E6)


What Does It Do?
This function will convert three separate numbers to an actual time.


Syntax
=TIME(Hour,Minute,Second)


Formatting
The result will be shown as a time which can be formatted either as 12 or 24 hour style.
If a normal number format is applied a decimal fraction is shown which represents the
time as a fraction of the day.





Item Sold price


Tyres 5 100


Filters 2 10


Bulbs 3 2



Total Sales Value : 526

=SUMPRODUCT(D4:D6,E4:E6)


What Does It 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.


Syntax
=SUMPRODUCT(Range1, Range, Range3 through to Range30)


Formatting
No special formatting is needed.


Example
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 In Stock with the Case Price to
calculate what the merchant spent in buying the stock.

The =SUMPRODUCT() function is used to multiply the Cases In Stock with
the Bottles In Case and the Bottle Setting Price, to calculate the potential value of the
stock if it is all sold.



Product
Cases
In
Stock Case Price
Bottles
In
Case
Bottle
Cost Markup
Bottle
Selling
Price

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:D39)

Total Selling Price Of
Stock : 9,790 =SUMPRODUCT(C35:C39,E35:E39,H35:H39)


Profit : 2,350 =E44-E43






Item Date Cost


Brakes 1-Jan-98 80


Tyres 10-May-98 25


Brakes 1-Feb-98 80


Service 1-Mar-98 150


Service 5-Jan-98 300


Window 1-Jun-98 50


Tyres 1-Apr-98 200


Tyres 1-Mar-98 100


Clutch 1-May-98 250


Total cost of all Brakes bought. 160 =SUMIF(C4:C12,"Brakes",E4:E12)
Total cost of all Tyres bought. 325 =SUMIF(C4:C12,"Tyres",E4:E12)
Total of items costing 100 or above. 1000 =SUMIF(E4:E12,">=100")
Total of item typed in following
cell. service 450 =SUMIF(C4:C12,E18,E4:E12)


What Does It Do ?
This function adds the value of items which match criteria set by the user.


Syntax
=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)
=SUMIF(C4:C12,"Brakes",E4:E12) This examines the names of products in C4:C12.

It then identifies the entries for Brakes.


It then totals the respective figures in E4:E12
=SUMIF(E4:E12,">=100") This examines the values in E4:E12.


If the value is >=100 the value is added to the total.

Formatting
No special formatting is needed.





Number
Places
To
Round
Rounded
Up


1.47589 0 2

=ROUNDUP(C4,D4)


1.47589 1 1.5

=ROUNDUP(C5,D5)


1.47589 2 1.48

=ROUNDUP(C6,D6)


13643.48 -1 13650

=ROUNDUP(C7,D7)


13643.48 -2 13700

=ROUNDUP(C8,D8)


13643.48 -3 14000

=ROUNDUP(C9,D9)


What Does It Do ?
This function rounds a number up to a specified amount of decimal places.
If 0 is used the number is rounded up to the nearest whole number.
If a negative amount of rounding is used the figures to the left of the decimal point are rounded.

Syntax
=ROUNDUPNumberToRound,DecimalPlacesToUse)


Formatting
No special formatting is
needed.




Original
Text
Start
Position
Characters
To
Replace
New
Character
Modified
Text


ABCDEFGH 2 1 x AxCDEFGH

=REPLACE(C4,D4,E4,F4)

ABCDEFGH 2 5 x AxGH

=REPLACE(C5,D5,E5,F5)

ABCDEFGH 2 1 hello AhelloCDEFGH

=REPLACE(C6,D6,E6,F6)

ABCDEFGH 2 5 hello AhelloGH

=REPLACE(C7,D7,E7,F7)

What Does It Do ?
This function replaces a portion of text with a new piece of text.
You need to specify where the replacement should start, how many characters to
remove and what the new replacement text should be.


Syntax
=REPLACE(OriginalText,StartPosition,NumberOfCharactersToReplace,NewText)


Formatting
No special formatting is needed.



Values
Ranking
Position
High to Low


7 4

=RANK(C4,C4:C8)


4 5

=RANK(C5,C4:C8)


25 1

=RANK(C6,C4:C8)


8 3

=RANK(C7,C4:C8)


16 2

=RANK(C8,C4:C8)



Values
Ranking
Position
Low to High


7 2 =RANK(C11,C11:C15,1)


4 1 =RANK(C12,C11:C15,1)


25 5 =RANK(C13,C11:C15,1)


8 3 =RANK(C14,C11:C15,1)


16 4 =RANK(C15,C11:C15,1)



Values
Ranking
Position
High to Low


10 5 =RANK(C18,C18:C22)


30 2 =RANK(C19,C18:C22)


20 4 =RANK(C20,C18:C22)


30 2 =RANK(C21,C18:C22)


40 1 =RANK(C22,C18:C22)


What Does It Do ?
This function calculates the position of a value in a list relative to the other values in the list.
A typical usage would be to rank the times of athletes in a race to find the winner.
The ranking can be done on an ascending (low to high) or descending (high to low) basis.
If there are duplicate values in the list, they will be assigned the same rank. Subsequent ranks
would not follow on sequentially, but would take into account the fact that there were
duplicates.
If the numbers 30, 20, 20 and 10 were ranked, 30 is ranked as 1, both 20's are ranked as 2,
and
the 10 would be ranked as 4.


Value Rank

30 1 =RANK(B34,B34:B37)

20 2 =RANK(B35,B34:B37)

20 2 =RANK(B36,B34:B37)

10 4 =RANK(B37,B34:B37)


Syntax
=RANK(NumberToRank,ListOfNumbers,RankOrder)

The RankOrder can be 0 zero or 1.
Using 0 will rank larger numbers at the top. (This is optional, leaving it out has the same effect).
Using 1 will rank small numbers at the top.


Formatting
No special formatting is needed.


Example
The following table was used to record the times for athletes competing in a race.
The =RANK() function was then used to find their race positions based upon the finishing
times.

Athlete Time Race Position

John 1:30 4 =RANK(C53,C53:C58,1)

Alan 1:45 6 =RANK(C54,C53:C58,1)

David 1:02 1 =RANK(C55,C53:C58,1)

Brian 1:36 5 =RANK(C56,C53:C58,1)

Sue 1:27 3 =RANK(C57,C53:C58,1)

Alex 1:03 2 =RANK(C58,C53:C58,1)




Numbers Product


2 3 6 =PRODUCT(C4,D4)


5 10 50 =PRODUCT(C5:D5)


3 7 210

=PRODUCT(C6:D6,10)


6300 =PRODUCT(C4:D6)

What Does It Do
?
This function multiples a group of numbers together.
It is the same as using 2*3*5*10*3*7, which results in 6300.


Syntax
=PRODUCT(Number1,Number2,Number3... through to Number30)
or

=PRODUCT(RangeOfNumbers)
or
=PRODUCT(Number1,Range,Number2...)


Formatting
No special formatting is
needed.





Order
No. Cost
Payment
Type
Handling
Charge


AB001 1000 Cash - =IF(OR(E4="Visa",E4="Delta"),5,0)

AB002 1000 Visa 5 =IF(OR(E5="Visa",E5="Delta"),5,0)

AB003 2000 Cheque - =IF(OR(E6="Visa",E6="Delta"),5,0)

AB004 5000 Delta 5 =IF(OR(E7="Visa",E7="Delta"),5,0)

What Does It Do?
This function tests two or more conditions to see if any of them are true.
It can be used to test that at least one of a series of numbers meets certain conditions.
Normally the OR() function would be used in conjunction with a function such as =IF().


Syntax
=OR(Test1,Test2)
Note that there can be up to 30 possible tests.


Formatting
When used by itself it will show TRUE or FALSE.


Example
The following table shows a list of orders taken by a company.
A handling charge of 5 is made on all orders paid by Visa or Delta cards.
The =OR() function has been used to determine whether the charge needs to be applied.


Order
No. Cost
Payment
Type
Handling
Charge


AB001 1000 Cash - =IF(OR(E27="Visa",E27="Delta"),5,0)

AB002 1000 Visa 5


AB003 2000 Cheque -


AB004 5000 Delta 5




The current Date and
Time


7/11/2009 18:10 =NOW()


40005.7574 =NOW()


What Does It Do?
This function shows the current date and time. The result will be updated each time the
worksheet is opened and every time an entry is made anywhere on the
worksheet.


Syntax

=NOW()


Formatting
The result will be shown as a date and time. If it is formatted to show as a
number
the integer part is used for the date and the decimal portion represent the time.



Start Date End Date Work Days


1-Mar-98 7-Mar-98 5 =NETWORKDAYS(C4,D4)

25-Apr-98 30-Jul-98 69 =NETWORKDAYS(C5,D5)

24-Dec-98 5-Jan-99 9 =NETWORKDAYS(C6,D6)

What Does It Do?
This function will calculate the number of working days between two dates.
It will exclude weekends and any holidays.


Syntax
=NETWORKDAYS(StartDate,EndDate,Holidays)
Holidays : This is a list of dates which will be excluded from the calculation, such as Xmas
and Bank holidays.


Formatting
The result will be shown as a number.


Note
The calculation does not include the last day. The result of using 1-Jan-98 and 5-Jan-98 will
give a result of 4. To correct this add 1 to the result. =NETWORKDAYS(Start,End,Holidays)+1

Example
The following example shows how a list of Holidays can be created.

Start Date End Date Work Days

Mon 02-Mar-98 Fri 06-Mar-98 5 =NETWORKDAYS(B28,C28,C33:C37)
Mon 02-Mar-98 Fri 13-Mar-98 10 =NETWORKDAYS(B29,C29,C33:C37)
Mon 27-Apr-98 Fri 01-May-98 4 =NETWORKDAYS(B30,C30,C33:C37)


Holidays

Bank Holiday 1-May-98

Xmas 25-Dec-98

New Year 1-Jan-97

New Year 1-Jan-98

New Year 1-Jan-99



Original
Date Month


1-Jan-98 1 =MONTH(C4)


1-Jan-98 January =MONTH(C5)


What Does It Do?
This function extracts the month from a complete date.


Syntax
=MONTH(Date)


Formatting
Normally the result will be a number, but this can be formatted to show the actual
month by using Format,Cells,Number,Custom and using the code mmm or mmmm.

Example
The =MONTH function has been used to calculate the name of the month for your birthday.


Please enter your date of birth in the
format dd/mm/yy 3/25/1962


You were born in January

=MONTH(F20)




Text
Start
Position
How Many
Characters
Mid
String


ABCDEDF 1 3 ABC =MID(C4,D4,E4)


ABCDEDF 2 3 BCD =MID(C5,D5,E5)


ABCDEDF 5 2 ED =MID(C6,D6,E6)



ABC-100-DEF 100 =MID(C8,5,3)


ABC-200-DEF 200 =MID(C9,5,3)


ABC-300-DEF 300 =MID(C10,5,3)



Item Size: Large Large =MID(C12,12,99)


Item Size: Medium Medium =MID(C13,12,99)


Item Size: Small Small =MID(C14,12,99)


What Does It Do ?

This function picks out a piece of text from the middle of a text entry.
The function needs to know at what point it should start, and how many characters to pick.
If the number of characters to pick exceeds what is available, only the available characters
will be picked.


Syntax

=MID(OriginalText,PositionToStartPicking,NumberOfCharactersToPick)


Formatting

No special formatting is needed.


Example 1

The following table uses the =MID() function to extract a post code from a branch ID used
by a company.
It is assumed that all branch ID's follow the same format with the letters identifying the
postal region being in the 5th and 6th positions.



Branch ID Postal Region


DRS-CF-476 CF =MID(C35,5,2)


DRS-WA-842 WA =MID(C36,5,2)


HLT-NP-190 NP =MID(C37,5,2)



Example 2

This example shows how to extract an item which is of variable length, which is inside
a piece of text which has no standard format, other than the required text is always
between two slash / symbols.



Full Branch Code Postal Region


DRS/STC/872 STC


HDRS/FC/111 FC


S/NORTH/874 NORTH


HQ/K/875 K


SPECIAL/UK &
FR/876 UK & FR


=MID(C50,FIND("/",C50)+1,FIND("/",C50,FIND("/",C50)+1)-
FIND("/",C50)-1)



Find the first /, plus 1 for the Start of the code.


Find the second /, occurring after the first /


Calculate the length of the text to extract, by subtracting the position


of the first / from the position of the second /





Text Letter To Find Position Of Letter


Hello e 2 =FIND(D4,C4)


Hello H 1 =FIND(D5,C5)


Hello o 5 =FIND(D6,C6)


Alan Williams a 3 =FIND(D7,C7)


Alan Williams a 11

=FIND(D8,C8,6)


Alan Williams T #VALUE! =FIND(D9,C9)


What Does It Do?
This function looks for a specified letter inside another piece of text.
When the letter is found the position is shown as a number.
If the text contains more than one reference to the letter, the first occurrence is used.
An additional option can be used to start the search at a specific point in the text, thus
enabling the search to find duplicate occurrences of the
letter.
If the letter is not found in the text, the result #VALUE is shown.


Syntax
=FIND(LetterToLookFor,TextToLookInside,StartPosition)
LetterToLookFor : This needs to be a single character.
TextToLookInside : This is the piece of text to be searched through.
StartPosition : This is optional, it specifies at which point in the text the search should begin.

Formatting
No special formatting is needed, the result will be shown as a number.