The Diode Exercise in Excel
An important part of the diode exercise is drawing charts of the forward voltage of the diode and the effective resistance of the diode. Each of these show specific characteristics of the diode that should be understood by the Bench Tech. Collecting the information is only the first step. We can draw these charts by hand on graph paper. Using Excel instead gives you an understanding of how to use Excel. This is another goal in itself.
Prerequisites for this exercise
1) Basic knowledge of using the Radio Shack Electronics Learning Lab, or an equivalent breadboard.
2) Basic abilities in using a meter to measure DC Voltage.
3) Ability to read resistor color codes.
4) A computer with Excel if you are going to do the exercise in Excel. Graph paper if you choose to do the exercise on paper instead.
5) Basic familiarity with diodes.
6) Familiarity with Ohm's Law to make Voltage, Current and Resistance calculations.
Expected completion time: 1 hour to 2 hours. The first time may take 2 hours. The advantage to using Excel is that once you have the file set up you can just copy it to make following exercises.
The exercise
1) Select a diode to do the exercise with.
2) Select a resistor within the safe limits of the diode. Reference a data book that contains operating characteristics for the diode. If no data is available keep the maximum current below 100 mA, but higher than 1 micro-Amp. For most diodes the range of resistors is from 10 Ohms to 1,000,000 Ohms when running from 3 V.
3) Make a simple series circuit using a diode with the cathode connected to ground and a resistor connecting to +3 V.
4) Measure the voltage at VCC. (If using the Excel approach enter this information in cell C3, or the equivalent of it if you changed the format.)
5) Measure the voltage across the diode. (If using the Excel approach enter this information in column B, or the equivalent of it if you changed the format.)
6) Calculate the voltage across the resistor. (VCC – V diode). (This step is for an exercise in Ohm’s Law. You could just measure the voltage across the resistor directly.)
7) Find the current through the diode. We can find this by finding the current through the resistor (V / R = I). Since this is a simple series circuit, the current through the resistor will be the same as the current through the diode.
8) Calculate the effective resistance of the diode at that current. (V diode / I = R).
(If using the Excel approach all the calculations are done for you automatically.)
9) Repeat the exercise using different resistors. Keep the current in the safe operating range of the diode.
10) Graph the voltage across the diode at different currents.
11) Graph the effective resistance of the diode at different currents.
Repeat the whole exercise using different diodes.
What can we tell from the exercise?
Different types of diodes have a different pattern of forward voltage.
At different currents the voltage across the diode changes to some degree. These changes reflect the changing resistance of the diode at various currents. Below a certain level the change is major. Once the diode has sufficient operating current the voltage across the diode stays fairly flat, but its resistance still changes as current through the diode changes.
If we get to know the diodes well enough we can tell the current in the circuit by measuring the voltage drop across the diode. When we put the meter on Diode Check and measure the diode we get a value in millivolts that is across the diode. If we measure a 1N5819 we get a reading of around 175 mV. The attached exercise for the 1N5819 shows us that the 1N5819 with 175 mV across it probably has about 600 A being pulled through it.
3 Volts is okay for most general purpose diodes. For some LEDs and for Zener Diode Exercises you may have to use a higher voltage.
Using Excel
You can create the Excel file and fill in the information as you do the exercise or you can do the exercise and put it in Excel later.
The following pages give you an idea of what the spreadsheet could look like. This is just one way of recording and displaying the information. Feel free to get creative with it if you desire.
The spreadsheet is organized in lettered Columns and numbered Rows. Each cell is referred to by its Column-Row. A1 is in the top left corner. For more predictable operation we should format the cells for the type of data we will be entering in that cell. We may choose that it be a number, text, currency, time, date or a long list of other possibilities. For example:
1) Move the cursor to select cell C3.
2) Right click the mouse and select "Format Cells".
3) Select it to be a "Number" with "two decimal places".
In column A we will enter the resistor value we are going to use at that step. Using the range of 10 Ohms to 1,000,000 Ohms we enter in column A all possible values between 10 Ohms and 1,000,000 Ohms. Format the cells as a number with no decimal place.
In column B we will enter the Voltage we actually measure across the Diode. Format these cells as a Number, with three decimal places.
In column C we will enter a Voltage across the resistor. Format these cells as a number with three decimal places. Since this is a calculated value we enter a formula into these cells. To do this we select do the following:
1) Select the cell where the results should go.
2) Enter "=" followed by the calculation. In this case we want to calculate the voltage across the resistor. We know the applied voltage we put in cell C3. We know the voltage across the diode. For cell C5 we would put "C3-B5". Starting with the "=" tells Excel that the following information is a formula to be executed not the data to enter in the cell.
In column D we will calculate the current in the circuit. Since it is a series circuit if we can find the current at any point we have found the current at any point in that circuit. We know the value of the resistor and the voltage across it, so in each cell in cell D5 we need to enter the formula "=C5/A5". This is C5 (our voltage across the resistor) divided by A5 (the resistance). The result gives us the current. Format these cells as a number with 6 decimal places.
In column E we will calculate the effective resistance of the diode. We know the voltage across the diode. We know the current. We enter a formula in cell E5 that calculates the diode's resistance, "=B5/D5". That is the voltage across the diode divided by the current through the diode.
Complete entering the formulas in all the cells that have calculations and format the cells for the type of information that will be entered.
Save the file making the name change of your choice. Save your work often along the way, just in case.
Once you have all the data entered from the exercise the next step is to create a chart of Forward Voltage, Forward Current and effective resistance of the diode. To create a chart you select the area of the spreadsheet you want to make a chart of.
1) To chart Diode Voltage you move the cursor to the first entry in that column, hold down the left mouse button and select to the bottom entry in that column.
2) Select "Insert" then "Chart". Select the type of chart you want to make. Line was used in the example.
The steps that follow depend on the type of chart you chose. The steps are fairly easy to follow. Try a few of them and see how they look. At the end you may choose to add the chart to the file you are working on or create the chart as a separate file.
Create charts for Current and Diode Effective Resistance.