Physics with Excel and Python: Using the Same Data Structure Volume I: Basics, Exercises and Tasks

Preface
Contents
About the Author
1 Introduction
	1.1 A Two-Track Didactical Approach
	1.2 What Can You Expect?
	1.3 What Do You Need?
	1.4 Tim, Alac, and Mag
	1.5 Didactic Concept
	1.6 Subject Matter
	1.7 Getting Started with Excel
		1.7.1 Start Menu
		1.7.2 Spreadsheet Presentation
	1.8 Getting Started with Python
	1.9 Skills to Be Trained
2 Data Structures, Excel and Python Basics
	2.1 Introduction: Named Ranges in Excel, Arrays in Numpy
	2.2 Characteristics of a Parabola
		2.2.1 Different Definitions of a Parabola
		2.2.2 Data Structure and Nomenclature
	2.3 Basic Exercise in Spreadsheet Calculation
		2.3.1 Cell Addressing
		2.3.2 Graphical Representation of a Function
		2.3.3 Smart Legends in Figures
		2.3.4 Scroll Bars
		2.3.5 Summary: Cell References and Name Manager
		2.3.6 What Have We Learned so Far, and How to Proceed Further?
		2.3.7 Python Program
	2.4 Python and NumPy Basics
		2.4.1 Basic Exercise
		2.4.2 Data Structures
		2.4.3 Python Libraries
		2.4.4 Numpy Constructions
		2.4.5 Standard Plot Program
		2.4.6 Formatted Output
	2.5 Matrix Calculations in Excel and Python
		2.5.1 Data Structure and Nomenclature
		2.5.2 Operations on Arrays
		2.5.3 Matrices in Spreadsheets
		2.5.4 Matrices in Python
	2.6 Four Parabolas and Their Upper Envelope
		2.6.1 Graphical Representation
		2.6.2 Data Structure and Nomenclature
		2.6.3 Spreadsheet Calculation
		2.6.4 Python Program
		2.6.5 Extrema Along Different Axes
	2.7 Sum of Four Cosine Functions
		2.7.1 Sound and a Cosine Identity
		2.7.2 Data Structure and Nomenclature
		2.7.3 Spreadsheet Layout
		2.7.4 Python Program
		2.7.5 Producing Labels (as Strings) in Excel and Python
	2.8 Questions
3 Formula Networks and Linked Diagrams
	3.1 Introduction: Well-Structured Sheets and Programs
	3.2 Image Construction for Focusing and Diverging Lenses
		3.2.1 Straight Line Equation
		3.2.2 Geometrical Image Construction for a Thin Focusing Lens
		3.2.3 Imaging Equation with Correct Signs
		3.2.4 Beam Through a Converging Lens that Really Contributes to the Image
		3.2.5 Data Structure and Nomenclature
		3.2.6 Spreadsheet Calculation
		3.2.7 Python Program
	3.3 Doppler Effect
		3.3.1 A Formula for All Cases
		3.3.2 A Sound Source Passes a Remote Receiver
		3.3.3 Data Structure and Nomenclature
		3.3.4 Spreadsheet Calculation “Remote Receiver”
		3.3.5 Python Program “Remote Receiver”
	3.4 Exponentials
		3.4.1 Explosive Character of Exponentials
		3.4.2 General Exponential Function
		3.4.3 Representation in a Diagram
		3.4.4 Diode Characteristics I(U)
		3.4.5 Data Structure and Nomenclature
		3.4.6 Spreadsheet Calculation
		3.4.7 Python Program
	3.5 Questions
