atoz excel formulas

8/20/2019 AtoZ Excel Formulas

1/139

Excel Function Dictionary© 1998 - 2000 Peter Noneley

Split ForenameSurnamePage 1 of 139

Split Forename and Surname

!e follo"ing formula are u#eful "!en you !a$e one cell containing text "!ic! nee%#

to &e #plit up'

(ne of t!e mo#t common example# of t!i# i# "!en a per#on# Forename an% Surnameare entere% in full into a cell'

!e formula u#e $ariou# text function# to accompli#! t!e ta#)'

Eac! of t!e tec!ni*ue# u#e# t!e #pace &et"een t!e name# to i%entify "!ere to #plit'

Finding the First Name

Full Name Fir#t Name

+lan ,one# +lan .EF/1FND/4 41155

6o& Smit! 6o& .EF/17FND/4 417155

arol illiam# arol .EF/1FND/4 41155

Finding the Last Name

Full Name .a#t Name

+lan ,one# ,one# :;


2/139


Percentage#Page 2 of 139

Percentages

!ere are no #pecific function# for calculating percentage#'

>ou !a$e to u#e t!e #)ill# you "ere taug!t in your mat!# cla## at #c!ool?

Finding a percentage of a value

nitial $alue 120

@ to fin% 27@

Percentage $alu 30 D8AD9

Example 1

+ company i# a&out to gi$e it# #taff a pay ri#e'

!e "age# %epartment nee% to calculate t!e increa#e#'

Staff on %ifferent gra%e# get %ifferent pay ri#e#'

;ra%e @ :i#e

+ 10@6 17@

20@

Name ;ra%e (l% Salary ncrea#e

+lan + 10000 1000 E23A.((BCP/D231820D18D2056o& 6 20000 3000 E2A.((BCP/D21820D18D205

arol 30000 000 E27A.((BCP/D271820D18D205Da$i% 6 27000 370 E2A.((BCP/D21820D18D205Elaine 32000 00 E2A.((BCP/D21820D18D205Fran) + 12000 1200 E28A.((BCP/D281820D18D205

Finding a percentage increase

nitial $alue 120

@ increa#e 27@

ncrea#e% $alue 170 D33AD3GD33

Example 2

+ company i# a&out to gi$e it# #taff a pay ri#e'

!e "age# %epartment nee% to calculate t!e ne" #alary inclu%ing t!e @ increa#e'

Staff on %ifferent gra%e# get %ifferent pay ri#e#'

;ra%e @ :i#e

+ 10@

6 17@ 20@

Name ;ra%e (l% Salary ncrea#e

+lan + H 10000 11000 =E48*LOOKU(D48,!C!18!C!$,!D!18!D!$)%E48

6o& 6 H 20000 23000 =E4&*LOOKU(D4&,!C!18!C!$,!D!18!D!$)%E4&

arol H 30000 3000 =E5$*LOOKU(D5$,!C!18!C!$,!D!18!D!$)%E5$

Da$i% 6 H 27000 2870 =E51*LOOKU(D51,!C!18!C!$,!D!18!D!$)%E51

Elaine H 32000 3800 =E5*LOOKU(D5,!C!18!C!$,!D!18!D!$)%E5

Fran) + H 12000 13200 =E53*LOOKU(D53,!C!18!C!$,!D!18!D!$)%E53

Finding one value as percentage of another

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


3/139


Percentage#Page 3 of 139

Ialue + 120

Ialue 6 0

+ a# @ of 6 70@ D79JD78

>ou "ill nee% to format t!e re#ult a# @ &y u#ing t!e @ &utton

on t!e tool&ar'

Example 3

+ manager !a# &een a#)e% to #u&mit &u%get re*uirement# for next year'

!e manger nee%# to #pecify "!at "ill &e re*uire% eac! *uarter'

!e manager )no"# "!at !a# &een #pent &y eac! region in t!e pre$iou# year'

6y analy#ing t!e pa#t year# #pen%ing t!e manager !ope# to pre%ict

"!at "ill nee% to &e #pent in t!e next year'

Last ears figures

:egion K1 K2 K3 K

Nort! 9000 2000 9000 000

Sout! 000 000 9000 7000

Ea#t 2000 8000 000 3000e#t 8000 9000 000 7000 otal

otal 2000 23000 31000 20000 100000

Last ears !uarters as " of last ears #otal

:egion K1 K2 K3 K

Nort! 9@ 2@ 9@ @ ;J


4/139


Percentage#Page of 139

D117J/100@GD1105A B C D E F G H I J

116


5/139


S!o" all formulaPage 7 of 139

Show all formula

Pre## t!e #ame com&ination to #ee t!e original $ie"'

10 20 30

30 0 0

70 0 0

0 80 30

>ou can $ie" all t!e formula on t!e "or)#!eet &y pre##ing 'trl an% ('

!e ) i# t!e left #ingle *uote u#ually foun% on t!e )ey to left of num&er 1'

Pre## 'trl an% ( to #ee t!e formula &elo"' (The screen may look a bit odd.)

A B C D E F G H I

1

2

3

4

5

6

7

8

9

10

11

12


6/139


SC=Lu#ingLname#Page of 139

S*M using names

>ou can u#e t!e name# type% at t!e top of column# or #i%e of ro"# in calculation#

#imply &y typing t!e name into t!e formula'

ry t!i# example

!e re#ult "ill #!o"'

,an Fe& =ar

Nort! 7 70 70

Sout! 30 27 37

Ea#t 37 10 70

e#t 20 70 7

otal

+f it does not wor, -

!e feature may !a$e &een #"itc!e% off on your computer'

;o to cell '1. an% t!en enter t!e formula /S*M0an

!i# formula can &e copie% to 1. an% E1. an% t!e name# c!ange to Fe$ an% Mar '

>ou can #"itc! it on &y u#ing #ools 4ptions 'alculation 5ccept La$els in Formula'

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

1819

20

21


7/139


n#tant !art#Page of 139

+nstant 'harts

>ou can create a c!art *uic)ly "it!out !a$ing to u#e t!e c!art &utton on

,an Fe& =ar

Nort! 7 70 70

Sout! 30 27 37

Ea#t 37 10 70

e#t 20 70 7

lic) any"!ere in#i%e t!e ta&le a&o$e'

t!e tool&ar &y pre##ing t!e function )ey F11 "!ile in#i%e a range of %ata'

!en pre## F11'

A B C D E F G H I

1

2

3

4

5

6

7

8

9

10

11

12

13


8/139


6rac)et# in formulaPage 8 of 139

6rac,ets in formula

Sometime# you "ill nee% to u#e &rac)et# /al#o )no"n a# M&race#M5 in formula'

!i# i# to en#ure t!at t!e calculation# are performe% in t!e or%er t!at you nee%'

Example 1 !e "rong an#"er ?

10

20

2

70 12G13A1

>ou may expect t!at 10 G 20 "oul% e*ual 30

+n% t!en 30 A 2 "oul% e*ual 0

6ut &ecau#e t!e A i# calculate% fir#t Excel #ee# t!e

calculation a# 20 A 2 re#ulting in 0

+n% t!en 10 G 0 re#ulting in 70

Example 2 !e correct an#"er'

10

20

2

0 /2G285A29

6y placing &rac)et# aroun% /10G205 Excel perform# t!i#

part of t!e calulation fir#t re#ulting in 30

!en t!e 30 i# multiple% &y 2 re#ulting in 0

!e nee% for &rac)et# occur# "!en you mix plu# or minu# "it! %i$i%e or multiply'

=at!ematically #pea)ing t!e A an% J are more important t!an G an% - '

!e 7 an% 8 operation# "ill &e calculate% &efore 9 an% : '

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


9/139


+ge alculationPage 9 of 139

5ge 'alculation

>ou can calculate a per#on# age &a#e% on t!eir &irt!%ay an% to%ay# %ate'

!e D+EDF/5 i# not %ocumente% in Excel 7 or 9 &ut it i# in 2000'

/=a)e# you "on%er "!at el#e =icro#oft forgot to tell u#?5

6irt! %ate 1-,an-0

>ear# li$e% 77 D+EDF/8(D+>/54y45

an% t!e mont!# 8 D+EDF/8(D+>/54ym45

an% t!e %ay# D+EDF/8(D+>/54m%45

>ou can put t!i# all toget!er in one calculation "!ic! create# a text $er#ion'

+ge i# 77 >ear# 8 =ont!# an% Day#

="' + "D'TEDIF(C8,TOD'(),".")" /0+, "D'TEDIF(C8,TOD'(),".")" 26+ / "D'TEDIF(C8,TOD'(),"")" D/.+"

5nother wa to calculate age!i# met!o% gi$e# you an age "!ic! may potentially !a$e %ecimal place# repre#enting t!e mont!#'

f t!e age i# 20'7 t!e '7 repre#ent# mont!#'

6irt! %ate 1-,an-0

+ge i# 77'8 /(D+>/5-235J37'27

!e calculation u#e# t!e D+EDF/5 function'

A B C D E F G H

1

2

3

4

5

67

8

9

10

11

12

13

14

15

16

17

18

1920

21

22

23

24

25


10/139


+utoSum S!ortcut BeyPage 10 of 139

5utoSum Shortcut ;e

n#tea% of u#ing t!e +utoSum &utton from t!e tool&ar

ry it !ere

or

,an Fe& =ar otal

Nort! 10 70 90

Sout! 20 0 100

Ea#t 30 0 200

e#t 0 80 300

otal

you can pre## 5lt an% / to ac!ie$e t!e #ame re#ult'

=o$e to a &lan) cell in t!e otal ro" or column t!en pre## 5lt an% /'

