Excel by Example: A Microsoft Excel Cookbook for Electronics Engineers

385 Pages·2004·14.77 MB·English

by  Aubrey Kagan

Excel by Example
This page intentionally left blank
Excel by Example
A Microsoft® Excel Cookbook for Electronics Engineers
By Aubrey Kagan
AMSTERDAM • BOSTON • HEIDELBERG • LONDON
NEW YORK • OXFORD • PARIS • SAN DIEGO
SAN FRANCISCO • SINGAPORE • SYDNEY • TOKYO
Newnes is an imprint of Elsevier
Newnes is an imprint of Elsevier
200 Wheeler Road, Burlington, MA 01803, USA
Linacre House, Jordan Hill, Oxford OX2 8DP, UK
Copyright © 2004, Elsevier Inc. 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,
without the prior written permission of the publisher.
Permissions may be sought directly from Elsevier’s Science & Technology Rights De-
partment in Oxford, UK: phone: [phone], fax: [phone], e-mail:
[e-mail]. You may also complete your request on-line via the Elsevier
homepage (http://elsevier.com), by selecting “Customer Support” and then “Obtaining Per-
missions.”
Recognizing the importance of preserving what has been written, Elsevier prints its books on
acid-free paper whenever possible.
Library of Congress Cataloging-in-Publication Data
(Application submitted.)
British Library Cataloguing-in-Publication Data
A catalogue record for this book is available from the British Library.
ISBN: 0-7506-7756-2
For information on all Newnes publications
visit our website at www.newnespress.com
04 05 06 07 08 09 10 9 8 7 6 5 4 3 2 1
Printed in the United States of America.
In memory of Jonathan Moshe Kagan
This page intentionally left blank
Contents
Acknowledgments .......................................................................................................xiii
Introduction ...............................................................................................................xiv
What’s on the CD-ROM? ..........................................................................................xviii
EXAMPLE 1: Voltage-to-Current Converter ..................................................................1
Model Description ......................................................................................................................................1
Starting Excel .............................................................................................................................................2
Data Entry into a Worksheet .....................................................................................................................3
Autofill .......................................................................................................................................................5
Bulk Formatting .........................................................................................................................................7
Formulas .....................................................................................................................................................8
Copying Formulas ......................................................................................................................................9
Relative and Absolute References ...........................................................................................................10
Naming Cells ............................................................................................................................................12
Hiding Cells .............................................................................................................................................15
Borders ......................................................................................................................................................15
Bells and Whistles ....................................................................................................................................17
Conditional IF and Absolute Value .........................................................................................................17
Chart ........................................................................................................................................................17
Error Bars ..................................................................................................................................................20
Adding a Trendline ..................................................................................................................................21
Macro: Timer ............................................................................................................................................22
EXAMPLE 2: Baud Rate Selection ...............................................................................26
Model Description ....................................................................................................................................26
Setup Workbook ......................................................................................................................................27
Hexadecimal .............................................................................................................................................29
Lookup Tables ..........................................................................................................................................30
Conditional Formatting ...........................................................................................................................33
Macro .......................................................................................................................................................35
EXAMPLE 3: Mean Time Between Failures (MTBF) ....................................................41
Model Description ....................................................................................................................................41
Factors ......................................................................................................................................................42
vii
Excel by Example
Bill of Material .........................................................................................................................................44
Calculating the Quality Factor ................................................................................................................49
Calculate Electrical Stress Factor .............................................................................................................50
Calculation of λ ......................................................................................................................................52
G
Scenario ....................................................................................................................................................54
EXAMPLE 4: Counting Machine Cycles .......................................................................58
Model Description ....................................................................................................................................58
Importing the File ....................................................................................................................................58
Extracting Op-code ..................................................................................................................................62
Opening a Second Workbook ..................................................................................................................63
Cross Workbook Reference ......................................................................................................................67
Easing the Pain of Nested IFs ...................................................................................................................67
EXAMPLE 5: Character Generator ..............................................................................69
Model Description ....................................................................................................................................69
Creating the Basic Workbook ..................................................................................................................70
LEN Function ...........................................................................................................................................71
Forms Controls .........................................................................................................................................71
Text Orientation ......................................................................................................................................75
Comments ................................................................................................................................................75
Double-Click Macro ................................................................................................................................76
Macro Activation by the Command Button ...........................................................................................78
Save to Data File ......................................................................................................................................81
Usage ........................................................................................................................................................85
EXAMPLE 6: 8052 Microcomputer Register Setup ......................................................86
Model Description ....................................................................................................................................86
Spreadsheet Concept ...............................................................................................................................86
Counter/Timer 0 Sheet ............................................................................................................................93
Timer Counter Control Register TCON .................................................................................................97
Counting Types ........................................................................................................................................97
Macros to Hide and Unhide ..................................................................................................................102
Adding Forms .........................................................................................................................................106
Add Image Control ................................................................................................................................108
Timer/Counter 1 Sheet ..........................................................................................................................111
Timer/Counter 2 Sheet ..........................................................................................................................112
Serial Port Sheet ....................................................................................................................................113
Interrupt Control Sheet .........................................................................................................................113
Summary Sheet ......................................................................................................................................115
Initialize Values ......................................................................................................................................116
Conclusion .............................................................................................................................................116
EXAMPLE 7: Finding the Optimal Resistor Combination: LP 2951 ............................118
Model Description ..................................................................................................................................118
Custom Autofill ......................................................................................................................................118
Data Tables .............................................................................................................................................120
Min Function .........................................................................................................................................123
MATCH Function .................................................................................................................................123
viii
Contents
INDEX Function ....................................................................................................................................124
Block Conditional Formatting ...............................................................................................................125
EXAMPLE 8: Resistor Color Code Decoder Using Speech Input ................................127
Model Description ..................................................................................................................................127
Implementing Speech Recognition .......................................................................................................129
Viewing and Hiding the Language Bar ..................................................................................................129
Worksheet Setup ....................................................................................................................................130
Macros ....................................................................................................................................................130
Custom Toolbar ......................................................................................................................................134
Adding Speech .......................................................................................................................................137
Evaluate the Color Code ........................................................................................................................137
Text to Speech .......................................................................................................................................141
Conclusion .............................................................................................................................................142
EXAMPLE 9: RTD to 4–20 mA Converter: XTR105 ..................................................143
Model Description ..................................................................................................................................143
Acquiring RTD Tables ...........................................................................................................................144
Lookup RTD Value ................................................................................................................................148
Creating a Function ...............................................................................................................................149
Accessing a Function ............................................................................................................................150
Adding a Help Description to a Function .............................................................................................152
Creating the Model in Excel ..................................................................................................................152
Standard Resistor Values ........................................................................................................................155
Creation of Add-In ................................................................................................................................158
Installing the NearestValues Add-In .....................................................................................................158
Back to the Project At Hand .................................................................................................................159
Prompting for User Input .......................................................................................................................161
Printout ..................................................................................................................................................162
Running Macros when the Workbook is Started ..................................................................................163
Running from the Desktop ....................................................................................................................166
EXAMPLE 10: Voltage Regulator: LM317 .................................................................167
Model Description ..................................................................................................................................167
Installing the NearestValues Add-In .....................................................................................................168
Initial Model ..........................................................................................................................................168
Goal Seek ...............................................................................................................................................170
Worst Case Analysis ...............................................................................................................................172
Thermal Analysis ...................................................................................................................................173
Half-Wave Rectification ........................................................................................................................179
True RMS and Integration .....................................................................................................................179
More Preparation ...................................................................................................................................181
Standard Capacitance Value ..................................................................................................................184
Chart ......................................................................................................................................................186
Conclusion .............................................................................................................................................189
EXAMPLE 11: TL431 Adjustable Voltage Reference ..................................................190
Model Description ..................................................................................................................................190
Installing the NearestValues Add-In .....................................................................................................190
ix