4 Macros with Visual Basic and Their Correspondences in Python
	4.1 Introduction: For, If, Sub/Def
	4.2 Basic Exercise: For-Loops
		4.2.1 Visual-Basic-Editor 1: Editing
		4.2.2 Programming
	4.3 Macro-Controlled Drawings with For, Sub, If
		4.3.1 Macro Recorder
		4.3.2 Visual-Basic Editor 2: Macro Recording, Debugging
		4.3.3 Programming Elements
	4.4 A Checkerboard Pattern (Excel)
		4.4.1 Checkerboard, Same-Colored and Multi-colored
		4.4.2 Global Variables
	4.5 A Checkerboard Pattern (Python)
		4.5.1 Turtle
		4.5.2 Differences to Visual Basic
		4.5.3 Checkerboard with Squares, Triangles, and Circles
	4.6 Drawing Densely-Packed Atomic Layers; Crystal Physics
		4.6.1 Program Structure and Geometry
		4.6.2 Data Structure and Nomenclature
		4.6.3 Excel
		4.6.4 Python
	4.7 Text Processing
		4.7.1 Cutting and Joining Strings
		4.7.2 Data Structure and Program Flow
		4.7.3 Excel
		4.7.4 Programming Step by Step
		4.7.5 VBA Constructs
		4.7.6 Python
	4.8 Processing the Protocol of a Measuring Device
		4.8.1 Protocol of a Measuring Device
		4.8.2 Detection of Code Words
		4.8.3 Data Structure and Nomenclature
		4.8.4 Excel
		4.8.5 Python
	4.9 User-Defined Functions
		4.9.1 User-Defined Functions as Add-In
		4.9.2 Scalar Product and Vector Product
		4.9.3 Python
	4.10 Questions and Tasks
5 Basic Mathematical Techniques
	5.1 Introduction: Calculus, Vectors, and Linear Algebra
	5.2 Straight-Line Segment Under a Magnifying Glass
		5.2.1 Under a Magnifying Glass
		5.2.2 Data Structure and Nomenclature
		5.2.3 Spreadsheet Calculation
		5.2.4 Plotting Vectors with Python Matplotlib
	5.3 Differentiation
		5.3.1 First and Second Derivative
		5.3.2 Data Structure and Nomenclature
		5.3.3 Spreadsheet Layout
		5.3.4 Python Program
	5.4 Integration
		5.4.1 Area Under a Curve
		5.4.2 Length of a Curve
		5.4.3 Data Structure and Nomenclature for the Arrays in the Integration
		5.4.4 Python Program
		5.4.5 Spreadsheet Solution
	5.5 Vectors in the Plane
		5.5.1 Vectors
		5.5.2 Data Structure and Nomenclature
		5.5.3 Spreadsheet Layout
		5.5.4 Python Program
	5.6 Tangents to and Perpendiculars on a Curve
		5.6.1 At/On a Polynomial and an Ellipse
		5.6.2 Data Structure and Nomenclature
		5.6.3 Python Program
		5.6.4 Spreadsheet Solution
	5.7 Banked Curve
		5.7.1 Cross-Section of the Road
		5.7.2 Data Structure and Nomenclature
		5.7.3 Python Program
		5.7.4 Spreadsheet Solution
	5.8 Weighted Average
		5.8.1 A Mobile with Two Arms
		5.8.2 Data Structure and Nomenclature
		5.8.3 Python Program
		5.8.4 Spreadsheet Calculation
	5.9 Systems of Linear Equations
		5.9.1 Polynomial and Electrical Network
		5.9.2 Data Structure and Nomenclature
		5.9.3 Spreadsheet Solutions
		5.9.4 Python Programs
	5.10 Some Mathematical Functions
	5.11 Questions and Tasks
