Your Reading List

Excel Tutorial: Advanced Topics

Excel is a great tool for tracking, monitoring and calculating different operations, expenses and cash flows that occur on your farm. Excel has built in features that can make the its use much more accurate, timely and effective. A few of these features are the autosum, trace, sort and filter options. Having a general knowledge of these functions allows you to create much more complicated and expansive worksheets with fewer worries of error.

When working with Excel, always remember to save the product you would like to keep often. From there you can play with the numbers to find various results without deleting or harming the copy you want to keep for good — as long as you do not “save” over it. Also one click mistakes can always be undone quickly by using the undo arrow button located at the top of the screen.

I’ll dedicate a few paragraphs to each of these handy features.

AUTO-SUM

Auto-sum is great for the addition of multiple cells in a column without having to create a formula or do the math yourself. This reduced the chance of human error.

For example, type the numbers of 12, 36, 54 and 22 in column A, rows 1 through 4. Now place the arrow on the cell A1 click and hold the left mouse button scrolling the mouse down to highlight all the relevant numbers you would like to add together. Next you need to find the auto-sum button, which is located on the far right side of the standard toolbar. The button will have the S on it and possibly say autosum depending on the version of Excel you have. Once you press this button you will see that it automatically adds the numbers and places the total directly below in cell A5.

To put the auto-sum total in a cell other than directly below, here’s what you do: Select the cell you want the total to appear in. Next, go to the Formula box (fx) at the top of the worksheet and type in “=Sum(A1:A4).” The “=” sign creates a mathematical formula for the cell instead of text. Sum(A1:A4) tells Excel to add the cells from A1 to A4. This will give you the same result as the above, but puts it in a cell of your choosing. You can use any of Excel’s mathematical operators or functions for multiple cells in this way. For a quick example, if you wanted an average of these numbers instead of the sum, type “=Average(A1:A4)” in the function box.

SORT AND FILTER

I use the sort and filter function to quickly and easily sort results from different expense sheets, or when looking at marketing options, to determine how the numbers rank compared to one another.

The sort and filter button is shown by the graphic of an A over a Z with a funnel to the direct right of it. By clicking on this button, you’ll see there are many options in this function. (Look at diagram 1 to help visualize the following

Formula tracing functions highlight what numbers are involved in each formula and allow you to quickly and easily double check to make sure the numbers you want are all in the right places.

procedure.) To do a quick ranking, highlight the cells you would like to rank and then select “sort largest to smallest” option from the sort and filter menu. If we take the same numbers from the summation above, Excel will sort them as 54, 36, 22, 12.

Step one when diagnosing electrical problems is to make sure the battery is functioning properly. A properly maintained, secured and charged battery should last a long time. Most battery failures can be traced back to neglect or improper use. As shown in the diagrams, the battery produces voltage and current from a chemical reaction.

(See photo 1.)

This reaction can be repeated numerous times because there are no “wear items” in a battery. So why do batteries fail? The discharge process puts sulphur on both plates of the battery, if everything functions perfectly the charging process should completely reverse the process. (See photo 2.) If there is a corroded connection between the alternator and battery and the battery does not completely recharge, some of the sulphur stays on the plates and over time it becomes permanently fixed.

Plate surface area is what determines battery capacity; so if parts of the plates are “sulphated,” battery capacity is lost. The charging and recharging process should happen slowly enough that the battery does not get hot. If a battery is completely discharged and then charged rapidly, either with a charger or the alternator, the resulting heat will bake the sulphur onto the plates or twist the plates reducing the life of the battery.

Also, ensure the battery hold-downs are properly secured, the top of the battery is clean and dry and the correct battery cable ends are used. If your battery still has caps on it, you can check the specific gravity of the electrolyte with a battery hydrometer.

Specific gravity is a comparison of the weight of a fluid compared to water. A fully charged battery at 21C should have a specific gravity of about 1.260. Because water has a specific gravity of one, the closer the number is to one, the weaker the acid solution. The missing sulphur therefore must be on the plates, which indicates a poorly charged battery or one that needs to be replaced.

Cell readings should be fairly close to each other. Never add more electrolyte to a battery, and always use distilled water to top up battery fluid levels. Unfortunately, battery testers, other than hydrometers, are quite expensive. So with a sealed battery, it will have to be taken it to a service centre to get checked out. You can get small battery load testers, but they will not provide enough load for a large, heavy-duty battery. They are only good for automotive or recreational vehicle use.

CHECKING THE ALTERNATOR

The alternator installed by a manufacturer is designed to meet all of the vehicle’s electrical requirements. So before adding a bank of lights or other electrical equipment, make sure the alternator can handle the extra load. If it can’t, it will need to be replaced by one with a higher output.

An alternator works by spinning a magnetic field inside a coil of wire. The voltage regulator regulates the amount of current to the magnetic field, which limits the output voltage of the alternator. The current to the magnetic field passes through a set of brushes and a set of slip rings. The magnetic field is called the rotor and the output winding is called the stator.

The current output of an alternator is determined by the number of stator windings and cannot be varied. Because the current coming from the stator is alternating current (AC), it needs to be changed to direct current (DC) before it can be used in a vehicle. There are six one-way electrical check valves called diodes that take the AC current and “rectify” (change) it to DC current.

To diagnose an alternator charging problem, make sure the battery and drive belt are in good condition. Ensure the belt is tensioned properly.

Then, with a voltmeter you can do a few important tests to determine what the problem is.

All alternators have a large wire going from the alternator to the battery. Sometimes to save wire, it runs to the starter and relies on the cable from the starter to the battery to complete the circuit.

With the key off, there should be battery voltage at that terminal

As the battery charges, sulphate drops off the lead plates and returns to the electrolyte solution. To improve the life of the battery, you want to charge it fully each time. Otherwise sulphate left on the lead plates can remain permanently, reducing battery capacity.

on the alternator. When disconnecting that wire, watch for a large spark, which means there is probably a bad diode in the alternator. A bad diode will lower the alternator output.

Older alternators can have external regulators to regulate the field current. (See photo 3.) With the key switch on, make sure there is battery power at the regulator. Check the wires between the regulator and the alternator to make sure they are not broken.

If the regulator is inside the alternator, there will be at least two smaller wires going to the alternator aside from the main output wire. (See photo 4.) Both of these wires need to have battery voltage when the key is on.

The regulator controls the magnetic field of the rotor, which also magnetizes the alternator shaft. With the alternator running, see if a screwdriver will stick to the end of the alternator on the bearing cap. If it does not, then there is likely a regulator problem.

CHECK FOR VOLTAGE DROPS

Voltage drops indicate how many volts it takes to push the current through a circuit. To check voltage drops, the engine must be running. It is a good idea to turn some lights on to provide a load for the alternator. Voltage drop readings should always be low, preferably zero. If the readings are high, clean the electrical connections between the alternator and battery.

Finally, with a good battery, engine running and no lights on, a reading at the battery with the voltmeter should show about 14.7 volts — if everything is working perfectly.

Dietrich Schellenberg, Marty Zuzens, Peter Lung and Dennis White are instructors at Assiniboine Community College’s School of Trades and Technology in Brandon, Man. If you’re interested in more information about the training programs offered there, check out www.assiniboine.net.

About the author

Contributor

Jay Peterson farms near Frontier, Sask.

Jay Peterson's recent articles

Comments

explore

Stories from our other publications