Table Of Content

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: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: [email protected]. 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

ebook img

Excel by Example A Mcrosoft Excel Cookbook for Electronic Engineers PDF

385 Pages·2004·14.55 MB·english
by  Aubrey Kagan
#Computers - Applications & Software
Save to my drive
Quick download
Download
Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Excel by Example A Mcrosoft Excel Cookbook for Electronic Engineers

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: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: [email protected]. 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

See more

The list of books you might like

Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.
DMCA & Copyright: Dear all, most of the website is community built, users are uploading hundred of books everyday, which makes really hard for us to identify copyrighted material, please contact us if you want any material removed.
Copyright @ PDFdrive.to, 2022 | We love our users