6 Superposition of Movements
	6.1 Introduction: Translations and Rotations
	6.2 Projectile Trajectory with Velocity Vectors (T-T)
		6.2.1 Projectile Trajectory and Velocity Vectors
		6.2.2 Data Structure and Nomenclature
		6.2.3 Spreadsheet
		6.2.4 Python
		6.2.5 Animation of Figures with FuncAnimation
	6.3 Cycloid, Rolling Curve (R-T)
		6.3.1 Trace of a Writing Point Fixed at a Rolling Wheel
		6.3.2 Data Structure and Nomenclature
		6.3.3 Excel
		6.3.4 Python
	6.4 Foucault’s Pendulum (T-R)
		6.4.1 A Lecture Experiment
		6.4.2 Data Structure and Nomenclature
		6.4.3 Excel
		6.4.4 Python
	6.5 Anchor, Deflected Out of Its Rest Position (R-R)
		6.5.1 Deflected Anchor
		6.5.2 Data Structure and Nomenclature
		6.5.3 Excel
		6.5.4 Python
	6.6 Wavefronts, Sound Barriers, and Mach Cone (T-T)
		6.6.1 Emitting Sound Waves
		6.6.2 Data Structure and Nomenclature
		6.6.3 Spreadsheet Solution
		6.6.4 Python
	6.7 Questions and Tasks
7 Integration of Newton’s Equation of Motion
	7.1 Introduction: Approximated Mean Value Instead of Exact Integration
		7.1.1 Newton’s Equation of Motion
		7.1.2 Four Methods for Estimating the Average Acceleration in a Time Segment
		7.1.3 Tactical Approaches in Python and Excel
	7.2 Harmonic Oscillation with “Progress with Look-Ahead” and “Runge–Kutta”
		7.2.1 Equation of Motion
		7.2.2 Data Structure and Nomenclature
		7.2.3 Spreadsheet Calculation
		7.2.4 Python
	7.3 Falling from a (Not Too) Great Height
		7.3.1 Limiting Cases, Analytically Solved
		7.3.2 Data Structure and Nomenclature
		7.3.3 Spreadsheet
		7.3.4 Python
	7.4 Stratospheric Jump
		7.4.1 Data Structure and Nomenclature
		7.4.2 Spreadsheet Calculation
		7.4.3 Python
	7.5 A Car Drives with Variable Power
		7.5.1 Various Types of Power
		7.5.2 Data Structure and Nomenclature
		7.5.3 Excel
		7.5.4 Python
	7.6 Bungee Jump
		7.6.1 Simulation of the Motion
		7.6.2 Analytical Calculations
		7.6.3 Data Structure and Nomenclature
		7.6.4 Excel
		7.6.5 Python
	7.7 Questions and Tasks
8 Random Numbers and Statistical Reasoning
	8.1 Introduction: Statistical Experiments Instead of Theoretical Derivations
	8.2 Equi-Distributed Random Numbers, Frequencies of Occurrence, Chi2 Test
		8.2.1 A Spreadsheet Experiment with Random Numbers
		8.2.2 Data Structure and Nomenclature
		8.2.3 Python
	8.3 Points Randomly Distributed in a Unit Square
		8.3.1 Creation and Distribution of the Points
		8.3.2 Data Structure and Nomenclature
		8.3.3 Excel
		8.3.4 Python
		8.3.5 Why Calculate Twice?
	8.4 Set Operations in Numpy
		8.4.1 Sets
		8.4.2 Data Structure and Nomenclature
		8.4.3 Python
	8.5 Normally Distributed Random Numbers
		8.5.1 Normal Distribution, Probability Density and Distribution Function
		8.5.2 Random-Number Generator and Frequencies of Occurrence
		8.5.3 Where Do Observed and Theoretical Frequencies Fit Better Together?
		8.5.4 Data Structure and Nomenclature
		8.5.5 Python
		8.5.6 Excel
	8.6 Random-Number Generator, General Principle
	8.7 Diffraction of Photons at a Double-Slit
		8.7.1 Physical Background: Wave-Particle Dualism
		8.7.2 Cos2 Distribution
		8.7.3 Data Structure and Nomenclature
		8.7.4 Python
		8.7.5 Excel
		8.7.6 Simulation in a Spreadsheet
	8.8 Chi2 Distribution and Degrees of Freedom
		8.8.1 Data Structure, Nomenclature
		8.8.2 Python
	8.9 Questions and Tasks
