46,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
SPREADSHEET APPLICATIONS IN CHEMISTRY USING MICROSOFT® EXCEL®
Find step-by-step tutorials on scientific data processing in the latest versions of Microsoft® Excel®
The Second Edition of Spreadsheet Applications in Chemistry Using Microsoft® Excel® delivers a comprehensive and up-to-date exploration of the application of scientific data processing in Microsoft® Excel®. Written to incorporate the latest updates and changes found in Excel® 2021, as well as later versions, this practical textbook is tutorial-focused and offers simple, step-by-step instructions for scientific data processing tasks commonly used by undergraduate students.
Readers will also benefit from an online repository of experimental datasets that can be used to work through the tutorials to gain familiarity with data processing and visualization in Excel®.
This latest edition incorporates new and revised content to use to learn the basics of Excel® for scientific data processing and now includes statistical analysis and regression analysis using Excel® add-ins, accounts for differences in navigation and utility between Windows and MacOS versions of the software, and integrates with an online dataset repository for the tutorial exercises.
Spreadsheet Applications in Chemistry Using Microsoft® Excel® also includes:
- A thorough introduction to Microsoft® Excel® workbook and worksheet basics, including Excel® toolbar navigation, entering and manipulating formulas and functions and charting experimental chemical data
- Comprehensive explorations of statistical functions and regression analysis
- Generating calibration plots from instrumental data
- Visualizing concepts in physical chemistry
Perfect for undergraduate and graduate students of analytical and physical chemistry, Spreadsheet Applications in Chemistry Using Microsoft® Excel® is also an ideal resource for students and practitioners of physics, engineering, and biology.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 291
Veröffentlichungsjahr: 2022
Ähnliche
Table of Contents
Cover
Title Page
Copyright
Preface
Acknowledgements
Navigation of the Book
About the Companion Website
1 Introduction to Excel
1.1 Navigating the Workbook
1.2 Mathematical Operations on Cells
1.3 Charts
1.4 Summary
1.5 Further Exercises
2 Statistical Analysis of Experimental Data
2.1 Statistical Functions
2.2 Analysis ToolPak
2.3 Confidence Intervals and Limits
2.4 Hypothesis Testing
2.5 Summary
2.6 Further Exercises
References
Note
3 Regression Analysis
3.1 Linear Regression and Visualization
3.2 Polynomial Regression for Testing Goodness of Fit
3.3 Error Bars
3.4 Non‐Linear Regression
3.5 Summary
3.6 Further Exercises
References
4 Calibration in Excel
4.1 Errors and Confidence Limits in Calibration
4.2 Limit of Detection in Calibration
4.3 Random Errors and Confidence Limits
4.4 Method of Internal Standard Calibration
4.5 Method of Standard Addition Calibration
4.6 Comparison of Analytical Methods
4.7 Summary
4.8 Further Exercises
References
5 Visualizing Concepts in Physical Chemistry
5.1 Ion Activity and Concentration
5.2 Kinetics
5.3 Metal–Complex Equilibria
5.4 Titration Curves
5.5 Summary
References
6 Regression Analysis Using Solver
6.1 Using Solver
6.2 Summary
6.3 Further Exercises
References
Index
End User License Agreement
List of Tables
Chapter 2
Table 2.1 Upper and Lower Confidence Limits for
n
= 8.
Table 2.2 Upper and Lower Confidence Limits for
n
= 40.
Table 2.3 Tabulated Data for
for Comparing Thiol Levels in Blood of Norma...
Table 2.4 Tabulated Data for ANOVA: Single Factor for Investigating if Diff...
Table 2.5 Tabulated Data for ANOVA: Two Factor Without Replication for Inve...
Table 2.6 Film Thicknesses of a Polymer Material Deposited Using Spin‐Coati...
Table 2.7 Surface Areas Taken on CNT films, Both Pristine and Argon‐Plasma ...
Table 2.8 Concentrations of Pb in Four Drinking Water Samples as Measured b...
Table 2.9 Replicate Current Responses for Four Different Batches of Screen‐...
Table 2.10 % of Total Available Interstitial Water Recovered in Samples Tak...
Chapter 3
Table 3.1 Output of the LINEST Function as Applied to the Calibration Data ...
Table 3.2 Tabulated Measured Fluorescence Intensities for Fluorescein Over ...
Chapter 4
Table 4.1 Regression Analysis for the Pb Dataset Using the Analysis ToolPak...
Table 4.2 Template for Computing Unknown Concentration and the Associated S...
Table 4.3 Logarithmic Transformation of the Method Difference Data
Table 4.4 Tabulated Experimental Data for the Standard Addition Calibration...
Chapter 5
Table 5.1 Worksheet Setup for Entering Initial Reactant Concentration and R...
List of Illustrations
Chapter 1
Figure 1.1 Highlighted aspects of Excel worksheet for navigation.
Figure 1.2 Periodic table worksheet highlighting row and column formatting n...
Figure 1.3 Generating a scatterplot in an Excel worksheet.
Figure 1.4 Formatted chart showing the linear relationship between atomic ma...
Figure 1.5 Series dialogue box for inputting detail for generating data seri...
Figure 1.6 Conversion of cursor to black cross symbol for auto‐filling cells...
Figure 1.7 Entering a function in a specific cell worksheet.
Figure 1.8 Populated template tables for assigning elemental composition in ...
Figure 1.9 Chart design contextual tab showing chart style options.
Figure 1.10 Scatter chart style for plotting data.
Figure 1.11 Edit Series dialogue box.
Figure 1.12 Chart plotted with additional data series.
Figure 1.13 Tabulated data for computing stoichiometric quantities of produc...
Figure 1.14 Tabulated values for constants in the Nernst equation.
Chapter 2
Figure 2.1 Heavy tailed histogram showing the alumina content in a rock test...
Chapter 3
Figure 3.1 Peak area against caffeine concentration and corresponding linear...
Figure 3.2 Regression analysis of the caffeine dataset using the Analysis To...
Figure 3.3 Scatterplot showing response data against
x
‐index. Error bars rep...
Figure 3.4 Scatterplots showing membrane potential against time after inject...
Figure 3.5 Scatterplot showing membrane potential against time after injecti...
Chapter 4
Figure 4.1 Response data against Pb concentration and corresponding linear r...
Figure 4.2 Response data against Pb concentration for a limited concentratio...
Figure 4.3 Standard addition plot for calculating unknown
concentration in...
Figure 4.4 Linear regression analysis‐based comparison of two different meth...
Figure 4.5 Bland–Altman plot to compare two different methods (alpha and bet...
Figure 4.6 Log transformed regression plot compared to line of equality.
Figure 4.7 Bland–Altman (Log Transformed) plot.
Figure 4.8 Bland–Altman (Log Transformed) plot including CIs.
Figure 4.9 Methods ratio vs. average mercury concentration.
Chapter 5
Figure 5.1 Worksheet setup to calculate activity coefficients for electrolyt...
Figure 5.2 Populated worksheet for calculating activity coefficients for ele...
Figure 5.3 Plot of activity coefficient as a function of ionic strength for ...
Figure 5.4 Plot of concentration against time for reactant
A
.
Figure 5.5 First‐order decay plot for the first‐order kinetic data where
k
=...
Figure 5.6 Overlaid kinetic plots for a first‐order reaction with different ...
Figure 5.7 Overlaid kinetic rate data for first and second‐order reactions....
Figure 5.8 Overlaid kinetic plots for a second‐order reaction with different...
Figure 5.9 Overlaid linearized plots for the second‐order rate data for diff...
Figure 5.10 Arrhenius plots for second‐order decomposition data.
Figure 5.11 Visualization of the relationship between Cu‐NH
3
complex species...
Figure 5.12 Visualization of the relationship between Cu‐NH
3
complex species...
Figure 5.13 Worksheet setup for calculating titration curve data for a weak ...
Figure 5.14 Weak acid‐strong base titration curve.
Figure 5.15 Weak acid‐strong base titration curve and first derivative to pr...
Figure 5.16 Weak acid‐strong base titration curve and first and second deriv...
Figure 5.17 Gran's plot to determine equivalence point volume.
Figure 5.18 Isolated linear region of the Gran's plot.
Figure 5.19 Worksheet setup for calculating data for a polybasic acid titrat...
Figure 5.20 Polybasic acid‐strong base titration curve and first derivative ...
Figure 5.21 Fraction of each form of acid present during the course of a tit...
Chapter 6
Figure 6.1 Van Deemter plot of experimental data.
Figure 6.2 Worksheet parameters table setup.
Figure 6.3 Model formula entry to generate model data.
Figure 6.4 Worksheet setup for running Solver to model the van Deemter data....
Figure 6.5 Van Deemter plot of experimental data and model before optimizati...
Figure 6.6 Solver parameter dialogue box.
Figure 6.7 Van Deemter plot of experimental data and model after optimizatio...
Figure 6.8 Experimental chromatography data and model before optimization.
Figure 6.9 Chromatography data and fitted model after optimization.
Figure 6.10 Unresolved Gaussian peaks in a simulated dataset and model befor...
Figure 6.11 Unresolved Gaussian peaks in a simulated dataset and model after...
Figure 6.12 Unresolved Gaussian peak data overlaid with the corresponding pl...
Figure 6.13 Chromatographic peak data plotted as a scatterchart.
Figure 6.14 Chromatographic peak data plotted as a scatterchart overlaid wit...
Figure 6.15 Chromatographic peak data plotted as a scatterchart overlaid wit...
Figure 6.16 Chromatographic peak data plotted as a scatterchart overlaid wit...
Figure 6.17 Fluorescent decay behaviour for the free ruthenium compound over...
Figure 6.18 %Error over time between the free ruthenium experimental decay d...
Figure 6.19 Fluorescent decay behaviour for the protein‐bound ruthenium comp...
Figure 6.20 %Error over time between the protein bound ruthenium compound ex...
Figure 6.21 Fluorescent decay behaviour for the protein‐bound ruthenium comp...
Figure 6.22 %Error over time between the protein‐bound ruthenium compound ex...
Figure 6.23 Experimental UV–Vis scans taken of a chromium carbonyl complex o...
Figure 6.24 Absorbance values plotted over time taken at 380 nm during the p...
Figure 6.25 %Error between the experimental data and the model.
Figure 6.26 ISE dynamic response behaviour data overlaid with an optimized m...
Figure 6.27 Optimized model parameters for each flow rate for the valinomyci...
Figure 6.28 Tabulated cell potential data collected using a calcium‐selectiv...
Figure 6.29 Cell potential data plotted against log(
a
i
) measured using the C...
Figure 6.30 Cell potential data plotted against log(
a
i
) (measured using a Ca...
Figure 6.31 Lineweaver–Burk plot of the enzyme kinetic data.
Figure 6.32 Plot of reaction rate and substrate concentration overlaid with ...
Guide
Cover
Table of Contents
Title Page
Copyright
Preface
Acknowledgements
Navigation of the Book
About the Companion Website
Begin Reading
Index
End User License Agreement
Pages
iii
iv
vii
viii
ix
xi
xii
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
Spreadsheet Applications in Chemistry Using Microsoft® Excel®
Data Processing and Visualization
Second Edition
Aoife MorrinDublin City UniversityDublin, Republic of Ireland
Dermot DiamondDublin City UniversityDublin, Republic of Ireland
This edition first published 2022© 2022 John Wiley & Sons, Inc.
Edition HistoryFirst edition 1997, Wiley
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, except as permitted by law. Advice on how to obtain permission to reuse material from this title is available at http://www.wiley.com/go/permissions.
The right of Aoife Morrin and Dermot Diamond to be identified as the authors of this work has been asserted in accordance with law.
Registered OfficeJohn Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, USA
Editorial Office111 River Street, Hoboken, NJ 07030, USA
For details of our global editorial offices, customer services, and more information about Wiley products visit us at www.wiley.com.
Wiley also publishes its books in a variety of electronic formats and by print‐on‐demand. Some content that appears in standard print versions of this book may not be available in other formats.
Limit of Liability/Disclaimer of WarrantyIn view of ongoing research, equipment modifications, changes in governmental regulations, and the constant flow of information relating to the use of experimental reagents, equipment, and devices, the reader is urged to review and evaluate the information provided in the package insert or instructions for each chemical, piece of equipment, reagent, or device for, among other things, any changes in the instructions or indication of usage and for added warnings and precautions. While the publisher and authors have used their best efforts in preparing this work, they make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives, written sales materials or promotional statements for this work. The fact that an organization, website, or product is referred to in this work as a citation and/or potential source of further information does not mean that the publisher and authors endorse the information or services the organization, website, or product may provide or recommendations it may make. This work is sold with the understanding that the publisher is not engaged in rendering professional services. The advice and strategies contained herein may not be suitable for your situation. You should consult with a specialist where appropriate. Further, readers should be aware that websites listed in this work may have changed or disappeared between when this work was written and when it is read. Neither the publisher nor authors shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.
Library of Congress Cataloging‐in‐Publication Data Applied for
ISBN: 9781119182979
Cover design by WileyCover image: © oxygen/iStock/Getty Images
Preface
Since the publication of the first edition of this book almost 25 years ago, there have been monumental changes in how we interact with experimental data as scientists. We can now store it more securely, visualize it in new ways, share and collaborate on it, and more deeply interpret it, thanks to new and constantly improving data processing tools coming on stream. The spreadsheet today as a data processing tool is very accessible and can visualize calculations and help make theory and experimental data come to life so that it is meaningful to the student. This new edition of the book retains its guided tutorial approach for teaching undergraduate and postgraduate students a range of chemistry topics that incorporate aspects of data analysis and also provide for visualizations of fundamental concepts.
In this edition, we have included additional datasets along with guided tutorials for the student to work through independently. The datasets and guided tutorials are designed around Excel but, if Excel is not available, the exercises can also be navigated in other spreadsheet programmes, e.g. Google Spreadsheets or LibreOffice Calc. Similar functionalities are available across all these programmes.
We have expanded the content on some important topics such as statistical treatment of data and calibration in analytical chemistry, for example, that were not included in the previous edition. The book brings the student from the basics of navigating a spreadsheet for simple data processing operations in a step‐by‐step manner, to advanced data processing and analysis for small and medium‐sized datasets. The chapters are intended to give students practical experience in performing spreadsheet calculations and visualizing experimental results. There is an emphasis on letting the learner gain enough familiarity and experience to enable them use spreadsheets independently, and in other scientific contexts, while at the same time encouraging the student to examine data objectively and critically interpret it as an experimental scientist. This book provides an experiential ‘learn by doing’ approach to gain conceptual insights as well as practical expertise in data analysis in chemistry topics.
Acknowledgements
This book is the culmination of many combined years of teaching and research experience for us both. We would like to thank our families who travelled the journal of this book writing venture, from the initial blank page and the pontificating, to the reading and suggestions to improve the offering, to the proofing of the final manuscript. It was a long road, but would have been infinitely longer without you. In particular, Aoife would like to thank David for his infinite support during the burning of midnight oil to get the chapters inked, and Ciara for putting time into shaping and structuring the content with her vision; and Dermot would like to thank Tara for her never‐ending patience with him.
We have also leaned on our colleagues in Dublin City University who have kindly supplied us with experimental datasets to use in one or both editions of this book: Francisco Saez, Robert Forster, Brendan O'Connor, Ciarán Fagan, Conor Long, Han Vos, Tia Keyes, Blánaid White, and Fiona Regan. The book would not be as rich without your input. Thank you.
Navigation of the Book
There are six concise chapters in this book that take the learner from basic spreadsheet navigation to complex data analysis approaches reasonably quickly. The book is designed to start at the basics so that no prior experience of spreadsheets or Excel is required. The book is not intended to be passively read, but instead to be actively used while sitting in front of a computer. Each chapter sets out its own learning objectives and is divided into sub‐sections that are focussed on covering basic theory needed to understand the scientific concepts and datasets in the accompanying guided tutorials. Additional questions are given at the end of each chapter to push the student in applying spreadsheet procedures in other scientific contexts.
About the Companion Website
This book is accompanied by a companion website.
www.wiley.com/go/morrin/spreadsheetchemistry2
This website includes:
Students' resources
Worksheets
Further Exercises
Teachers' resources
Worksheets
Further Exercises
1Introduction to Excel
In this chapter, students will learn to:
Undertake basic operations in an Excel worksheet
Perform mathematical calculations on worksheet data using formulas and functions
Understand and apply relative and absolute cell referencing
Visualize and interpret data sets in the form of charts
Excel is a Microsoft spreadsheet application widely used to store, organize, process and analyze many forms of data, including experimental data. It offers great flexibility and is, in many respects, unrivalled in terms of its functions as applicable to scientific experimental data. Researchers use spreadsheet applications such as Excel to work with experimental data. For example, they will transfer data to a spreadsheet such as Excel to:
Store and organize experimental data
Manipulate data using mathematical functions
Visualize data, for example, through charts and tables
Perform statistical analysis of data
Apply curve fitting with linear and non‐linear regression
Besides Excel, other examples of spreadsheet applications exist including free, open source software packages such as LibreOffice Calc and Google Spreadsheets. They operate in a similar manner to Excel in general, but differ in some features and hence functionality. Microsoft® Excel® has the most features and is currently more widely used than these open source alternatives. That said, the landscape is rapidly changing and these open source software packages are increasing in maturity and popularity. If you have access to Excel, it is the spreadsheet software program of choice. As such, the tutorials in this book are designed specifically around Excel. However, if Excel is not accessible, open source alternatives are a good option to work through the tutorials to learn approaches to processing experimental data.
This chapter introduces basic standard worksheet operations in Excel that will be needed for the later chapters. The tutorial exercises have been designed around Excel for PC. If you are using Excel for Mac, you can expect minor deviations from the tutorial instructions, as formats and styles, and locations of commands and options can differ between the two versions. Likewise, accessing tools and commands may differ if you are using an early version of Excel. However, most functionality is equivalent between versions and so all tutorials here can be undertaken using any version of Excel. Of course, it is advisable to upgrade Office if you are using a particularly archaic version. Once you are up and running with Excel, it is worth spending time working through the tutorials in this chapter to ensure that the more basic spreadsheet functions of Excel are understood before moving to the more advanced topics and tutorials in later chapters.
1.1 Navigating the Workbook
1.1.1 The Worksheet
Launching Excel brings you into a workbook containing a set of spreadsheets. Excel refers to each spreadsheet within a workbook as a ‘worksheet’. Some basic aspects of the worksheet are labelled in Figure 1.1.
Figure 1.1 Highlighted aspects of Excel worksheet for navigation.
The Ribbon menu gives access to all tools and commands. Within the Ribbon tab, you can see several tabs – Home, Insert, Page Layout, Formula, Data, Review, and View. Each of these has their own Ribbon display, which comprises groups of buttons representing a variety of commands that are displayed when each tab is selected.
Contextual tabs are special types of tabs that appear only when an object is selected, such as a chart or a shape. These contextual tabs contain commands specific to whatever object you are currently working on. For example, after you add a shape to a worksheet, a new Format tab appears as a Contextual tab. These tabs only activate when you work with particular objects. You will use these tabs regularly in the tutorials in this book.
The Formula bar is the toolbar at the top of the worksheet window that can be used to enter or copy an existing formula into cells. It is labelled with the function symbol fx. By clicking the Formula bar, or when you type the equal (=) symbol in a cell, the Formula bar will activate.
1.1.2 Worksheet Tools
You can navigate the Excel worksheet fairly intuitively using standard Office365 operations. The first tutorial here will use an already populated worksheet to show you some of the tools available.
Tutorial 1.1 Using Basic Formatting and Analysis Commands
In this tutorial, you will work with a data set relating to the Periodic Table to learn some basic formatting and analysis commands in Excel.
Open the workbook
1.1_Periodic Table.xls
.
In the worksheet, you will see columns of data related to the periodic table. Expand the width of the columns so that all text in each of the columns can be seen. To do this, bring the mouse cursor to where the row and column headers meet – see
Figure 1.2
. By clicking here you will select the whole worksheet. Then double‐click any one of the column partition lines. This will readjust all column widths so that you can visualize the data clearly.
Figure 1.2 Periodic table worksheet highlighting row and column formatting navigation.
Now take a look at column D –
Atomic Mass
. The values in the cells have 7 decimal places reported which is unnecessary for our purposes. To reduce the number of significant figures, first highlight the data by clicking at the top of column D. Right click and select
Format Cells
. In the pop‐up dialogue box, select
Number
and enter
3
in the
Decimal Places
box. Press
OK
.
Next, format the columns of data into a table so that you can sort the data. Highlight columns A to I and under the
Home
tab, click
Format as Table
. Choose a style you like in the dialogue box that pops up. Ensure
Header Row
is ticked in the
Table Style Options
under the contextual
Design
tab.
Next, sort the data in increasing order of atomic radius. Select the greyed icon in G1 to the right of text
Atomic Radius
. Click on
Smallest to Largest
and exit out of the box.
○ Also try sorting the data indifferent ways according to the different properties listed.
You can visualize the data by creating charts to represent the data. Try graphing
Atomic Number
against
Atomic Mass
. To do this, highlight columns C and D. Click the
Insert
tab and then click
Scatter
chart type as shown in
Figure 1.3
. This type of chart is very common when working with experimental data.
Figure 1.3 Generating a scatterplot in an Excel worksheet.
To format the chart, select the chart and double click into each
axis title
and
chart title
to edit the text.
Click on the
x
‐axis, and right click and select the
Format Axis
option. Select
Tick Marks
and in the
Major Type
box, and select
Inside
to add tick marks to the
x
‐axis. Repeat this for the
y
‐axis.
Gridlines
are the light grey horizontal and perpendicular lines that divide the chart area into squares to form a grid. To delete these, click on one of the horizontal gridlines, and then right click and select
Delete
. Repeat this step for the vertical gridlines.
Click through the previews in the
chart styles
to change the layout or style to one you like. Depending on your chosen style, your chart might look something like in
Figure 1.4
.
Figure 1.4 Formatted chart showing the linear relationship between atomic mass and atomic number.
Using the same approach, create charts to visualize the dependence of electronegativity and atomic radius on atomic number. Decide yourself on the chart type and format and design that you use.
Save and close the
1.1_Periodic Table.xls
workbook.
1.2 Mathematical Operations on Cells
1.2.1 Formulas and Functions
Once data is entered into a worksheet, operations can be performed to process the data. Excel performs mathematical operations using formulas and functions. Formulas can be written into the formula bar and always begin with an equals sign (=).
These formulas and functions act on specified cells in a worksheet, where variables can be defined in other cells that are referenced. There are two types of cell references used by Excel: relative and absolute. Relative and absolute behave differently when copied and filled from other cells. Using a letter‐number combination, e.g. A2, to describe a cell is known as relative referencing. By default, all cell references are relative references. These references change based on position relative to the original cell when the formula is copied and pasted into another cell. The effect is to keep the relative addresses between cells referenced in a formula, in effect making these variables.
In contrast, absolute referencing uses the format $letter$number, e.g. $A$2, and remains constant when copied and filled from other cells. If the absolute reference $A$2 had been used as the address, then this address is maintained in the formula across all cells, effectively rendering it a constant (the value of the number in cell A2).
The following tutorials have examples of using both relative and absolute referencing.
Tutorial 1.2 Entering a Simple Formula into a Worksheet
In this tutorial, you will generate model temperature data and convert it from Celsius to both Fahrenheit and Kelvin using relative referencing.
Open a new workbook and name as
1.2_Temperature.xls
.
To set up the worksheet, enter the titles
Celsius
,
Fahrenheit
, and
Kelvin
in A1, B1, and C1, respectively.
Adjust column widths A‐C so all titles are visible.
Bold the titles by highlighting and click on the
Bold
icon under the
Home
tab.
Enter the centigrade temperature range from 0 to +100 in increments of 5 into column A according to the
Fill
→
Series…
technique as described in the sub‐bullets here:
○ Enter
0
into A2.
○ On the
Home
tab, in the
Editing group
, click
Fill→Series...
to open up the Series dialogue box (
Figure 1.5
).
○ Select
Series in
as Columns and
Type
as Linear.
○ Use
5
as
Step value
and
100
as
Stop value
.
○ Press OK.
Figure 1.5 Series dialogue box for inputting detail for generating data series.
In B2, enter the conversion formula from Celsius to Fahrenheit, starting with an equals sign, and using A2 as the centigrade variable
= (A2*9/5)+32
.
Fill all corresponding values for data in column A into column B by hovering the cursor over the small square on the bottom right corner of B2 (known as the fill handle) until it becomes a black cross and double click on your mouse (
Figure 1.6
).
Figure 1.6 Conversion of cursor to black cross symbol for auto‐filling cells.
In C2, enter the conversion formula from Celsius to Kelvin, again starting with an equals sign, and using A2 as the centigrade variable
= A2+273.15
.
Fill down the column as before to report all Kelvin values.
Save and close the
1.2_Temperature.xls
workbook.
It is important to note that all Excel formulas follow the same rules of algebra, regarding the order of operations. If there is more than one set of brackets or parentheses, the inner‐most set will be computed first. Exponent operations will then be calculated. Multiplication and division calculations will be performed next. Finally Excel will then complete any addition and subtraction in the formula.
It can be a good practise to use brackets whenever you can in Excel formulas to structure the equation, even if the use of brackets is superfluous. The use of brackets can help you not only avoid calculation errors but also better understand the formula you are applying.
1.2.2 Entering Functions
Functions in Excel are accessed through the Formulas tab under Insert Function. A comprehensive range of mathematical, statistical, and scientific functions are available. These all have the general syntax:
= FunctionName(arguments)
For example, = SIN(number) calculates the sine of a number (where the number is an angle in radians) and = SUM(number 1, number 2,…) calculates the summation of the numbers in the cells defined by the argument. Functions can be entered into cells using the Insert Function button, or by typing the function directly into the Formula Bar or cell. Here, we will use the Insert Function dialogue box to write some formulas.
Tutorial 1.3 Entering a Function into a Worksheet
In this tutorial, you will use the functions AVERAGE and STDEV to describe a set of replicate experimental data.
Open the workbook
1.3_Sensor Repeatability.xls
. You will see a set of data relating to the anodic current responses of a platinum electrode to 10 repeated measurements of a standard solution of hydrogen peroxide (5 mM).
Calculate the mean and standard deviation of the data using the AVERAGE and STDEV functions:
○ Click on B13 and then click
Insert Function
under the
Formulas
tab.
○ In the search box that pops up, search for the function
AVERAGE
, highlight it, and double click.
○ With the cursor in the box
Number1
, select B2:B11. The result will be returned in the dialogue box. Look to the bottom of the dialogue box pop‐up for specific information on the selected function and arguments (
Figure 1.7
).
○ Press
OK
to enter the calculated value into B13.
○ To calculate the standard deviation of the set of data, select cell B14 and following the same steps as above but this time search for and select the STDEV function. You should return a value of 0.33236205. In order to reduce down the number of decimal places used, select the cell and right click. From the drop‐down menu, select
Format Cells
. Then select the
Category:
Number. Enter the number of decimal places you require (three in this case) and press
OK
to give just three significant figures in B14.
○ Enter the
relative standard deviation
(
RSD
) in B15 by typing the formula
= B14/B13*100
. Reduce down the number of decimal places to 1 according to the instructions given above.
Save and close the
1.3_Sensor Repeatability.xls
workbook.
Note: As you become more accustomed to particular formulas, you can enter them directly, without the need for the Insert Function dialogue box. For example, the AVERAGE function can also be entered directly into a cell by typing = AVERAGE(B2:B11) in B13 and pressing Enter.
Functions in Excel can also be nested, which means placing one function within another. Generally, this is more of a requirement in logic decisions than mathematical calculations. However, they do have their use in scientific data processing. For instance, calculating a formula based on a function inside another function, e.g.
= STDEV(AVERAGE(A1:A5),(AVERAGE(B1:B5),(AVERAGE(C1:C5))
calculates the standard deviation of the set of average values taken from the 3 columns of data.
The following tutorials introduce more functions as well as nesting calculations within functions.
Figure 1.7 Entering a function in a specific cell worksheet.
Tutorial 1.4 Using Nested Functions
In this tutorial, you will transform angle data from degrees to radians units in order to compute the sine function for the angles.
Open a new workbook and save the file as
1.4_Degrees.xls
.
In the first worksheet, in cell A1, enter the title
Degrees
and below this enter the
x
range (0–360°) in column A, incrementing every 10°.
As the sine function,
y
= sin(
x
), stipulates that
x
must be in radians, the data must be converted to radian units. Enter the title
Radians
in B1. Convert the degree values in column A to radian values in column B using the RADIANS() function available via
Insert Function
. Once the formula is entered in B2, fill down in column B to convert the entire data set.
In C1, enter the title
Sin(x)
and calculate sin(
x
) in C2 with the
Insert Function
dialogue box, using B2 as the value for
x
in radians. Fill this formula down column C over the full data range using the default relative referencing.
The alternative here is that these arguments can be nested to merge the steps of calculating radians and sine function together. To demonstrate this, in D1, enter the title
Sin(x)_Nested
. In D2 select the SIN function using
Insert Function
and type the function
RADIANS(A2)
into the arguments box. Alternatively, you can directly type
= SIN(RADIANS(A2))
into the cell. Exit out of the dialogue box and fill down the column. By nesting the arguments in this way, the worksheet needs only contain two columns of data.
Save and close the
1.4_Degrees.xls
workbook.
Using nested arguments is a matter of preference in Excel. Performing calculations in a stepwise fashion in columns, rather than combining several transformations in a single step, can have advantages when it comes to troubleshooting calculations.
Tutorial 1.5 Entering Functions for Template Design
In this tutorial, you will get more practise entering formulas using relative and absolute referencing by designing a template for assigning elemental composition in organic compounds.
Open the workbook
1.5_Organic Compounds.xls
where you will see a template setup with three tables. The chemical formulas for methanol, ethanol, and acetic acid are entered in the first table. Populate the rest of this table by entering the number of carbons in each of the corresponding compounds in column C, the number of hydrogens in each of the corresponding compounds in column D, and the number of oxygens in each of the corresponding compounds in column D.
