
Page 1 of 1

[ 7 posts ] 

Author 
Message 
mrriggs

Post subject: Using Excel for complex problem solving Posted: May Fri 13, 2016 6:42 pm 

Member 

Joined: Jun Mon 01, 2015 7:58 pm Posts: 259 Location: Vancouver, WA

I had mentioned this in my guitar amp thread but thought it might be good to post it here as well. Excel can make designing and analyzing circuits with resistive and reactive components a breeze. Normally the math for this kind of thing can be overwhelming for anyone who is not an engineer, but Excel will do all the hard stuff for you.
The confusion comes from the need to use complex (two dimensional) numbers. The real part of the number is the resistance [R] and the imaginary part of the number is the reactance [X]. When you combine these two you get the impedance [Z]. You don't really need to understand all of this to use it.
Excel has a function called COMPLEX that will convert R and X into Z. For a capacitor use the formula "=COMPLEX(0,1/(2*PI()*F*C))", where F is the frequency and C is the capacitance in Farrads. For an inductor use the formula "=COMPLEX(0,2*PI()*F*L)", where F is the frequency and L is the inductance in Henries. You can cut and paste these formulas into Excel [minus the quotes] then change the bold characters to cell references for the appropriate values. It is not necessary to convert resistors into complex numbers.
These Z values can basically be used just like R values. The only catch is that any formula containing a Z value has to use IMSUM, IMSUB, IMPRODUCT, and IMDIV functions instead of the respective +, , *, and / operators.
For example, most people are probably familiar with the equation for a voltage divider, Vout = (Vin * R2) / (R1 + R2). If you set this up in Excel for a resistive divider you put Vin in cell A1, R1 in cell A2, R2 in cell A3 then the formula "=(A1*A3)/(A2+A3)" in cell A4.
If you replace R2 with a capacitor then you have a basic RC low pass filter and can use the same equation, only substituting the capacitor Z for R2. So, put Vin in cell A1, R1 in cell A2, C in cell A3. The capacitors impedance changes with frequency so you also need to add a frequency value. For the sake of simplicity we will use a single value in cell A4. To figure out the Z value, enter the formula "=COMPLEX(0,1/(2*PI()*A3*A4))" into cell A5. The formula for the voltage divider looks different because we can't use the standard math operators. For cell A6 enter the formula "=IMDIV( IMPRODUCT(A1,A5) , IMSUM(A2,A5) )".
The result is still a complex number so there is one more step. The IMABS function will convert it back to a real number that represents amplitude. In cell A7 enter the formula "=IMABS(A6)". You can also use the IMARGUMENT function to show the phase of the output. It returns the phase angle in radians so you will also need to use the DEGREES function if you want to see it in degrees. In cell A8 enter the formula "=DEGREES( IMARGUMENT(A6) )".
Hope you find this helpful.
Last edited by mrriggs on May Fri 13, 2016 11:02 pm, edited 1 time in total.


Top 


Chas

Post subject: Re: Using Excel for complex problem solving Posted: May Fri 13, 2016 8:00 pm 

Member 

Joined: Jan Thu 01, 1970 1:00 am Posts: 14735 Location: S. Dartmouth MA 027481225 USA

Excell, Open Office, Libre Office are excellent tools for this math. My problem is interpreting the formula into the proper cell and establishing the input cells for the variable. Once done the "tool" is saved to a sheet, variations can be on another sheet.
I do wish I was more of a math whiz as I would like to see curves for things like resistive losses that can be printed.
I have Ubuntu 10.04.4 and peloaded Open Office.
Thanks for the good work.
I would like to see more engineering questions answered with excel based formulae. Helps the math learning curve.
Chas
_________________ Smith's Ale Gives Strength, Smith Bros. Brewers, New Bedford MA


Top 


pixellany

Post subject: Re: Using Excel for complex problem solving Posted: May Fri 13, 2016 8:45 pm 

Member 

Joined: Jul Mon 26, 2010 8:30 pm Posts: 27267 Location: Annapolis, MD

Slightly offtopic, but....i thought that LibreOffice was now preferred...but I am not at all current on what happened with OpenOffice.
YesExcel or equivalent is a major power tool
_________________ Mark "Measure voltage, but THINK current." anon.


Top 


Poppa Fuse

Post subject: Re: Using Excel for complex problem solving Posted: May Sun 15, 2016 8:01 pm 

Member 

Joined: Jun Thu 18, 2015 12:28 am Posts: 51

pixellany wrote: i thought that LibreOffice was now preferred... It is for me. pixellany wrote: but I am not at all current on what happened with OpenOffice. Sun 'ran out of gas' and sold to Oracle. Time to move on.


Top 


Radiosmoker

Post subject: Re: Using Excel for complex problem solving Posted: Jun Thu 02, 2016 4:09 am 

Member 

Joined: Oct Thu 18, 2007 11:34 am Posts: 4228 Location: Port Orchard, Wa 98366

Downloaded Libre Office, I tried using the Database, it requires JAVA, however it has to be the 32 bit version. I tried to install and delete both Libre and Java. I know that the document and spreadsheet apps are doable. I am using Windows 10, there are bugs in both Java and Libre, for them not being compatable, However I have Open Office and it works, but has no Database app.
_________________ Visit our site: http://antiqueradiolab.forumotion.com/ I was smokinradios Long time ago (2004)


Top 


mrx

Post subject: Re: Using Excel for complex problem solving Posted: Jun Thu 02, 2016 11:08 am 

Joined: Jan Thu 01, 1970 1:00 am Posts: 3509 Location: Massachusetts

Radiosmoker wrote: Downloaded Libre Office, I tried using the Database, it requires JAVA, however it has to be the 32 bit version. I tried to install and delete both Libre and Java. I know that the document and spreadsheet apps are doable. I am using Windows 10, there are bugs in both Java and Libre, for them not being compatable, However I have Open Office and it works, but has no Database app. BASE is the database App in OpenOffice.


Top 


pixellany

Post subject: Re: Using Excel for complex problem solving Posted: Jun Thu 02, 2016 11:19 am 

Member 

Joined: Jul Mon 26, 2010 8:30 pm Posts: 27267 Location: Annapolis, MD

Indeed....LibreOffice is a fork of OpenOffice. It all traces back to a product called "Star Office", which...IIRC...started in Germany.
There are situations where the standard install does not pick up all the modules, but they are all available.
_________________ Mark "Measure voltage, but THINK current." anon.


Top 


Who is online 
Users browsing this forum: No registered users and 6 guests 

