Practical Astronomy with your
Calculator or Spreadsheet
Fourth Edition
Now in its fourth edition, this highly regarded book
is ideal for those who wish to solve a variety of prac-
tical and recreational problems in astronomy using
a scientific calculator or spreadsheet.
Updated and extended, this new edition shows
you how to use spreadsheets to predict, with greater
accuracy, solar and lunar eclipses, the positions of
the planets, and the times of sunrise and sunset.
With clear, easy-to-follow instructions, shown along-
side worked examples, this handbook is essential for
anyone wanting to make astronomical calculations
for themselves. It can be enjoyed by anyone inter-
ested in astronomy, and will be a useful tool for
software writers and students studying introductory
astronomy.
Gives easy-to-understand, simplified methods for
use with a pocket calculator.
Covers orbits, transformations and general celes-
tial phenomena, for use anywhere, worldwide.
High-precision spreadsheet methods for greater
accuracy are available at
www.cambridge.org/practicalastronomy.
Peter Duffett-Smith is a physicist by training and a radio
astronomer by trade. He is a Reader in Experimen-
tal Radio Physics at the Cavendish Laboratory, Uni-
versity of Cambridge, and is a Fellow of Downing
College, Cambridge and of the Royal Astronomical
Society.
Jonathan Zwart is a Postdoctoral Research Scientist at
the Columbia Astrophysics Laboratory in New York,
and a co-founder and former editor of Cambridge’s
science magazine, BlueSci.
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Practical Astronomy with your
Calculator or Spreadsheet
Fourth Edition
Peter Duffett-Smith
Downing College, Cambridge
Jonathan Zwart
Columbia University in the City of New York
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
www.cambridge.org
Information on this title: www.cambridge.org/9780521146548
© Cambridge University Press 1979, 1982, 1989
© Peter Duffett-Smith and Jonathan Zwart 2011
This publication is in copyright. Subject to statutory exception
and to the provisions of relevant collective licensing agreements,
no reproduction of any part may take place without the written
permission of Cambridge University Press.
First published 1979
Second edition 1982
Third edition 1989
Fourth edition 2011
2015
Printed in the United Kingdom by Clays, St Ives plc.
A catalogue record for this publication is available from the British Library
Library of Congress Cataloguing in Publication data
Duffett-Smith, Peter.
Practical astronomy with your calculator or spreadsheet / Peter Duffett-Smith, Jonathan Zwart. – 4th ed.
p. cm.
Rev. ed. of: Practical astronomy with your calculator / Peter Duffett-Smith. 3rd ed. 1988.
Includes bibliographical references and index.
ISBN 978-0-521-14654-8 (pbk.)
1. Astronomy – Problems, exercises, etc. 2. Calculators – Problems, exercises, etc. 3. Electronic spreadsheets in
education. I. Zwart, Jonathan. II. Duffett-Smith, Peter. Practical astronomy with your calculator. III. Title.
QB62.5.D83 2011
520.76 – dc22 2010041671
ISBN 978-0-521-14654-8 Paperback
Additional resources for this publication at www.cambridge.org/practicalastronomy
Cambridge University Press has no responsibility for the persistence or
accuracy of URLs for external or third-party internet websites referred to
in this publication, and does not guarantee that any content on such
websites is, or will remain, accurate or appropriate.
University Printing House, Cambridge CB2 8BS, United Kingdom
Cambridge University Press is part of the University of Cambridge.
It furthers the University’s mission by disseminating knowledge in the pursuit of
education, learning and res earch at the highest
internationa
l
levels of
excellence.
4th printing
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
To our friends and
colleagues at MRAO
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Contents
Preface to the fourth edition page xi
About this book and how to use it xiii
A word about spreadsheets what are they? xv
The layout of spreadsheets in this book xviii
Calculations involving multiple sheets xix
Using our own functions xxi
Time 1
1 Calendars 2
2 The date of Easter 3
3 Converting the date to the day number 6
4 Julian dates 8
5 Converting the Julian date to the Greenwich calendar date 11
6 Finding the name of the day of the week 12
7 Converting hours, minutes and seconds to decimal hours 14
8 Converting decimal hours to hours, minutes and seconds 15
9 Converting the local time to universal time (UT) 16
10 Converting UT and Greenwich calendar date to local time and date 20
11 Sidereal time (ST) 22
12 Conversion of UT to Greenwich sidereal time (GST) 23
13 Conversion of GST to UT 24
14 Local sidereal time (LST) 27
15 Converting LST to GST 28
16 Ephemeris time (ET) and terrestrial time (TT) 30
Coordinate systems 33
17 Horizon coordinates 34
18 Equatorial coordinates 35
19 Ecliptic coordinates 37
20 Galactic coordinates 38
21 Converting between decimal degrees and degrees, minutes and seconds 39
22 Converting between angles expressed in degrees and angles expressed in hours 41
23 Converting between one coordinate system and another 42
vii
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
viii Contents
24 Converting between right ascension and hour angle 43
25 Equatorial to horizon coordinate conversion 47
26 Horizon to equatorial coordinate conversion 49
27 Ecliptic to equatorial coordinate conversion 51
28 Equatorial to ecliptic coordinate conversion 55
29 Equatorial to galactic coordinate conversion 56
30 Galactic to equatorial coordinate conversion 58
31 Generalised coordinate transformations 60
32 The angle between two celestial objects 66
33 Rising and setting 67
34 Precession 71
35 Nutation 76
36 Aberration 78
37 Refraction 80
38 Geocentric parallax and the figure of the Earth 83
39 Calculating corrections for parallax 85
40 Heliographic coordinates 88
41 Carrington rotation numbers 94
42 Selenographic coordinates 95
43 Atmospheric extinction 99
The Sun 101
44 Orbits 102
45 The apparent orbit of the Sun 103
46 Calculating the position of the Sun 103
47 Calculating orbits more precisely 107
48 Calculating the Sun’s distance and angular size 110
49 Sunrise and sunset 112
50 Twilight 114
51 The equation of time 116
52 Solar elongations 118
The planets, comets and binary stars 119
53 The planetary orbits 120
54 Calculating the coordinates of a planet 121
55 Finding the approximate positions of the planets 131
56 Perturbations in a planet’s orbit 132
57 The distance, light-travel time and angular size of a planet 136
58 The phases of the planets 137
59 The position-angle of the bright limb 138
60 The apparent brightness of a planet 140
61 Comets 143
62 Parabolic orbits 151
63 Binary-star orbits 155
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Contents ix
The Moon and eclipses 161
64 The Moon’s orbit 162
65 Calculating the Moon’s position 164
66 The Moon’s hourly motions 170
67 The phases of the Moon 171
68 The position-angle of the Moon’s bright limb 175
69 The Moon’s distance, angular size and horizontal parallax 176
70 Moonrise and moonset 178
71 Eclipses 181
72 The ‘rules’ of eclipses 183
73 Calculating a lunar eclipse 184
74 Calculating a solar eclipse 190
75 The Astronomical Calendar 194
Glossary of terms 197
Symbols and abbreviations 205
Bibliography 208
A useful website 209
Index 210
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Preface to the fourth edition
Practical Astronomy with your Calculator or Spreadsheet has been written for those who wish to calculate
the positions and visual aspects of the major heavenly bodies and important phenomena such as eclipses, ei-
ther for practical purposes or simply because they enjoy making predictions. We present recipes for making
calculations, where we have cut a path through the complexities and difficult concepts of rigorous mathe-
matics, taking account only of those factors that are essential to each calculation and ignoring corrections
for this and that, necessary only for very precise predictions of astronomical phenomena. Our simple meth-
ods, suitable for use with a pocket calculator, are usually sufficient for all but the most exacting amateur
astronomer, but they should not be used for navigational purposes. For example, the times of sunrise and
sunset can be determined to within 1 minute and the position of the Moon to within one fifth of a degree.
But new to this fourth edition are spreadsheets which offer much higher precision (see below).
The second edition included much more material in response to letters and requests from readers of the
first edition. Many errors were also corrected. The third edition continued the same process, adding four
new sections on generalised coordinate transformations, nutation, aberration and selenographic coordinates,
improving the sunrise/set and moonrise/set calculations so that they worked properly everywhere in the
world, including a rigorous method of calculating precession, taking account of the J2000 astronomical
system where appropriate, and correcting mistakes or clarifying obscurities wherever they were found in
the s econd edition.
The fourth edition has also been updated considerably; however the major change is that we have in-
cluded, for the first time, a spreadsheet for nearly every calculation. Each spreadsheet illustrates the cal-
culation, making it easier to get the right answer. But we have also written a library of powerful functions
which can carry out many of the calculations for you with much higher precision, so those people who wish
to use their computers can do so and obtain the benefits of greater accuracy. For example, use the simple
recipes and your calculator to find the times of moonrise and moonset to within a precision of 10 minutes
or so, or use the spreadsheet functions to obtain the results correct to within 1 minute. You will need to
visit our website (see page 209) to download the spreadsheets to your computer; the library of functions
will come automatically with the spreadsheets.
We are most grateful to those kind people who have taken the trouble to write in with their suggestions,
criticisms and corrections, in particular to Mr E. R. Wood, who kindly scanned the manuscript of the
third edition for errors, Mr S. Hatch, Mr S. J. Garvey, who supplied the nomogram for the solution of
Kepler’s equation, and Mr Anthony Ehrlich of Pittsburgh, Pennsylvania, who developed a rudimentary
scheme for calculating the circumstances of sunrise/set and moonrise/set into one that actually worked
xi
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
xii Preface
(superseded in this edition). We would also like to thank and acknowledge those authors whose books we
have read and whose ideas we have cribbed, mentioning particularly Jean Meeus (Astronomical Formulae
for Calculators) and W. Schroeder (Practical Astronomy). We have made extensive use of The Explanatory
Supplement to the Astronomical Ephemeris and the American Ephemeris and Nautical Almanac,aswellas
the Astronomical Almanac and its predecessors.
Our thanks are also due to Dr Anthony Winter, who suggested writing the first edition of the book, to
Mrs Dunn who typed it, to Dr Guy Pooley who read the manuscript and made many helpful suggestions,
and to Dr Simon Mitton for taking so much trouble over the production of the book. Thanks for particular
help with the fourth edition go to William Lancaster, Sehar Tahir and our editor Vince Higgs.
We are most grateful to Gary Barnes, Allan Bell, Markus Böhm, Michael Coren, Mike Dworetsky, Errol
Glaze, Greg Halac, Ilja Heckmann, John Horsman, Stuart Lowe, Henry Nilsson, Graham Relf, J. Sapranidis
and Mike Trace for spotting errors in the initial versions of the text and in the spreadsheets and their
functions.
We hope you have as much fun with these recipes and spreadsheets as we have had! Please let us know
when you find an error. You can contact us via the book’s website (see page 209).
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
About this book and how to use it
How many times have you said to yourself, ‘I wonder whether I can see Mercury this month?’ or ‘What
will be the phase of the Moon next Tuesday?’ or even ‘Will I be able to see the s olar eclipse in Boston?’
Perhaps you could turn to your local newspaper to find the information, or go down to your local library
to consult the Astronomical Almanac. You may even have an astronomical journal containing the required
information, or perhaps some computer software or a website that might do the trick. But you would
not, we suspect, think of sitting down and calculating it for yourself. Yet even though you may not find
mathematics particularly transparent, you can still do this for yourself. You can quite easily find the answer
to many astronomical questions using this book of calculation recipes. You use it just as you would a recipe
book in the kitchen f ollow the recipe and produce a delicious dish! All you need in addition is a calculator,
a piece of paper, a ruler and a pencil. (For those of us with access to a computer, we can use that instead of
the calculator and carry out all the calculations in a spreadsheet program as further described below.)
Your calculator does not have to be a very sophisticated device costing a great deal of money; on the
other hand it should be a little better than a basic four-function machine. At a minimum, it must have
buttons for the trigonometric functions sine, cosine and tangent. It should also be able to find square
roots and logarithms. Such calculators generally describe themselves as ‘scientific calculators’. Features
other than these are not essential but can make the calculations easier. For example, having a number of
separately-addressable memories in which you can store intermediate results would be useful. If you have a
programmable calculator, you can write programs to carry out many of the calculations automatically with
a s ubsequent saving of time and effort.
When choosing a calculator, don’t be led astray by arguments about whether ‘reverse Polish notation’
(RPN) or ‘algebraic notation’ (AN) is the better system. Each has its advantages and the same complexity
of calculation may be made using either. It is important, however, to read the instructions carefully and
to get to know your calculator thoroughly, whatever system it uses. Make sure that you like the ‘feel’ of
the keypad, and that pressing a key once results in just one digit appearing in the display. Look out for
special functions that can help you, like a key that gives you
π
(the constant 3.141 592 654), a key that
converts between times or angles expressed as hours or degrees, minutes, and seconds, and their decimal
equivalents, a key that takes any angle, positive or negative, and returns its equivalent value reduced to
the range 0
to 360
, and a key that converts between rectangular and polar coordinates (very useful for
removing the ambiguity of 180
on taking the inverse tangent of an angle).
When you go through the worked examples given with each calculation, do not be alarmed if your
figures do not match ours exactly. There are several reasons why they may not, including rounding errors
xiii
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
xiv About this book and how to use it
and misprints. You should try to work with at least seven or eight significant figures. If you write your
own programs to carry out any of the calculations on a computer, make sure that you use variables having
sufficient resolution. Use double precision (eight-byte precision) everywhere if possible.
Having gathered together your writing materials, calculator and book, how do you proceed? Let us take
as an example the problem of finding the time of sunrise. Turn to the index and look up ‘sunrise’; you are
directed to page 112 where you will find a paragraph or two of explanation and a list of instructions with
a worked example in the form of a table. We have kept things brief on purpose and have made no attempt
to provide mathematical derivations. We have also simplified the calculations. As you work through each
step, write down the step number and the result in a methodical fashion. Take care here and it will save you
a lot of time later!
Many calculations require you to turn back and forth between different sections. For example, step 1 of
‘sunrise’ directs you to another section to calculate the position of the Sun. Make the calculations in that
section, and then turn back to carry on with step 2. You will find it useful to keep several slips of paper
handy as bookmarks.
This book is not intended to match the precision of the results found in the Astronomical Almanac.Aswe
have already mentioned, the calculations have deliberately been simplified although they are good enough
for most purposes. If you have your own computer, you can use the methods to write programs displaying
the evolving Solar System with a precision that is better than the resolution of the computer screen. But
those of us with simple pocket calculators can find great satisfaction in simply being able to work out the
stars for ourselves and to predict astronomical events with almost magical precision.
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
A word about spreadsheets what are they?
In 1979, when the first edition of Practical Astronomy with your Calculator was published, very few people
had access to a computer. Although home computers were beginning to appear in the high street, they were
not the commonplace household accessory we see today. Calculations were made using a calculator, the
sophistication of which ranged from the simple four-function device to the versatile programmable reverse-
Polish s cientific machine. You may already own a calculator that would be suitable for the recipes given
here, but you might also own a computer and wish to make the calculations using that instead. If you are
good at programming, you could consider using the methods described in this book as a basis for writing
your own astronomical software. But most of us don’t want to embark on such a project. How then can we
use our computers to make astronomical calculations?
One answer is to use a spreadsheet program such as Microsoft’s Excel, or OpenOffice Calc. The latter
is available at no cost, and described as fully compatible with the former, so if you do not already own a
commercial spreadsheet program, then Calc might be a good way to go. Once you have loaded the software
on to your machine, open the spreadsheet program. The screen display should then look something like
Figure I. (Here and throughout the book, toolbars, sidebars and many other features have been removed
from the spreadsheet views.)
Figure I. An empty spreadsheet.
xv
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
xvi A word about spreadsheets what are they?
Figure II. Cell C5 carries the number 23.9, and cell D5 carries the label This is a number.
The spreadsheet consists of an array of cells, labelled A, B, C etc. across the top (these are the column
labels) and 1, 2, 3 etc. down the left-hand side (these are the row labels). Each individual cell is labelled by
its column letter and its row number, e.g. A1, B25 etc. The cell with the thick border around it in Figure I
is cell C5. You can write some text or numbers in any cell. In Figure II, the number 23.9 has been placed
in cell C5, and the label
This is a number has been placed in cell D5. (Since cell E5 is empty, the program
has allowed the label to overwrite the space allocated to E5, although the entire content
This is a number
remains in D5, and E5 remains empty.) The spreadsheet knows that something placed in a cell is a label
(i.e. text) if you begin the entry with a single apostrophe symbol (
'). If you want to enter a number as a
number, just type it in. If you want the spreadsheet to treat the number as a label, put the apostrophe in front
of it.
We can obviously put labels and numbers in any of the cells, but the real power of the spreadsheet comes
from using formulas. A formula is a calculation which can use the contents of other cells. The result of
the calculation is displayed in the cell carrying the formula, so you are not usually aware of the calculation
that has gone on in the background since what is displayed is the result rather than the formula itself. A
formula is placed in a cell by typing the equals sign (
=) followed by the formula. The spreadsheet knows
from the equals sign that it is to calculate the formula and display the result. For example, in Figure III, cell
C6 carries the entry
=C5*C5. You will see that C6 now displays the result of multiplying the number in cell
C5 by itself (the star symbol
* means ‘multiply’), i.e. the square of the number 23.9, which is the number
571.21. We have also placed the label
This is its square in cell D6.
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
A word about spreadsheets what are they? xvii
Figure III. Cell C6 carries the formula =C5*C5 and hence displays the square of 23.9.
Let’s see what happens if now we change the number in cell C5 without making any other change to the
spreadsheet. In Figure IV the number in C5 has been changed to the number 4.0 and, hey presto, the square
of 4 (i.e. 16) is displayed in cell C6. You can begin to see that complex calculations can be performed for
you automatically with a spreadsheet program. With the right formulas placed in order in the spreadsheet,
the results can be calculated for any set of starting values. That is just what we want to do in this book. We
can hide the complications of the calculation of, say, the time of sunrise within the formulas and just enter
a date and geographical location in the correct cells at the top to obtain the result immediately.
Figure IV. Cell C5 now carries the number 4 and so cell C6 displays the number 4 multiplied by 4 which is 16.
We don’t need to explain much more about spreadsheets here, although we will note various techniques
as we go along. If you want to learn more about their powerful capabilities we suggest buying a book about
spreadsheets (see the Bibliography on page 208 for a suggestion). In this book, we have supplied you with
the spreadsheet and formulas for most calculations, so all you have to do is to type in the labels, numbers
and formulas as shown. The spreadsheet will then do its work automatically and give you the answer for
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
xviii A word about spreadsheets what are they?
any starting values you enter. (We have provided the spreadsheets ready-made on our website. Please look
in the section A useful website” on page 209 for details.)
The layout of spreadsheets in this book
All of the spreadsheets in this book conform to the same general format (see Figure V). At the top, in cell
A1, is the title of the spreadsheet (in this case
Converting decimal hours to hours, minutes and seconds). It
is best to use a slightly larger font size for this and to make it boldface as here. We have used Arial 16 point
for the title. Row number 2 is left blank (i.e. none of the cells has anything in it). In row 3, we have written
the label Input in A3 (Times New Roman font, italic face, 10 point) to remind us that the input values for
the spreadsheet are entered to the right of this cell. In the case shown in Figure V, there is only one input
value, the decimal hours (name label in B3, Arial font, bold face, 14 point), and it is entered in cell C3 (also
Arial font, bold face, 14 point). In spreadsheets which have more than one input value, the others have their
name labels in cells B4, B5 etc. and their corresponding values in C4, C5 etc.
Input name
Input value
Step numbers
Variable names
Formula results
The formulas in the adjacent cells to the left
Spreadsheet title
Output names
Output values
Figure V. The layout of a spreadsheet.
The results of the calculations, i.e. the output values, are provided to the right of cell F3. We have written
the label Output in F3 (Times New Roman font, italic face, 10 point) to remind us that the output values
calculated by the spreadsheet appear to the right of this cell. In the case shown in Figure V there are three
output values, called
hours, minutes, seconds. Their name labels appear in cells G3, G4, G5 (Arial font,
bold face, 14 point) and their values in H3, H4, H5 (also Arial font, bold face, 14 point) respectively. Just
to the right of the three output values, in column I, are shown the formulas (written as labels, i.e. with an
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Calculations involving multiple sheets xix
apostrophe in front of the equals sign to stop the program calculating the formula) that are actually in the
output value cells. Thus cell H3 actually contains the formula
=C14 (i.e. it will display the value of the cell
C14) and you will need to enter
=C14 in the cell H3. Wherever you see a formula (anything beginning with
the equals sign) enter exactly that formula in the cell immediately to its left. In this case you would put
=C14 in cell H3, =C12 in cell H4, and =C10 in cell H5.
The calculations carried out by the spreadsheet begin on row 7 in Figure V. Each row corresponds to one
step in the calculation, in this case the calculation method of Section 8. In the method table shown in that
section there are just two steps, whereas in the spreadsheet there are eight. There is only a rough correspon-
dence between method steps and spreadsheet steps. This is partly because the spreadsheet calculations do
not have the benefit of human intelligence to assist them! For example, if you used your calculator to carry
out the s teps of Section 8, and you found that the result was, say, 6h 35m 60s, you would automatically
write this as 6h 36m 0s. The spreadsheet would, however, quite happily report the result in the first format.
We get over the problem in the spreadsheet by first stripping out the sign, then converting to seconds, then
finding the seconds, minutes and hours in that order, and finally putting back the sign.
In the example shown in Figure V, you would enter the labels and formulas exactly as shown. Thus
on row 7 you place the label
'1 in A7 (this is text, and the apostrophe tells the spreadsheet so), the label
'unsigned decimal in B7 and the formula in C7 shown immediately to its right, i.e. =ABS(C3). Do this for
each calculation row (7 to 14 in this case). Finally, rename the spreadsheet on the tab at the bottom (
DHHMS
in this case). (You can probably do this by pointing at it with the mouse, pressing the right-hand mouse
button, and selecting the ‘rename’ option.)
Although the labels in columns A and B make no difference to the calculations, we recommend that you
put them in as they make the spreadsheet much easier to understand. This becomes more important if you
return to a spreadsheet some time after you constructed it.
One other note about spreadsheets that you might find useful concerns column widths. If the column
width is too narrow to display the content of a cell, you may just see something like
######## displayed
instead. You can adjust the column width by placing the mouse pointer on the division between the label
(A, B, C etc.) of the column you want to alter and the label of the column immediately to its right, holding
down the left-hand mouse button, and ‘dragging’ the column width left or right as needed.
Calculations involving multiple sheets
Some of the spreadsheet calculations, as in the example just given, use just one sheet. Most, however, use
several. For example, suppose that a first spreadsheet calculation results in a number expressed in decimal
hours but the answer has to be in the form hours, minutes and seconds. The first sheet passes its answer
(in decimal hours) to a s econd sheet which carries out the conversion and passes the converted result back
again to the first sheet.
A concrete example is illustrated by a spreadsheet for Section 14, reproduced in Figure VI. You will
see that there are three tabs in the bottom left-hand corner, corresponding to three sheets labelled
GSTLST,
HMSDH and DHHMS. Only the top sheet, GSTLST is visible in the figure with the other two lying
‘underneath’ it. The input values to the calculation include the Greenwich sidereal time (GST) expressed in
hours, minutes and seconds (cells C3, C4 and C5). These must first be converted to the GST expressed in
decimal hours, a calculation covered in Section 7. The spreadsheet for that section, labelled
HMSDH,must
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
xx A word about spreadsheets what are they?
Figure VI. A spreadsheet with multiple sheets.
Figure VII. Illustrating cross-references between sheets.
be included in this spreadsheet file as an additional sheet the tab HMSDH in Figure VI. Figure VII shows
the spreadsheet with the
HMSDH sheet on top so it is visible.
The link between the sheets is accomplished by using the sheet name, followed by an exclamation mark
(
!) and the cell reference. In Figure VII, the input value of hours (C3) is obtained from cell C3 of sheet
GSTLST by using the formula =GSTLST!C3. Similarly, the input value of minutes is obtained using the
formula
=GSTLST!C4 in cell C4 of HMSDH, and the input value of seconds is obtained by using the formula
=GSTLST!C5 in cell C5. The result of the calculation by this sheet, the decimal hours, appears in cell H3
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
Using our own functions xxi
of Figure VII. This is passed back to sheet
GSTLST in cell C8 of Figure VI, which contains the formula
=HMSDH!H3.
Similarly, the result of the calculation of
GSTLST, expressed in decimal hours, appears in cell C11 of
Figure VI. This needs to be converted to the format hours, minutes and seconds and it is passed to sheet
DHHMS (see Figure VIII) by using the formula =GSTLST!C11 in cell C3 of that sheet. Sheet GSTLST then
extracts the results from sheet
DHHMS (H3, H4 and H5 of Figure VIII) using the formulas DHHMS!H3,
DHHMS!H4 and DHHMS!H5 respectively in cells C12, C13, and C14 of Figure VI.
Figure VIII. Illustrating cross-referencing between sheets.
Now you can proceed in this way if you wish, using multiple sheets to carry out specific calculations
as just described, but the result can be quite confusing when you have a complicated calculation requiring
many sheets. A better way to proceed is for us to define our own functions and use these instead to carry
out the calculations. This is the approach that we have adopted here.
Using our own functions
Microsoft Excel and OpenOffice Calc both come with an internal programming language called
BASIC.We
don’t need to go in to any of the details of what this is and how it works, but suffice it to say that we have
written functions to carry out most of the calculations described in this book. All you have to do is to use
the functions in your spreadsheet exactly as if they were formulas. This has the advantage that you now
need only one sheet for any calculation with no cross-linking to multiple sheets, making the whole thing
easier to comprehend. Another advantage is that we have provided functions with much higher accuracy
than the s implified calculations of many of the sections. For example, you can use the method of Section 46
to calculate the Sun’s ecliptic longitude approximately, or you can use the function
SunLong to calculate it
much more precisely.
Let us illustrate the use of functions instead of multiple sheets using the example above. Figure IX shows
the spreadsheet of Section 14 using functions instead of multiple sheets. Compare this with Figure VI. You
can see that in Figure IX there is now only one sheet, labelled
GSTLST.
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press
xxii A word about spreadsheets what are they?
Figure IX. Illustrating the use of functions instead of multiple sheets.
The results of the calculation, contained in cells H3, H4 and H5 in both Figures VI and IX, are identical,
but in place of the cross-references between sheets at C8, C12, C13 and C14 of Figure VI there are formulas
in the corresponding cells of Figure IX. In cell C8, for example, the formula
=HMSDH(C3,C4,C5) converts
the hours, minutes and seconds (in cells C3, C4 and C5) to decimal hours, with the result shown in cell C8.
The contents of C3, C4 and C5 are passed to the function
HMSDH as the references contained within the
brackets after the function. When the spreadsheet program sees a formula, in this case
=HMSDH(C3,C4,C5),
it first looks through a list of its own formulas, and then checks to see if the function has been written in
BASIC. If it has, the spreadsheet then runs the BASIC program corresponding to the function, passing the
contents of the cells in the reference list to the
BASIC program, in this case the contents of cells C3, C4 and
C5. The result of the calculation is then passed back to the spreadsheet where it appears in the same cell as
the function (C8).
Functions like this have been provided for most of the calculations in this book, and are described in the
corresponding sections. You will need to download the spreadsheets from the Cambridge University Press
website in order to obtain the functions (which are included invisibly with each sheet). Please look in the
section A useful website” on page 209 for details.
Cambridge University Press
978-0-521-14654-8 - Practical Astronomy with your Calculator or Spreadsheet: Fourth Edition
Peter Duffett-Smith and Jonathan Zwart
Frontmatter
More information
www.cambridge.org© in this web service Cambridge University Press