Select a ro" column or all cell# an% t!en pre## 5lt an% /'

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


11/139


+6SPage 11 of 139

56S

Num&er +olute Ialue

10 10 +6S/5

-10 10 +6S/75

1'27 1'27 +6S/5

-1'27 1'27 +6S/5


12/139


+DD:ESSPage 12 of 139

5>ESS

ype a column num&er 2

ype a ro" num&er 3

ype a #!eet name


13/139


+NDPage 13 of 139

5N

tem# o e#t :e#ult

700 800 1 +ND/100D1005

700 27 0 +ND/7100D7100527 700 0 +ND/100D1005

12 1 +ND/D1DQ725


14/139


+IE:+;EPage 1 of 139

5?E>5@E

=on ue e% !u Fri Sat Sun +$erage

emp 30 31 32 29 2 28 2 29 +IE:+;E/D,5

:ain 0 0 0 3 1 2 +IE:+;E/D7,75


emp 30 32 29 2 28 2 28' +IE:+;E/D8,85

:ain 0 0 3 1 2'33333 +IE:+;E/D9,95


emp 30 No 32 29 2 28 2 28' +IE:+;E/D12,125

:ain 0 :ea%ing 0 3 1 2'33333 +IE:+;E/D13,135


15/139


E.N;Page 17 of 139

'E+L+N@

Num&er :ai#e% Cp

2'1 3 E.N;/15

1'7 2 E.N;/7151'9 2 E.N;/15

20 30 E.N;/305

27 30 E.N;/8305

0 0 E.N;/9305


16/139


E.N;Page 1 of 139

tem

6ric)# 1000 300 E.N;/D7JE715

oo% 7000 00 9 E.N;/D77JE7715

ement 2000 370 E.N;/D7JE715

Example 3

!e follo"ing ta&le# "ere u#e% &y a #!op)eeper to calculate t!e #elling price of an item'

!e #!op)eeper &uy# pro%uct# &y t!e &ox'

!e co#t of t!e item i# calculate% &y %i$i%ing t!e 6ox o#t &y t!e 6ox Kuantity'

!e #!op)eeper al"ay# "ant# t!e price to en% in 99 pence'

a&le 1 #!o"# !o" Ru#t a normal %i$i#ion re#ult# in $arying tem o#t#'

a&le 1

tem 6ox Knty 6ox o#t o#t Per tem

Plug# 11 H20 1'81818 D9J9

Soc)et# H18'27 2'01 D0J0

,unction# 7 H28'10 7'2000 D1J1

+%apter# 1 H28 1'7000 D2J2

a&le 2 #!o"# !o" t!e E.N;/5 function !a# &een u#e% to rai#e t!e tem o#t to

al"ay# en% in 99 pence'

a&le 2

tem n 6ox 6ox o#t o#t Per tem :ai#e% o#t

Plug# 11 H20 1'81818 1'99

Soc)et# H18'27 2'01 2'99

,unction# 7 H28'10 7'2000 7'99

+%apter# 1 H28 1'7000 1'99