9 Evaluation of Measurements
	9.1 Introduction: We Know Everything and Play Stupid
	9.2 Weighing a Glass Substrate
		9.2.1 Discussion on the Accuracy of a Balance
		9.2.2 Data Structure and Nomenclature
		9.2.3 Excel
		9.2.4 Python
	9.3 A Procedure for Rounding to Relevant Digits
		9.3.1 Numerical Evaluations
		9.3.2 Spreadsheet Calculation
		9.3.3 Python Function
		9.3.4 VBA Function
	9.4 Increasing the Measuring Accuracy Through Repetition
		9.4.1 Standard Deviation and Standard Error of the Mean Value of a Measurement Series
		9.4.2 Data Structure and Nomenclature
		9.4.3 Python Program
		9.4.4 Spreadsheet Layout for This Task
		9.4.5 How to Report a Measurement Result
	9.5 The t Statistics Connects Confidence Interval with Confidence Level
		9.5.1 Student's t Distribution
		9.5.2 Data Structure and Nomenclature
		9.5.3 Spreadsheet Calculation
		9.5.4 Python Program
	9.6 Combining Results from Several Measurement Series
		9.6.1 Combining Two Measurement Results
		9.6.2 Data Structure and Nomenclature
		9.6.3 Spreadsheet Calculation
		9.6.4 Python, Internally and Externally Consistent Error of the Combined Result
	9.7 Propagation of Standard Deviations
		9.7.1 Rules for Propagation of Standard Deviations
		9.7.2 Data Structure and Nomenclature
		9.7.3 Spreadsheet Calculation
		9.7.4 Python Program
	9.8 Propagation of Confidence Intervals
		9.8.1 From Variance to Confidence
		9.8.2 Sum and Product of Two Measurands
	9.9 Mass of a Thin Film on a Glass Substrate
		9.9.1 Instructions for Use for Accurate Measurements and Their Results
		9.9.2 Data Structure and Nomenclature
		9.9.3 Spreadsheet Solution
		9.9.4 Python Program
	9.10 Questions and Tasks
10 Fitting Trend Curves to Data Points
	10.1 Introduction: Linear and Nonlinear Regression
		10.1.1 Straight Line Through Data Points by Sight
		10.1.2 Multilinear Regression
		10.1.3 Nonlinear Regression
		10.1.4 Coefficient of Determination R2
		10.1.5 C-spec Error with Iterative t Adaptation
	10.2 Linear Trend Line
		10.2.1 Creating Data Points and Evaluating Them
		10.2.2 Data Structure and Nomenclature
		10.2.3 Spreadsheet Calculation with Linest
		10.2.4 Python Program
	10.3 Fitting a Polynomial Trend Line to Data Points with Multilinear Regression
		10.3.1 Introduction
		10.3.2 Data Structure and Nomenclature
		10.3.3 Spreadsheet Solution
		10.3.4 Python Solution
	10.4 Exponential Trend Line
		10.4.1 Exponential and Logarithm
		10.4.2 Exponential or Polynomial?
		10.4.3 Data Structure and Nomenclature
		10.4.4 Python Program
		10.4.5 Spreadsheet Solution
	10.5 Solving Nonlinear Equations
		10.5.1 Intersection of Straight Lines with a Parabola
		10.5.2 Data Structure and Nomenclature
		10.5.3 Spreadsheet Calculation
		10.5.4 Python Program
	10.6 Temperature Dependence of the Saturation Magnetization of a Ferromagnet
		10.6.1 Langevin Function
		10.6.2 Data Structure and Nomenclature
		10.6.3 Spreadsheet Layout
		10.6.4 Python
	10.7 Fitting Gaussians to Spectral Lines with Nonlinear Regression
		10.7.1 Fitting the Sum of Two Gaussians to Data Points
		10.7.2 C-spec Errors of the Coefficients by a Statistical Simulation
		10.7.3 Data Structure and Nomenclature
		10.7.4 Python
		10.7.5 Spreadsheet
		10.7.6 C-spec Error of the Optimized Coefficients by Simulation-Based t Adaptation
	10.8 Questions and Tasks
Index
Index