Excel 2013 for Scientists E-Book

Dr. Gerard Verschuuren

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 315

Veröffentlichungsjahr: 2024

Beschreibung

This book provides a detailed guide for scientists to fully utilize Excel 2013 for data analysis, visualization, and statistical modeling. It begins with core spreadsheet techniques like range names, nested functions, and cell referencing, creating a strong foundation for advanced skills. Tailored examples help readers understand how to apply these basics in scientific contexts.
The book progresses into advanced data analysis tools, covering pivot tables, lookups, conditional formatting, and filtering techniques. Regression methods, curve fitting, and distribution simulations are explored, allowing readers to analyze trends, predict outcomes, and validate data. Statistical methods such as ANOVA, significance testing, and sampling techniques are presented with practical examples to reinforce learning.
Later chapters focus on advanced graphing techniques, customizing charts, and working with complex functions like arrays and nonlinear regression. Exercises and step-by-step instructions ensure concepts are clear and practical. By the end, readers will confidently apply Excel tools to streamline experiments, enhance productivity, and achieve scientific precision.

Leseprobe

Excel 2013 for Scientists

by

Dr. Gerard M. Verschuuren

Holy Macro! Books

PO Box 82, Uniontown, OH 44685 USA

Excel 2013 for Scientists

© 2014 Dr. Gerard M. Verschuuren

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Author: Dr. Gerard M. Verschuuren

Layout: Tyler Nash

Cover Design: Shannon Mattiza 6’4 Productions

Indexing: Nellie J. Liwam

Published by: Holy Macro! Books, PO Box 82, Uniontown, OH 44685 USA

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: December 2013. Printed in USA

ISBN: 978-1-61547-025-9 Print, 978-1-61547-217-8 PDF, 978-1-61547-338-0 ePub, 978-1-61547-117-1 Mobi

LCCN: 2013938521

About the Author

Dr. Gerard M. Verschuuren is a Microsoft Certified Professional specialized in VB, VBA, VBScript, VB.NET, and C#-NET. He has more than 25 years of experience in teaching at colleges and corporations.

He holds master’s degrees in biology (human genetics) and philosophy, as well as a doctorate in the philosophy of science from universities in Europe.

He is the author of From VBA to VSTO (2006, Holy Macro! Books), and the author of Excel Simulations (2013, Holy Macro! Books).

He is also the author behind the Visual Learning series (www.mrexcel.com/microsoft-office-visual-learning.html), which includes:

• Your Access to the World (2004)

• Access 2007 VBA (2008)

• Visual C# Express DVD (2008)

• Excel 2007 Expert (2007)

• Excel VBA 2007 (2008)

Introduction

This book can be used on its own or in conjunction with an interactive CD called Excel 2013 for Scientists, also available from MrExcel (www.mrexcel.com/2013books/scientist2013cd.html). This book assumes at least some basic knowledge of Excel. Readers new to Excel may want to familiarize themselves with a basic how-to source such as the interactive CD Excel 2007 Expert, also available from MrExcel (www.MrExcel.com/excel2007expert.shtml).

Scientists do not want nor do they need verbose explanations. Therefore, I was as concise as possible in the chapters of this book. I also attempted to add some meaningful simple exercises because the proof is still in the pudding. The examples appear at the end of each part, along with their solutions. Because I am a human geneticist myself, most of my simple examples stem from the life sciences.

All files used in this book can be found at www.genesispc.com/Science2013.htm. Each file has an original version (to work in) and a finished version (to check your solutions).

Excel was originally created as a financial application, but it has evolved into a rather sophisticated scientific tool. Although other and perhaps more advanced programs exist, many of those have a steep learning curve. Excel may, therefore, still be your best choice. I hope you will soon discover why.

I also teach the content of this book to scientists. It is a 4-day hands-on course of six hours a day at your own location. To schedule such a class go here: www.genesispc.com/schedule.htm.

Part 2: Data Analysis

Chapter 6: Auto-numbering

To make record keeping easier, it may be wise to implement a good numbering system for each row or record in your data set on your spreadsheet. You need to know about some of Excel’s dedicated tools and functions before you take on your records’ numbering system.

Figure 2.1provides an overview of Excel’s rounding functions. All numbers in the top row have been rounded with various functions—some always round down; others round toward zero, depending on whether they are in the positive or negative range; and so on.

Figure 2.1

This chapter focuses on four of the rounding functions:

• INT: This function returns the integer part of a number but rounds down; for example, INT(7/2) returns 3, but INT(-7/2) returns -4.

• TRUNC: This function returns the integer part of a number but rounds toward zero; for example, TRUNC(7/2) returns 3, and TRUNC(-7/2) returns -3.

• QUOTIENT: This function returns the integer part of a number after division; for example, QUOTIENT(7,2) returns 3.

• MOD: This function returns the remainder of a division; for example, MOD(7,2) returns 1

• There is one more function you should know about in this context—the ROW function:

◦ =ROW() returns the number of the row the formula is located in.

◦ =ROW(A1) returns the row number of cell A1, which is 1. When you copy the formula downward, the reference to A1 automatically updates to A2, and so on.

Figure 2.2 shows you some fancy automatic numbering systems:

Figure 2.2

• A1: =ROW()

• C1:=RIGHT(“000” & ROW(),3). The ampersand (&) is a string connector to hook things together. The function RIGHT takes the last three digits in this case.

• E1:=ROW(A1000). The ROW function’s argument allows you to start at a higher number, and it adjusts to copying.

• G1:=MOD(ROW()-1,5)+1. After each fifth row, the number starts all over again.

• I1:=QUOTIENT(ROW()-1,5)+1. The number repeats itself five times.

Here are three important steps to use in implementing an auto-numbering system:

• You can select the entire numbering range at once. To do so, you click in the start cell (for example, A1), then in the Name box, type the address of the end cell (for example, A1000), and press Shift+Enter to select the entire range in between.

• Now you can just start your function or formula, which automatically ends up in the start cell; then you press Ctrl+Enter to place that formula in all the selected cells

• Finally, you can change the formulas into real numbers by copying the entire section with Copy, then selecting Paste Special, and choosing Values Only.

Figure 2.3 shows a different kind of auto-numbering system: It places a series of values (A1:E20) into a number of bins (in column I). In cell G2, you can determine how many bins you would like (from 5 to 30). Note the following about Figure 2.3:

Figure 2.3

• The range of the series of values has been named data.

• Cell G2 allows you to change the number of bins with a drop-down box: Data | Data Validation | Allow: List | Source: =$G$4:$G$9.

• When you change the number of bins, the frequency bins adjust automatically, thanks to a formula in the cells I1:I30 that uses the functions INT, ROW, MIN, and MAX: =INT(MIN(data)+(ROW(A1)*(MAX(data)-MIN(data))/$G$2)). This is not the perfect formula yet, but good enough for now.

• In column J, we list the frequency of each bin. To do so, select the entire range J1:J30 at once, insert the formula =FREQUENCY(data,I1:I30), but accept it with Ctrl+Shift+Enter (this is required because we are dealing with an array function; more on this later).

• Range I1:J30 changes its colored range according to cell G2. To do so select the entire range: Home | Conditional Formatting | New Rule | Use a Formula | =ROW()<=$G$2 | Select a format.

Chapter 7: Subtotals

A common step in data analysis is to create subtotals for specific subsets of records. Subtotals can be sums, means, standard deviations, and similar summary calculations. This step can become a tremendous task in large spreadsheets if you’re not familiar with the right tools to do so quickly and efficiently. As you’ll learn in this chapter, you have to follow a few basic rules.