N/E835GE.N;/=(D/E83150'995

Explanation

N/E835 alculate# t!e integer part of t!e price'

=(D/E8315 alculate# t!e %ecimal part of t!e price'

E.N;/=(D/E8350'995 :ai#e# t!e %ecimal to 0'99

Cnit# o6e =o$e%

ruc)apacity

ruc)#Nee%e%

A B C D E F G H

53

54

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


17/139


E..Page 1 of 139

'ELL

!i# i# t!e cell an% content# to te#t' 1'70@

!e cell a%%re##' D3 E../4a%%re##4D35!e column num&er' E../4col4D35

!e ro" num&er' 3 E../4ro"4D35!e actual content# of t!e cell' 0'17 E../4content#4D35

$ E../4type4D35

T E../4prefix4D35

!e "i%t! of t!e cell' 12 E../4"i%t!4D35

P2 E../4format4D35

0 E../4parent!e#e#4D35

0 E../4color4D35

1 E../4protect4D35

!e filename containing t!e cell' MfileJJJ$arJ"""Japp#Jcon$er#ionJtmpJ#cratc!L1J2833870'xl#MUE..

E../4filename4D35


18/139



19/139


.E+NPage 19 of 139

'LE5N

Dirty ext lean ext


20/139


(=6NPage 20 of 139

'4M6+N

Pool (f tem# tem# n + ;roup Po##i&le ;roup#

2 (=6N/D5

3 (=6N/7D75

2 2 327 (=6N/D5

ello" 6lac) >ello" 6lac)

Sc!eme Sc!eme Sc!eme 8 Sc!eme 9 Sc!eme 10;reen ;reen ;reen 6lue WWWWWW

A B C D E F G

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

3031

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


21/139


(=6NPage 21 of 139

6lue 6lue >ello" >ello"

>ello" 6lac) 6lac) 6lac)

A B C D E F G

57

58


22/139


(N+EN+EPage 22 of 139

'4N'5#EN5#E

Name 1 Name 2 oncatenate% ext

+lan ,one# +lan,one# (N+EN+E/D5

6o& illiam# 6o&illiam# (N+EN+E/7D75

arol Da$ie# arolDa$ie# (N+EN+E/D5

+lan ,one# +lan ,one# (N+EN+E/4 4D5

6o& illiam# illiam# 6o& (N+EN+E/D84 485

arol Da$ie# Da$ie# arol (N+EN+E/D94 495

ou can ac!ie$e t!e #ame re#ult &y u#ing t!e operator'

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


23/139


(NIE:Page 23 of 139

'4N?E>#

1 in cm 2'7 (NIE:/DE51 ft m 0'308 (NIE:/7D7E75

1 y% m 0'91 (NIE:/DE5

1 yr %ay 37'27 (NIE:/8D8E85

1 %ay !r 2 (NIE:/9D9E95

1'7 !r mn 90 (NIE:/10D10E105

0'7 mn #ec 30 (NIE:/11D11E115

ear yr

Day %ay Pressure


24/139


(NIE:Page 2 of 139

#emperature Li%uid

Degree el#iu# ea#poon t#p

Degree Fa!ren!ei F a&le#poon t

Degree Bel$in B Flui% ounce o

up cupForce Pint pt

Ne"ton N Kuart *t

Dyne %yn ;allon gal

Poun% force l&f .iter l

Energ Power

,oule ,


25/139


(::E.Page 27 of 139

'4>>EL

a&le 1 a&le 2

=ont! +$g emp Sale#

,an 20 100 H2000 H20000

Fe& 30 200 H1000 H30000

=ar 30 300 H7000 H20000

+pr 0 200 H1000 H0000

=ay 70 00 H8000 H0000

,un 70 00 H1000 H20000

orrelation 0'8 orrelation 28@

(::E./D7D10E7E105 (::E./;7;10


26/139


(CNPage 2 of 139

'4*N#

Entrie# o 6e ounte% ount

10 20 30 3 (CN/E5

10 0 30 3 (CN/7E7510 -20 30 3 (CN/E5

10 1-,an-88 30 3 (CN/E5

10 2130 30 3 (CN/8E85

10 0'0888 30 3 (CN/9E95

10 30 2 (CN/10E105

10


27/139


(CN+Page 2 of 139

'4*N#5

Entrie# o 6e ounte% ount

10 20 30 3 (CN+/E5

10 0 30 3 (CN+/7E7510 -20 30 3 (CN+/E5

10 1-,an-88 30 3 (CN+/E5

10 2130 30 3 (CN+/8E85

10 0'3 30 3 (CN+/9E95

10 30 2 (CN+/10E105

10


28/139


(CN6.+NBPage 28 of 139

'4*N#6L5N;

:ange o e#t 6lan)#

1 2 (CN6.+NB/115

or N'

+# t!e re#ult# of t!e $ote "ere collate% t!ey "ere entere% in to t!e ta&le'

!e (CN6.+NB/5 function !a# &een u#e% to calculate t!e num&er of %epartment# "!ic!!a$e no yet regi#tere% a $ote'

+%min +ccount# Pro%uction Per#onnel

Factory 1 > N

Factory 2 > > N

Factory 3

Factory N N N

Factory 7 > >

Factory > > > N

Factory N >

Factory 8 N N > >

Factory 9 >Factory 10 > N >

Iote# not $et regi#tere% 1 (CN6.+NB/32F15

Iote# for >e# 1 (CNF/32F14>45

Iote# for No 10 (CNF/32F14N45

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

4041

42

43

44

45

46

47


29/139


(CNFPage 29 of 139

'4*N#+F

tem Date o#t

6ra)e# 1-,an-98 80

yre# 10-=ay-98 276ra)e# 1-Fe&-98 80

Ser$ice 1-=ar-98 170

Ser$ice 7-,an-98 300

in%o" 1-,un-98 70

yre# 1-+pr-98 200

yre# 1-=ar-98 100

lutc! 1-=ay-98 270


30/139


D+EPage 30 of 139

5#E

Day =ont! >ear Date

27 12 99 12J27J99 D+E/ED5

27 12 99 27-Dec-99 D+E/E7D775

33 12 99 ,anuary 2 2000 D+E/ED5


31/139


D+EDFPage 31 of 139

5#E+F

Fir#tDate Secon%Date nter$al Difference

1-,an-0 10-=ay-0 %ay# 382 D+EDF/D4%45

1-,an-0 10-=ay-0 mont!# 12 D+EDF/7D74m45

1-,an-0 10-=ay-0 year# 10 D+EDF/D4y45

1-,an-0 10-=ay-0 year%ay# 129 D+EDF/D4y%45

1-,an-0 10-=ay-0 yearmont!# D+EDF/8D84ym45

1-,an-0 10-=ay-0 mont!%ay# 9 D+EDF/9D94m%45


32/139


D+EI+.CEPage 32 of 139

5#E?5L*E

Date Date Ialue

27-%ec-99 3719 D+EI+.CE/5

27J12J99 Err702 D+EI+.CE/75

27-%ec-99 3719 D+EI+.CE/527J12J99 Err702 D+EI+.CE/5


33/139


D+>Page 33 of 139

5G

Full Date !e Day

27-Dec-98 27 D+>/5

7-Sep-17 !u D+>/75

7-Sep-17 7 D+>/5

function !a# &een u#e% to calculate t!e name of t!e %ay for your &irt!%ay'

Plea#e enter your %ate of &irt! in t!e format %%JmmJyy 3J27J192

>ou "ere &orn on e%ne#%ay 2 D+>/F215

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


34/139


D+>S30Page 3 of 139

5GS3.C

StartDate En%Date Day# 6et"een * See the Note below.

1-,an-98 7-,an-98 D+>S30/D:CE5

1-,an-98 1-Fe&-98 30 D+>S30/7D7:CE51-,an-98 31-=ar-98 89 D+>S30/D:CE5

1-,an-98 31-Dec-98 379 D+>S30/D:CE5

S30/StartDateEn%Date:CE of F+.SE5

:CE C#e t!i# for European accounting #y#tem#'

F+.SE C#e t!i# for CS+ accounting #y#tem#'

Formatting

!e re#ult "ill &e #!o"n a# a num&er'

Note

!e calculation %oe# not inclu%e t!e la#t %ay' !e re#ult of u#ing 1-,an-98 an% 7-,an-98 "ill

gi$e a re#ult of ' o correct t!i# a%% 1 to t!e re#ult' D+>S30/StartEn%:CE5G1

A B C D E F

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23


35/139


DE.+Page 37 of 139

EL#5

Num&er1 Num&er2 Delta

10 20 0 DE.+/D5

70 70 1 DE.+/7D751'7 1'7 1 DE.+/D5

1'7 18 1 DE.+/D5

1'70@ 0'17 1 DE.+/8D85


36/139


E+SPage 3 of 139

Ea#tern %ata'

C#e% &y t!e example for t!e ND:E/5 function'

,an Fe& =ar otal

+lan 1000 2000 3000 000

6o& 000 7000 000 17000arol 000 8000 9000 2000

otal 12000 17000 18000 7000

A B C D E F G H I J

1

2

3

4

5

67

8


37/139


ED+EPage 3 of 139

E5#E

Start Date Plu# =ont!# En% Date

1-,an-98 3 1-+pr-98 ED+E/D5

2-,an-98 3 2-+pr-98 ED+E/7D752-,an-98 -3 2-(ct-9 ED+E/D5

/ED+E/8D85257EEBD+>/ED+E/8D8525-705

A B C D E F G

1

2

3

4

56

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


38/139


E(=(N<Page 38 of 139

E4M4N#D

StartDate Plu# =ont!# En% (f =ont!

7-,an-98 2 37887 E(=(N


39/139


E::(:'>PEPage 39 of 139

E>>4>H#GPE

Data !e Error Error ype

10 0 UDIJ0? 732 E::(:'>PE/E5

10 3 UN+=EW 727 E::(:'>PE/E7510 3 UI+.CE? 719 E::(:'>PE/E5

1000 1300 2100 UNJ+ E::(:'>PE/E5


40/139


EIENPage 0 of 139

E?EN

(riginal Ialue E$enly :oun%e%

1 2 EIEN/5

1'2 2 EIEN/75

2'3 EIEN/527 2 EIEN/5


41/139


EY+Page 1 of 139

EI5'#

ext1 ext2 :e#ult


42/139


F+Page 2 of 139

F5'#

Num&er Factorial

3 F+/5

3'7 F+/757 120 F+/5

10 328800 F+/5

20 23290200810000 F+/85


43/139


FNDPage 3 of 139

F+N

ext .etter o Fin% Po#ition (f .etter


44/139


FYEDPage of 139

F+IE

10 10'00 FYED/510 10 FYED/705

10 10'0 FYED/15

10 10'00 FYED/25

10'27 10'27 FYED/85

10'27 10 FYED/905

10'27 10'3 FYED/1015

10'27 10'27 FYED/1125

1000 1000'00 FYED/125

1000'23 1000 FYED/1305

1000'23 1000 FYED/10:CE5


45/139


F.((:Page 7 of 139

FL44>

Num&er :oun%e% Do"n

1'7 1 F.((:/15

2'3 2 F.((:/715

2'9 2 F.((:/15123 100 F.((:/705

17 100 F.((:/8705

17 170 F.((:/9705


46/139



47/139



48/139



49/139


Num&er


50/139


FPage 70 of 139

+F

Name Sale# arget :e#ult

+lan 1000 7000 Not +c!ie$e% F/D4+c!ie$e%44Not +c!ie$e%45

6o& 000 7000 +c!ie$e% F/7D74+c!ie$e%44Not +c!ie$e%45

arol 2000 000 Not +c!ie$e% F/D4+c!ie$e%44Not +c!ie$e%45


51/139


FPage 71 of 139

;la## No H 2000 H - H 2000

ement >e# H 700 H - H 700

urf >e# H 3000 H 300 H 200

F/+ND/14>e#4D110005D1A10@05

A B C D E F G H I J

59

60

61

62


52/139


NDEYPage 72 of 139

+NEI


53/139


NDEYPage 73 of 139

;la## 9000'00 10000'00 11000'00 12000'00

S4*#D Ktr1 Ktr2 Ktr3 Ktr6ric)# 1700'00 2700'00 3700'00 700'00

oo% 7700'00 700'00 700'00 8700'00

;la## 9700'00 10700'00 11700'00 12700'00

ype 1 2 or 3 for t!e pro%uct 1

ype 1 2 3 or for t!e Ktr 3ype 1 for Nort! or 2 for Sout! 2

!e re#ult i# Err70 NDEY/Nort!+n%Sout!FFF85

Example

!i# i# an exten%e% $er#ion of t!e pre$iou# example'

t allo"# t!e name# of pro%uct# an% t!e *uarter# to &e entere%'

!e =+


54/139


ND:EPage 7 of 139

+N+>E'#

,an Fe& =ar

Nort! 10 20 30

Sout! 0 70 0Ea#t 0 80 90

e#t 100 110 120

ype a%%re## of any of t!e cell# in t!e a&o$e ta&le #uc! a# ; ;

!e $alue in t!e cell you type% i# 80 ND:E/


55/139


NF(Page 77 of 139

+NF4

Sy#tem nformation

urrent %irectory Err702 NF(/4%irectory45

+$aila&le &yte# of memory Err702 NF(/4mema$ail45

=emory in u#e Err702 NF(/4memu#e%45otal &yte# of memory Err702 NF(/4totmem45

Num&er of acti$e "or)#!eet# 1 NF(/4numfile45

ell currently in t!e top left of t!e "in%o" Err702 NF(/4origin45

(perating #y#tem in%o"# /32-&it5 N 7'01 NF(/4o#$er#ion45

:ecalculation mo%e +utomatic NF(/4recalc45

Excel $er#ion 20m0/6uil%25 NF(/4relea#e45

Name of #y#tem' /P or =ac5 .NCY NF(/4#y#tem45


56/139


NPage 7 of 139

+N#

Num&er nteger

1'7 1 N/5

2'3 2 N/75

10'7 10 N/5-1'789 -2 N/5


57/139


NPage 7 of 139

!e rea#on for u#ing 37'27 i# to ta)e account of t!e leap year#'A B C D E F G H I J

59


58/139


S6.+NBPage 78 of 139

+S6L5N;

Data # !e ell 6lan)

1 0 S6.+NB/5


59/139


SE::Page 79 of 139

+SE>>

ell to te#t :e#ult

3 0 SE::/D5

UDIJ0? 1 SE::/D75UN+=EW 1 SE::/D5

UI+.CE? 1 SE::/D5

UI+.CE? 1 SE::/D85

UI+.CE? 1 SE::/D95

UNJ+ 0 SE::/D105


60/139


SE::(:Page 0 of 139

+SE>>4>

ell to te#t :e#ult

3 0 SE::(:/D5

UDIJ0? 1 SE::(:/D75UN+=EW 1 SE::(:/D5

UI+.CE? 1 SE::(:/D5

UI+.CE? 1 SE::(:/D85

UI+.CE? 1 SE::(:/D95

UNJ+ 1 SE::(:/D105


61/139


SEIENPage 1 of 139

+SE?EN

Num&er # it E$en

1 0 SEIEN/5

2 1 SEIEN/752'7 1 SEIEN/5

2' 1 SEIEN/5

3'7 0 SEIEN/85

3' 0 SEIEN/95


62/139


S.(;+.Page 2 of 139

+SL4@+'5L

ell o e#t :e#ult

0 0 S.(;+./D5

1 0 S.(;+./D750 S.(;+./D5

20 0 S.(;+./D5

1-,an-98 0 S.(;+./D85


63/139


SN+Page 3 of 139

+SN5

Num&er :e#ult

1 0 SN+/5


64/139


SN(NEYPage of 139

+SN4N#EI#

tem o e#t # t + Num&erW

10 1 SN(NEY/5


65/139


SNC=6E:Page 7 of 139

+SN*M6E>

ell Entry :e#ult

1 1 SNC=6E:/D5

1-,an-98 1 SNC=6E:/D750 SNC=6E:/D5

UDIJ0? 0 SNC=6E:/D5


66/139


S(DDPage of 139

+S4

Num&er # it (%%

1 1 S(DD/5

2 0 S(DD/75

2'7 0 S(DD/52' 0 S(DD/5

3'7 1 S(DD/85

3' 1 S(DD/95


67/139


S:EFPage of 139

+S>EF

1 S:EF/+15

0 S:EF/6995

UN+=EW S:EF/


68/139


SEYPage 8 of 139

+S#EI#

ell o e#t :e#ult


69/139


.+:;EPage 9 of 139

L5>@E

Ialue#


70/139


.EFPage 0 of 139

LEF#

ext .eft String +lan ,one# 1 + .EF/D5

+lan ,one# 2 +l .EF/7D75

+lan ,one# 3 +la .EF/D5

ar%iff ar%if .EF/D5

+6123 +61 .EF/8D85


71/139


.ENPage 1 of 139

LEN

ext .engt!

+lan ,one# 10 .EN/5

6o& Smit! 9 .EN/75arol illiam# 1 .EN/5

ar%iff .EN/5

+6123 .EN/85


72/139


.((BCP /+rray5Page 2 of 139

L44;*P 05rra

Name Jan Fe$ Mar

5lan 10 80 K

6o$ 20 90 .K'arol 30 100 &

avid 0 110 1

Eric 70 120

Francis 0 130 2

@ail 0 10 3

ype a Name in t!i# cell Eric

.((BCP/F12D;105


73/139


.((BCP /+rray5Page 3 of 139

column !ea%ing of ,an i# loo)up range'

not inclu%e% in t!e loo)up

range' +lan 6o& arol Da$i%

,an ,an 100 100 100 100

+lan 100

6o& 100

arol 100

Da$i% 100

Eric 100

Fre% 100

Formatting

No #pecial formatting i# nee%e%'

Pro$lems

!e li#t of information to &e loo)e% t!roug! mu#t &e #orte% in a#cen%ing or%er ot!er"i#e error#

"ill occur eit!er a# UNJ+ or incorrect re#ult#'

a&le 1 #!o"# t!e Name column #orte% alp!a&etically t!e re#ult# of u#ing .((BCP/5 "ill

&e correct'

a&le 2 #!o"# t!e #ame %ata &ut not #orte%' Sometime# t!e re#ult# "ill &e correct &ut ot!er

time# t!e re#ult "ill &e an UNJ+ error or incorrect figure'

#a$le 1 #a$le 2

Name ,an Fe& =ar Name ,an Fe& =ar

5lan 10 80 K avid 0 110 1

6o$ 20 90 .K Eric 70 120

'arol 30 100 & 5lan 10 80 Kavid 0 110 1 6o$ 20 90 .K

Eric 70 120 'arol 30 100 &

Francis 0 130 2 Francis 0 130 2

@ail 0 10 3 @ail 0 10 3

Name Eric Name Eric

Ialue Ialue

.((BCP/88680E85 .((BCP/


74/139


.((BCP /Iector5Page of 139

L44;*P 0?ector

Name ,an Fe& =ar

5lan 10 C 9

6o$ 20 KC 9'arol 30 1CC 7

avid 0 11C 71

Eric 70 12C

Francis 0 13C 28

@ail 0 1&C 3

ype a Name in t!i# cell Eric

120 .((BCP/F12D;10FF105


75/139


.(E:Page 7 of 139

L4

Cpper a#e ext .o"er a#e

+.+N ,(NES alan Rone# .(E:/5

6(6 S=< &o& #mit! .(E:/75+:(. ..+=S carol "illiam# .(E:/5

+:DFF car%iff .(E:/5

+6123 a&c123 .(E:/85


76/139


=+<Page of 139

M5#'D

Name# Ialue#

6o& 270

+lan 00Da$i% 1000

arol 000

ype a name to loo) for +lan ype a $alue 1000

!e po#ition of +lan i# 2 Ialue po#ition 3

=+


77/139


=+<Page of 139

+#cen%ing De#cen%ing rong Ialue

10 0 10

20 30 20

30 20 30

0 10 0

20 20 27

2 UNJ+ 2

=+


78/139


=+<Page 8 of 139

f an exact matc! i# not foun% t!e next lo"e#t &rea)point i# u#e%'

!e NDEY/5 function t!en loo)# %o"n t!e ;ra%e li#t to fin% t!e gra%e'

Exam Score ;ra%e Pupil Score ;ra%e

0 Fail +lan 0 Pa##

70 Pa## 6o& Fail

90 =erit arol 9 Di#tinction

97 Di#tinction Da$i% 89 Pa##

NDEY/D111D11=+


79/139


=+YPage 9 of 139

M5I

Ialue# =aximum

120 800 100 120 270 800 =+Y/;5

Date# =aximum

1-,an-98 27-Dec-98 31-=ar-98 2-Dec-98 -,ul-98 2-Dec-98 =+Y/;5


80/139


=ED+NPage 80 of 139

ME+5N

Ialue1 Ialue2 Ialue3 Ialue Ialue7 =e%ian

20 70 10 30 0 30 =ED+N/;5

2000 1000 10 20 8000 1000 =ED+N/;5

10 20 0 0 0 0 =ED+N/8;85

Ialue1 Ialue2 Ialue3 Ialue =e%ian

20 0 30 10 27 =ED+N/11F115

20 20 0 20 20 =ED+N/13F135


81/139


=DPage 81 of 139

M+

ext =i% String

+6DEDF 1 3 +6 =D/DE5 +6DEDF 2 3 6D =D/7D7E75

+6DEDF 7 2 ED =D/DE5

+6-100-DEF 100 =D/8735

+6-200-DEF 200 =D/9735

+6-300-DEF 300 =D/10735

tem Sie .arge .arge =D/1212995

tem Sie =e%ium =e%ium =D/1312995

tem Sie Small Small =D/112995


82/139


=DPage 82 of 139

Fin% t!e fir#t J plu# 1 for t!e Start of t!e co%e'

Fin% t!e #econ% J occurring after t!e fir#t J

alculate t!e lengt! of t!e text to extract &y #u&tracting t!e po#ition

of t!e fir#t J from t!e po#ition of t!e #econ% J

A B C D E F G H

53

54

55

56


83/139


=NPage 83 of 139

M+N

Ialue# =inimum

120 800 100 120 270 100 =N/;5

Date# =aximum1-,an-98 27-Dec-98 31-=ar-98 2-Dec-98 -,ul-98 1-,an-98 =N/;5


84/139


=(DPage 8 of 139

M4

Num&er Di$i#or :emain%er

12 7 2 =(D/D5

20 =(D/7D75

18 3 0 =(D/D5

9 2 1 =(D/D5

2 3 =(D/8D85


85/139


=(DEPage 87 of 139

M4E

Ialue1 Ialue2 Ialue3 Ialue Ialue7 =o%e

20 70 10 10 0 10 =(DE/;5

0 20 0 10 0 0 =(DE/;5

10 10 99 20 20 10 =(DE/8;85

20 20 99 10 10 10 =(DE/9;95

10 20 20 99 10 10 =(DE/10;105

10 20 30 0 70 UI+.CE? =(DE/12;125


86/139


=(DEPage 8 of 139

Note

f t!e +IE:+;E/5 function !a% &een u#e% t!e an#"er "oul% !a$e &een 9'

!i# figure i# of no &enefit to t!e #!op)eeper a# t!ere are no garmet# of t!i# #ie?

A B C D E F G H I J

54

55

56


87/139


=(N<Page 8 of 139

M4N#D

(riginal Date =ont!

1-,an-98 1 =(N


88/139


=:(CNDPage 88 of 139

M>4*N

Num&er =ultiple

110 70 100 =:(CND/D5120 70 100 =:(CND/7D75

170 70 170 =:(CND/D5

10 70 170 =:(CND/D5

10 70 170 =:(CND/8D85


89/139


NPage 89 of 139

N

(riginal on$erte%

1 1 N/5

3 1J2 3'7 N/753'7 3'7 N/5

3'70@ 0'037 N/5

27-Dec-98 317 N/85

1 1 N/95

0 0 N/105


90/139


N+Page 90 of 139

N5

UNJ+ N+/5

Ialue e#t10 11 F/S6.+NB/5N+/5G15

UNJ+ F/S6.+NB/5N+/5G15

30 31 F/S6.+NB/85N+/58G15

Sale#

Nort! 100

Sout! UNJ+ N+/5

Ea#t UNJ+ N+/5

e#t 200

otal UNJ+ SC=/D11D15


91/139


NE(:BD+>SPage 91 of 139

NE#;5GS

Start Date En% Date or) Day#

1-=ar-98 -=ar-98 7 NE(:BD+>S/D5

27-+pr-98 30-,ul-98 9 NE(:BD+>S/7D752-Dec-98 7-,an-99 9 NE(:BD+>S/D5

S/StartDateEn%DateS/628283335

=on 02-=ar-98 Fri 13-=ar-98 10 NE(:BD+>S/629293335

=on 2-+pr-98 Fri 01-=ay-98 NE(:BD+>S/630303335

ear 1-,an-98

Ne" >ear 1-,an-99

A B C D E F

1

2

3

4

56

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


92/139


N(:<Page 92 of 139

Nort!ern %ata'


+lan

,an Fe& =ar otal

+lan 10 20 30 0

6o& 0 70 0 170arol 0 80 90 20

otal 120 170 180 70

A B C D E F G H I J

1

2

3

4

5

6

7

8

9


93/139


N(Page 93 of 139

N4#

ell# o e#t :e#ult

10 20 1 N(/D5

10 20 1 N(/7D7510 20 0 N(/QD5

1-,an-98 1-Fe&-98 1 N(/D5


94/139


N(Page 9 of 139

N4<

!e current Date an% ime

9J7J2017 1133 N(/5

2272'81118222 N(/5


95/139


(DDPage 97 of 139

4

Num&er 2 3 (DD/5

2' 3 (DD/75

2'9 3 (DD/5

3 3 (DD/5

3' 7 (DD/85

3'9 7 (DD/95


96/139


(:Page 9 of 139

4>

(r%er No' o#t

+6001 1000 a#! - F/(:/E4Ii#a4E4Delta45705 +6002 1000 Ii#a 7'00 F/(:/E74Ii#a4E74Delta45705

+6003 2000 !e*ue - F/(:/E4Ii#a4E4Delta45705

+600 7000 Delta 7'00 F/(:/E4Ii#a4E4Delta45705


97/139


P:(PE:Page 9 of 139

P>4PE>

(riginal ext Proper

alan Rone# +lan ,one# P:(PE:/5

&o& #mit! 6o& Smit! P:(PE:/75ca:ol "..+=S arol illiam# P:(PE:/5

car%iff ar%iff P:(PE:/5

+6123 +&c123 P:(PE:/85


98/139


KC+:.EPage 98 of 139

!*5>#+LE

Ialue# Kuarter No' Kuartile

1 0 1 KC+:.E/8E5

27 1 27 KC+:.E/8E7570 2 70 KC+:.E/8E5

7 3 7 KC+:.E/8E5

100 100 KC+:.E/8E85

Ialue# Kuarter No' Kuartile

81 10 0 0 10 KC+:.E/12F1


99/139


KC(ENPage 99 of 139

!*4#+EN#

Num&er Di$i#or :e#ult

12 7 2 KC(EN/D5

20 3 KC(EN/7D75

17 3 KC(EN/D5


100/139


:+NDPage 100 of 139

>5N

:an%om greater t!an or e*ual to 0 &ut le## t!an 1'

0'130917 :+ND/5

:an%om greater t!an or e*ual to 0 &ut le## t!an 10

'9988111 :+ND/5A10

:an%om &et"een 7 an% 10'

'03071827 :+ND/5A/10-75G7


101/139


:+NDPage 101 of 139

lu 12 0'0993 9 0'321317


102/139


:+NBPage 102 of 139

>5N;

Ialue# :+NB/85

7 :+NB/785

27 1 :+NB/85

8 3 :+NB/85

1 2 :+NB/885

Ialue#

2 :+NB/11111715

1 :+NB/12111715

27 7 :+NB/131117158 3 :+NB/1111715

1 :+NB/17111715

Ialue#

10 7 :+NB/1818225

30 2 :+NB/1918225

20 :+NB/2018225

30 2 :+NB/2118225

0 1 :+NB/2218225


103/139


:+NBPage 103 of 139

!e follo"ing ta&le "a# u#e% to recor% t!e time# for at!lete# competing in a race'

!e :+NB/5 function "a# t!en u#e% to fin% t!eir race po#ition# &a#e% upon t!e fini#!ing time#'

+t!lete ime :ace Po#ition

,o!n 130 :+NB/73737815

+lan 17 :+NB/7737815Da$i% 102 1 :+NB/77737815

6rian 13 7 :+NB/7737815

Sue 12 3 :+NB/7737815

+lex 103 2 :+NB/78737815

A B C D E F G H I

49

50

51

52

53

54

55

56

57

58


104/139


:EP.+EPage 10 of 139

>EPL5'E

(riginal ext +6DEF;< 2 1 x +xDEF;< :EP.+E/DEF5

+6DEF;< 2 7 x +x;< :EP.+E/7D7E7F75

+6DEF;< 2 1 !ello +!elloDEF;< :EP.+E/DEF5

+6DEF;< 2 7 !ello +!ello;< :EP.+E/DEF5


105/139


:;+@D#

+lan ,one# 1 # :;


106/139


:(=+NPage 10 of 139

>4M5N

Num&er :oman

1 :(=+N/5

2 :(=+N/753 :(=+N/5

7 I :(=+N/5

10 Y :(=+N/85

1998 ==YI :(=+N/95

1998 ==YI :(=+N/1005

1998 =.=I. :(=+N/1115

1998 =Y=I :(=+N/1225

1998 =I= :(=+N/1335

1998 =I= :(=+N/15

1998 =.=I. :(=+N/17:CE5

1998 ==YI :(=+N/1F+.SE5


107/139


:(CNDPage 10 of 139

>4*N

Num&er 1'789 0 1 :(CND/D5

1'789 1 1'7 :(CND/7D75

1'789 2 1'8 :(CND/D5

133'789 -1 130 :(CND/D5

133'789 -2 1300 :(CND/8D85

133'789 -3 1000 :(CND/9D95


108/139


:(CNDD(NPage 108 of 139

>4*N4


109/139


:(CNDCPPage 109 of 139

>4*N*P

Num&er 1'789 0 2 :(CNDCP/D5

1'789 1 1'7 :(CNDCP/7D75

1'789 2 1'8 :(CNDCP/D5

133'8 -1 1370 :(CNDCP/D5

133'8 -2 1300 :(CNDCP/8D85

133'8 -3 1000 :(CNDCP/9D95


110/139


SE(NDPage 110 of 139

SE'4N

Num&er Secon%

07JSepJ17 11331 1 SE(ND/5

120000 P= 0 SE(ND/750'70 0 SE(ND/5

0'71 2 SE(ND/5

1'71 2 SE(ND/85


111/139


S;NPage 111 of 139

S+@N

Ialue

10 1 S;N/520 1 S;N/75

0 0 S;N/5

-10 -1 S;N/5

-20 -1 S;N/85


112/139


S=+..Page 112 of 139

SM5LL

Ialue# .o"e#t Ialue 100 S=+../815

120 2n% .o"e#t Ialue 120 S=+../825

800 3r% .o"e#t Ialue 120 S=+../835

100 t! .o"e#t Ialue 270 S=+../85120 7t! .o"e#t Ialue 800 S=+../875

270


113/139


S(C<Page 113 of 139

Sout!ern %ata'


,an Fe& =ar otal

+lan 100 200 300 00

6o& 00 700 00 1700arol 00 800 900 200

otal 1200 1700 1800 700

A B C D E F G H I J

1

2

3

4

5

67

8


114/139


SDEIPage 11 of 139

S#E?

Ialue# Ialue# Ialue#

10 10 10

10 10 11

9 11 910 10 12

0'7 0'7 1'29099

SDEI/5 SDEI/EE5 SDEI/;;5


115/139


SDEIPPage 117 of 139

S#E?P

Ialue# Ialue# Ialue#

10 10 10

10 10 119 11 9

10 10 12

0'33013 0'33013 1'11803

SDEIP/5 SDEIP/EE5 SDEIP/;;5


116/139


SC6SCEPage 11 of 139

S*6S#+#*#E

(riginal ext Cp%ate% ext

+6DEF D !ello +6!elloEF SC6SCE/6D5 +6D+6D D !ello +6!ello+6!ello SC6SCE/677D75

Nort!ern :egion :egion +rea Nort!ern +rea SC6SCE/6D5

San% an% ement an% X SX X ement SC6SCE/6D5

(riginal ext Cp%ate% ext

+6+6+6 +6 !ello 3 +6+6!ello

San% an% ement an% X 2 San% X ement

SC6SCE/61010D10E105

SC6SCE/61111D11E115


117/139


SC6SCEPage 11 of 139

SC6SCE/P:(PE:/6705P:(PE:/705P:(PE:/D7055A B C D E F G H

51


118/139


SC=Page 118 of 139

S*M


119/139


SC=La#L:unningLotalPage 119 of 139

S*M 0>unning #otal

*sing /S*M0 For 5 >unning #otal

=ont! Sale#

,an 10 10 SC=/DD5

Fe& 70 0 SC=/DD85

=ar 30 90 SC=/DD95

+pr 20 110 SC=/DD105

=ay 110 SC=/DD115

,un 110 SC=/DD125

,ul 110 SC=/DD135

+ug 110 SC=/DD15

Sep 110 SC=/DD175

(ct 110 SC=/DD15No$ 110 SC=/DD15

Dec 110 SC=/DD185

ype t!e formula SC=/DD5 in cell E an% t!en copy %o"n t!e ta&le'

t "or)# &ecau#e t!e fir#t reference u#e# %ollar #ym&ol# to )eep D #tatic

a# t!e formula i# copie% %o"n' Eac! occurrence of t!e SC=/5 t!en a%%# all

t!e num&er# from t!e fir#t cell %o"n'

!e function can &e ti%ie% up to #!o" 0 ero "!en t!ere i# no a%Racent $alue

&y u#ing t!e F/5 function'

=ont! Sale#

,an 10 10 SC=/F/DDD055

Fe& 70 0 SC=/F/D8DD8055

=ar 30 90 SC=/F/D9DD9055

+pr 20 110 SC=/F/D10DD10055

=ay 0 SC=/F/D11DD11055

,un 0 SC=/F/D12DD12055

,ul 0 !e SC=/5 only ta)e# place "!en

+ug 0 t!ere i# %ata in column D'

Sep 0 (t!er"i#e t!e $alue 0 ero i# entere%'

(ct 0

No$ 0Dec 0

:unningotal

:unningotal

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


120/139


SC=L"it!L(FFSEPage 120 of 139

S*M and the /4FFSE# function

Sometime# it i# nece##ary to &a#e a calculation on a #et of cell# in %ifferent location#'

+n example "oul% &e "!en a total i# re*uire% from certain mont!# of t!e year #uc! a#

t!e la#t 3 mont!# in relation to t!e current %ate'

(ne #olution "oul% &e to retype t!e calculation eac! time ne" %ata i# entere% &ut t!i#

"oul% &e time con#uming an% open to !uman error'

+ &etter "ay i# to in%icate t!e #tart an% en% point of t!e range to &e calculate% &y

u#ing t!e (FFSE/5 function'

!e (FFSE/5 pic)# out a cell a certain num&er of cell# a"ay from anot!er cell'

6y gi$ing t!e (FFSE/5 t!e a%%re## of t!e fir#t cell in t!e range "!ic! nee%# to

&e totalle% "e can t!en in%icate !o" far a"ay t!e en% cell #!oul% &e an% t!e (FFSE/5

"ill gi$e u# t!e a%%re## of cell "!ic! "ill &e t!e en% of t!e range to &e totalle%'

!e (FFSE/5 nee%# to )no" t!ree t!ing#V

1' + cell a%%re## to u#e a# t!e fixe% point from "!ere it #!oul% &a#e t!e off#et'

2'


121/139


SC=L"it!L(FFSEPage 121 of 139

i# t!en totalle%'

1700 10 &CC CC .CC 00

!e cell E7 !a# &een u#e% a# t!e #tarting point for &ot! off#et# t!e fir#t off#et i#

off#et &y 1 column t!e #econ% &y 3 column#' !e re#ult i# t!e range F7


122/139


SC=FPage 122 of 139

S*M+F

tem Date o#t

6ra)e# 1-,an-98 80

yre# 10-=ay-98 27

6ra)e# 1-Fe&-98 80Ser$ice 1-=ar-98 170

Ser$ice 7-,an-98 300

in%o" 1-,un-98 70

yre# 1-+pr-98 200

yre# 1-=ar-98 100

lutc! 1-=ay-98 270

otal co#t of all 6ra)e# &oug!t' 10 SC=F/1246ra)e#4EE125

otal co#t of all yre# &oug!t' 327 SC=F/124yre#4EE125

otal of item# co#ting H100 or a&o$e' 1000 SC=F/EE12410045

otal of item type% in follo"ing cell' #er$ice 70 SC=F/12E18EE125


123/139


SC=P:(DCPage 123 of 139

S*MP>4*'#

tem Sol% price

yre# 7 100

Filter# 2 106ul 3 2

otal Sale# Ialue 72 SC=P:(DC/DDEE5


124/139


Page 12 of 139

#

ell o e#t :e#ult


125/139


EYPage 127 of 139

#EI#

10 10'00 EY/40'004510 H10'00 EY/74H0'0045

10 10 EY/4045

10 H10 EY/4H045

10'27 10'3 EY/840'045

10'27 H10'3 EY/94H0'045


126/139


=EPage 12 of 139

#+ME


127/139


=EI+.CEPage 12 of 139

#+ME?5L*E

ext ime

13079 0'08973 =EI+.CE/5

13079 13079 =EI+.CE/7513079 23079 P= =EI+.CE/5


128/139


(D+>Page 128 of 139

#45G

o%ay #

7-Sep-17 (D+>/5

> format'

Example

!e follo"ing example #!o"# !o" t!e o%ay function i# u#e% to calculate t!e num&er

of %ay# #ince a particular %ay'

Date Day# Since

1-,an-9 821 (D+>/5-20

10-+ug-9 00 (D+>/5-21

Note t!at t!e re#ult i# actually t!e num&er of %ay# &efore to%ay# %ate' o calculate

a re#ult "!ic! inclu%e# t!e current %ate an extra 1 "ill nee% to &e a%%e%'

Date Day# Since

1-,an-9 822 (D+>/5-28G1

10-+ug-9 01 (D+>/5-29G1

Example

!e follo"ing example #!o"# t!e num&er of %ay# from to%ay until t!e year 2000'

>ear 2000 Day# Cntil

01-,an-2000 -72 3-(D+>/5

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

1819

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36


129/139


:+NSP(SEPage 129 of 139

#>5NSP4SE

,an Fe&

+lan 10 30

6o& 0 70arol 0 80

otal 120 10

+lan 6o& arol otal

,an 10 0 0 120

Fe& 30 70 80 10

Z:+NSP(SE/3E5[

+# an array formula in all t!e#e cell#


130/139


:=Page 130 of 139

#>+M

(riginal ext rimme% ext

+6D +6D :=/5

+ 6 D + 6 D :=/75 +lan ,one# +lan ,one# :=/5

+6D +6D :=/5


131/139


:CNPage 131 of 139

#>*N'

Num&er 1'789 0 1 :CN/D5

1'789 1 1' :CN/7D75

1'789 2 1' :CN/D5

-1'789 1 -1' :CN/D5

-1'789 2 -1' :CN/8D85

133'8 -1 130 :CN/9D95

133'8 -2 1300 :CN/10D105

133'8 -3 13000 :CN/11D115


132/139


CPPE:Page 132 of 139

*PPE>

(riginal ext Cpper a#e

alan Rone# +.+N ,(NES CPPE:/5

&o& #mit! 6(6 S=< CPPE:/75

car(l "i..iamS +:(. ..+=S CPPE:/5

car%iff +:DFF CPPE:/5

a&c123 +6123 CPPE:/85

'

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


133/139


I+.CEPage 133 of 139

?5L*E

ext ontaining + Num&er Ialue

+nnual turno$er "a# H7000 Err702 I+.CE/=D/SE+:


134/139


I.((BCPPage 13 of 139

?L44;*P

The column numbers are not needed.

they are part of the illustration.

col 1 col 2 col 3 col col ! col " ,an 10 20 30 0 70

Fe& 80 90 100 110 120

=ar 9 9 7 71

ype a mont! to loo) for Fe&

!ic! column nee%# to &e pic)e% out

!e re#ult i# 100

I.((BCP/;11


135/139



ype a name to loo) for eric

ype a mont! to loo) for mar

!e re#ult i# 9

I.((BCP/F770F7=+


136/139



t!e 100 ro" i# u#e%'

I.((BCP/D12F1111=+


137/139


EEBD+>Page 13 of 139

/5

!u 01-,an-98 7 EEBD+>/75!u 01-,an-98 7 EEBD+>/15

!u 01-,an-98 EEBD+>/25

!u 01-,an-98 3 EEBD+>/835

/3539D75

6oo)ing :ate#

Day (f ee) o#t

1 70'00

2 27'00

3 27'00

30'00

7 0'00

70'00

100'00

o #!o" t!e re#ult a# t!e name of t!e %ay u#e Format 'ells 'ustom an% #et

t!e #pe to ddd or dddd'

A B C D E F G H

1

2

3

4

56

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


138/139


(:BD+>Page 138 of 139

;5G

StartDate Day# :e#ult

1-,an-98 28 3783 (:BD+>/DE5

1-,an-98 28 10-Fe&-98 (:BD+>/D7E75

/StartDateDay#


139/139


>E+:Page 139 of 139

GE5>

Date >ear

UUU 1998 >E+:/5

atoz excel formulas

Documents