Forums :: Resources :: Features :: Photo Gallery :: Vintage Radio Shows :: Archives
Support This Site: Contributors :: Advertise


It is currently Aug Sun 25, 2019 6:41 am


All times are UTC [ DST ]





Post New Topic Post Reply  [ 7 posts ] 
Author Message
 Post subject: Using Excel for complex problem solving
PostPosted: May Fri 13, 2016 6:42 pm 
Member
User avatar

Joined: Jun Mon 01, 2015 7:58 pm
Posts: 257
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
 Profile  
 
 Post subject: Re: Using Excel for complex problem solving
PostPosted: May Fri 13, 2016 8:00 pm 
Member
User avatar

Joined: Jan Thu 01, 1970 1:00 am
Posts: 13930
Location: S. Dartmouth MA 02748-1225 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
 Profile  
 
 Post subject: Re: Using Excel for complex problem solving
PostPosted: May Fri 13, 2016 8:45 pm 
Member
User avatar

Joined: Jul Mon 26, 2010 8:30 pm
Posts: 26231
Location: Annapolis, MD
Slightly off-topic, but....i thought that LibreOffice was now preferred...but I am not at all current on what happened with OpenOffice.

Yes---Excel or equivalent is a major power tool

_________________
-Mark
"Measure voltage, but THINK current." --anon.


Top
 Profile  
 
 Post subject: Re: Using Excel for complex problem solving
PostPosted: May Sun 15, 2016 8:01 pm 
Member
User avatar

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
 Profile  
 
 Post subject: Re: Using Excel for complex problem solving
PostPosted: Jun Thu 02, 2016 4:09 am 
Member
User avatar

Joined: Oct Thu 18, 2007 11:34 am
Posts: 4120
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. :cry:

_________________
Visit our site:
http://antique-radio-lab.forumotion.com/
I was smokinradios Long time ago (2004)


Top
 Profile  
 
 Post subject: Re: Using Excel for complex problem solving
PostPosted: Jun Thu 02, 2016 11:08 am 
Member

Joined: Jan Thu 01, 1970 1:00 am
Posts: 3439
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. :cry:


BASE is the database App in OpenOffice.


Top
 Profile  
 
 Post subject: Re: Using Excel for complex problem solving
PostPosted: Jun Thu 02, 2016 11:19 am 
Member
User avatar

Joined: Jul Mon 26, 2010 8:30 pm
Posts: 26231
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
 Profile  
 
Post New Topic Post Reply  [ 7 posts ]  Moderator: Chuck Schwark

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest



Search for:
Jump to:  




























Privacy Policy :: Powered by phpBB