Cheat Sheet for Graphing with Rankem and Excel



Cheat Sheet for Graphing with Rankem and Excel

By Ron Van Nurden

The Rankem Part:

Load all your farms in Rankem. The select Rank Reports and Group Listing. Then choose what ever parts you want to use to graph in Excel. I choose crop in the example I am working through.

On the screen that follows select the crop and land tenure type. Then in the large box called “Report Items to Include”, unselect everything. Then go into the screen and choose the two items you want to graph. I chose Net Returns per acre and Fertilizer cost for example. Those are the only two boxes checked.

Next go to the box that says “Type of Report” and check the box that says “Sort by one individual item” and select the factor you want the report sorted by. I chose Net Return.

Preview the report to make sure it is right. Then choose print. Check the box that says “Text File” and name the file. I used “Net Return vs Fertilizer Cost on Rented Land” in my example. Click the file folder box on the side of file name and tell the program where to store the file. I put mine on the desk top.

The data is now ready to be used by Excel.

The Excel Part:

You first need to get the data imported and ready to graph. To import the Data, open Excel and go to “Data” on your command line. You want to import external data. Choose import Data and then go to the directory where you have stored the Rankem data. In my case I went to the desk top and picked “Net Return vs Fertilizer Cost on Rented Land”. Open the file and you will get a dialog box. Click “Fixed Width” in the top part of the box. Leave everything else as is and click next. Leave everything on the next screen as is and click finish. Tell the Excel program what cell you want to use to start the import. I usually leave the curser in the cell I want to start with before I start the import data. Then it will default to that cell when I get to this screen. Click Okay and the data is imported to Excel.

The next step is to clean up the data so that you can make a graph of the data. You need to delete all the lines that are not needed. Your top line should be for the identification of you data. All other lines should contain data. Any blank lines should be deleted. If you want to add more data. Move your curser to the bottom of the data set you just imported and go through the process again. In my example I also imported the Owned land data below the Rented Data. Clean the new data up as before.

If you imported new data you need to sort the data in descending order. Make sure your cursor is in the correct column you want to sort. I moved the cursor to the Return column. Then go to “Data” on the command line and choose Sort and check the box descending order. In my example Excel sorted the Return column from highest to lowest and brought along the corresponding fertilizer cost.

With the data all ready to go, I can start to develop my graph. I put my cursor in the returns column and then went to “Insert” on my command line. Then I choose Chart. The program will ask me what type of chart I want to insert. I choose “Line” in my example. I used the type of line graph the program gave me and clicked next. By setting up the data the way I did, the program automatically selected the correct data range all I have to do on this screen is to make sure the column box is checked and click next. On this screen I put the name of my graph in the box. In my example I called it “Corn Returns vs Fertilizer Cost”. I also labeled the two axis. You will see the information you type into the boxes on the sample graph so look to make sure it is the way you want it. The last step is to tell the program whether you want the graph on a separate sheet or imbedded into the current sheet. I choose to put it in the same sheet and choose the second box. Then click finish.

Your graph will appear. Since the values on the graph may be very different between the two pieces of data you are graphing, you will probably want to put two “y” axis in the graph. In my example I wanted an axis for return dollars and another axis for fertilizer dollars. To do this I double clicked on the fertilizer data line in the graph. This brought up a dialog box where I could change the looks of the line but also create another axis on the right hand side of my graph. To do this I choose the axis tab in the dialog box. The box gives me two choices a Primary Axis and a Secondary Axis. I selected “Secondary Axis” and then clicked okay. To name this new secondary axis, I went to “Chart” on the command line and then Choose chart options. I selected the titles tab and changed the secondary y axis title to “fertilizer dollars”. The graph is now complete.

To draw the trend line, move your cursor on the Fertilizer Cost Data line and click. This will highlight the Fertilizer line. Right click and select “Add Trend line”. In the dialog box that appears I chose “Logarithmic” by clicking on that picture and then I clicked okay.

There are many other refinements that can be made to this chart but the process for producing the graph is complete.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download