OpenOffice.org Writer Mail Merge Fields Insert Formula

I use OpenOffice.org, LibreOffice.org to merge from the Calc spreadsheet to the Writer word processor module. I was merging raw scores and wanted to reflect the percentage correct after the raw score. I could have added a column to my grade book that would have calculated the percentage correct, but to do this for every assignment would double the column count of an already expansive spreadsheet.

Fumbling about with the Insert fields dialog box, I could not hit on the right combination. I even tried copying and pasting fields into the Insert formula dialog box, but to no avail. When copied and pasted, the field pastes as the field names only in angle brackets, such as and generates an invalid formula

Rumbling around inside the help files answered many simpler questions, but not how to set up a formula using a Calc spread sheet field name.


Stumbling about the web for an hour was also of no avail. Searches of the pertinent forums that are out there (oooforum, OpenOffice.org community forum, and libreofficeforum) did not yield a solution.

Bumbling back inside Writer I did finally tumble across a solution. Turns out I had to manually type the full field name in filename.tab.field_name format along with the calculation desired.

Thus the calculation for test two was filename.sb1.t2/34. No preceding equals sign is used - one of my many missteps in this process. This was then formatted as a percent using Additional formats...  This is useful because I mail merge my grade book to produce grade reports for each student. Seeing the percentage seems to help the students when the total possible is a number such as thirty-four.

Changes in latitude

SC 130 Physcial Science continued exploring latitude and longitude in laboratory 072. The laboratory centered on the mathematical relationship between minutes of latitude and distance in meters.

Walking a line of longitude northbound

Some students used GPS units to walk the a  line of longitude north. The eight o'clock section walked the E 158° 09.570' line of longitude from N 6 °54.470 to N 6 °54.593. A surveyor's wheel put the distance at 243 meters.

 Alwihter rolls the surveyor's wheel

The eleveno'clock section walked the E 158° 09.569' line of longitude from N 6 °54.462 to N 6 °54.602. A surveyor's wheel put the distance at 284 meters. 

 Jesse and Bersin navigating

The power went off after eleven, thus the 11:00 section did not utilize Google Earth.

 David reads out his final latitude

The 11:00 section chose to cross the entrance road. Coupled with a start slightly further south than the eight o'clock class, the students obtained a longer run of the line of longitude.The data for both sections is available in a Google Docs spreadsheet.

The surveyor's wheel is alwaize popular. Mercy, Nancy, and Leslie

Hide and Seek

Every term the physical science students have located me,  in some terms they have located me within twenty minutes, other terms thirty minutes. The students always find me, the question is when, not whether. This term I wanted a location that would be more challenging to locate.

 

I was hoping for a location that would hold for forty minutes if possible. Using my knowledge of the alignment of lines of latitude and the penchant for the students to usually first "lock on to" the correct latitude line, I chose a latitude that would be potentially problematic if followed blindly.

Although the area appears remote and isolated, in actuality the grass is veritably riddled with trails running to private picnic and rendezvous spots.

The trails fork out to different clearings under small trees, each with tell tale signs of human presence such as the blue styrofoam plate peeking out from the Nephrolepis ferns on the right in the image above.

Two teams simultaneously converged on my position at 45 minutes after the hour. One came in from the east, slogging through impossibly tall Polystachion grass to remain on the line of latitude. The other group had serendipitously made the decision to follow the road until the longitude was correct, and then follow the longitude to the correct latitude. This was, due to the terrain, the slightly easier way to access my location.

The east group, however, was the first to actually spot me in my hiding location.

Both of the successful groups were reduced in number, ultimately only five students arrived at the correct coordinates. That five did so, and in 45 minutes, is a testament to the ability to precisely locate any position on the planet using a GPS unit.

Ardos and Robertson at the correct location.

Jeremiah, having come from the general direction behind him.

Four other groups never did find me. Two groups were "following" an "arrow" that was actually a north indicator. The groups were simply walking due north and, I gathered later, only the swamp stopped them. 

A third group was following a triangle on the "paths" screen that indicates simply where you are on the screen. The result was that they had been walking what was effectively a random walk path. Apparently neither group read the instructions nor had listened to directions on Monday concerning pulling up the latitude and longitude screen.

One group had reached the gym and apparently decided to stay and play basketball in lieu of continuing the search. Had I been in need of actual assistance or rescue, I would have perished at the hands of this group! A member of that group did note, in their defense, that they thought the period had expired by the time they reached the gym.

I later learned that one group had wandered up into the area behind maintenance, effectively the opposite side of the campus on a north-south axis. Their latitude number must have been much larger than the correct number.

Thursday I will debrief the class and pull together what may or may not have been learned today. This activity is a discovery learning activity, although this term the discovery learner was the instructor. I learned primarily that four of six groups did not listen carefully on Monday nor read the directions on the hand-out. For the groups that found me, however, both have a better "gut" level understanding of latitude and longitude. As one student said, "these numbers are like a grid."

Island Food Community of Pohnpei visit

The SC/SS 115 Ethnobotany class visited the Island Food Community of Pohnpei. Coordinator Emihner Johnson gave a presentation on the need to Go Local!

Vanessa, Isabella, Mylinda, and Jasmine make up the front of the class.

In a nation that is number two for obesity and a state where the diabetes rate is 32%, Ms. Johnson led the students through the CHEEF framework. Ms. Johnson noted the need to support local Culture through the growing of traditionally valued crops. She noted too the Health benefits of local foods, especially the high beta-carotene content of particular varieties of local bananas, taro, and pandanus.

Local foods have a lower Environmental impact that foreign foods. The environmental impact of foreign foods that is seen here on Pohnpei is that of the improper disposal of packaging. Yet that is only the tip of an invisible ice berg. Foreign foods are typically energy intensive in their production, and mass production techniques often degrade the environment. The destruction of vast swaths of tropical rain forest for the production of palm oil is but one example of the environmental impacts of foreign foods that are not directly observed by the consumer. Done correctly, using traditional agro-forestry techniques, local foods can be grown in harmony with the environment of these islands.

Ms. Johnson noted too the Economic benefit of local plants. Local plants provide income to local farmers. Foreign foods are simply the export of money directly off island. There is no economic multiplier for money spent on foreign food. For local food, that is money kept on island and in circulation.

Poster courtesy of Island Food Community of Pohnpei

Food security and safety is the F in CHEEF. Food prices are up by 28% with cereals up 44%, there is an emerging food crisis. Local food is food security, foreign food is food insecurity.

Poster courtesy of Island Food Community of Pohnpei

Local foods are not processed foods that are often loaded with salt, monosodium glutamate, sugars, artificial coloring, and preservatives. Local foods have been safely eaten for over 2000 years, the same cannot be said for highly processed snack foods. The ants that live in my car will eat anything, including the feet of passengers. But a collection of foreign highly processed potato chips were scattered in the back seat by a small child. Even after a week there, no ant would come near the chips. Clearly they are not a food product.

After the presentation the class toured the collection of bananas and soft taro plants. The Island Food Community has developed into a valuable living herbarium and a center for display of different varieties.

The class owes a debt of gratitude to the Island Food Community and to Ms. Johnson for the presentation, tour, and, more importantly, the work being done to improve the health of all served by the IFC. Kalahngan!

Plumeria pudica, orchids, a rose, a lily, and an anthurium

Plumeria pudica is not presently listed in the Checklist of the Vascular Plants of Pohnpei, Federated States of Micronesia with Local Names and Uses, 2010 (Allertonia, 10). The plant is, however, an ornamental found in gardens in Kolonia. Thus either the plant was not collected due to location or the plant is a recent introduction. At present my sense is that the plant is a fairly recent introduction. Allertonia 10 is a must have for any Micronesian scientific journal collection as well as for Pacific island botany and ethnobotany collections.

Obspatulate leaves.

I know the days are short, dark, and deeply gray in the northern hemisphere now. Punctuated by occasional days of blindingly cold sun on a white snow. I know that the heat and humidity of an equatorial marine rainy climate can be oppressive to those not acclimatized to the torrid tropics. There are bonuses, however. With proper neglect of the plants one can sit on the front driveway and enjoy a display worthy of any garden show. While others spend hours misting and feeding, building carefully climate controlled greenhouses with lights on timers, we here merely plant and ignore. The fullness of time then yields the following without further ado.

Gratuitous image of an anemone in a newly set up aquarium tank at the College of Micronesia-FSM.

Rotary Club 5k fun run

The first run of 2011, the Rotary Club of Pohnpei 5k, went off under cloudy, cool skies that gave way to rain. Good conditions for running hard here in the equatorial tropics. With children off on overnights, and a spouse still snuggled in bed on a dark morning, I was unaccompanied on the run.

 Sweeter and Debra

Male mster's division winner Gary

Female master's division winner Limat on the left, overall winner Rendy on the far right

The future generation of runners

Gardenia jasminoides Variegata and a cloud

Variegated Cape Jasmine Gardenia (Gardenia jasminoides Variegata, also known as G. augusta, G. grandiflora) in bloom in the garden. One of the pleasures of Pohnpei is that plants such as variegated gardenia bloom with minimal care and attention.

A couple of sites note that the plant is not a prolific bloomer in the tropical lowlands. Here the plant, semi-shaded, blooms regularly and beautifully.

An orchid that opened up in the front yard.

Cumilis humilis puff ball

Heat capacity of metals

Physical Science laboratory 062 built off of a 31 year old video tape title Eureka: Heat and Temperature the students viewed on Monday. The college had acquired the video in 1993.  At the time I showed the video, I was unaware of the age of the video. Despite the age, the humor connected and the students laughed at a number of different sections.

I wanted to chase the idea of heat capacity, and I knew the lab had an eclectic collection of specific heat capacity materials. I opted to use Styrofoam cups as calorimeters because of their simplicity and insulation qualities.

I deliberately chose to avoid the mathematical formality of Q = mcΔT and the loss of heat by the heated metal objects and the gain of heat by the water. On a first run of the experiment I wanted the students to engage in a more open ended exploration of which metal held the most heat.

End of lab wrap-up

The experiment was by no means a discovery or constructivist oriented experiment per se. In a guided discussion I directed the students to fill each cup to the same level, to mass the metal, and to heat the metal to 100 °C. I then asked the students to measure amount of heat as the change in the temperature of the water per gram of metal mass. This is not the specific heat capacity, but is roughly half the specific heat capacity. Not exactly half the specific heat capacity, as detailed in an analysis of the data gathered by the students.

The laboratory wrapped up with my leading a question and answer session as to where the results agreed and disagreed. The variety of incomplete specific heat capacity sets was a real plus: the same metals were measured at different masses. The density of some metals had to be calculated to determine the probable metal.

I also asked for graph suggestions, and the students suggested a column chart.

The 11:00 section was tasked with exploring the discrepancies and attempting to verify the results of the 8:00 section. This went more quickly than the morning session, and, as is often the case, the 11:00 section was much more half-hearted and less enthusiastic. This is a common pattern and appears to in part be a result of the 11:00 lab spanning the lunch hour.

Saraca asoca, Saraca indica

The soil in the triangle between the A classroom building, the B classroom building, and the north faculty office has killed off six Cananga odorata trees. Even the grass struggles to grow in the top soil free red clay. Thus the success of Saraca asoca is a pleasant and fragrant surprise.

Native to India, the tree is known as the Ashoka tree and is used in traditional ethnobotanical medicines. The bark apparently contains an estrogenic ketosterol compound. The bark is also the active ingredient in a number of gynecological medicines.

Scientific classification
Kingdom:  Plantae
(unranked):  Angiosperms
(unranked):  Eudicots
(unranked):  Rosids
Order:          Fabales
Family:  Fabaceae
Genus:          Saraca
Species:  S. asoca
Binomial name:  Saraca asoca (Roxb.) Wilde
Synonyms:       Saraca indica Linnaeus

Cantilevers, physical science, and rational functions

As an experiment, I opted to conclude the week on forces not with a quiz but rather with demonstration of a non-linear system in which a meter stick cantilever arm balances an increasing amount of mass on the other of the suspension line. This was a modification from earlier terms.

The final mass was a whopping 3050 grams suspended by a single string

In 056 Center of gravity and static force, a single 200 gram meter stick is suspended from an overhead track using a string. The exercise begins with no mass hung from one end of the meter stick and the string tied at the 50 cm mark. The meter stick hangs balanced.

A 50 gram mass is added as close to the end as possible, and the string is moved towards the mass in order to rebalance the system. The length of the balancing arm of the meter stick was recorded in a table. Then additional masses were added. The data gathered is contained in the following table.

Cantilever length (cm)	mass (gmf)	Theory (gmf)
50	0	-0
59.5	50	47
61.5	60	60
63	70	70
65.5	90	90
67.7	110	110
69.6	130	129
71.3	150	148
72.8	170	168
74	190	185
77.8	250	250
81.5	350	341
92	1050	1050
95.7	2050	2126
97	3050	3133

When graphed, the result is as seen below.

The small blue squares are the actual data, the green diamonds are the theoretic values based on a rational function. Note that the power function is a poor fit to the data at high mass values.

where rho is the density per linear centimeter for the meter stick. For the system above the value was two grams per linear centimeter. In a spreadsheet with the data arranged as seen above, the formula becomes =2*(5000-100*A2)/(A2-100).

I stumbled onto the system a couple of years ago without realizing that the mathematical relationship involved a rational function. The relation can be derived by setting the torques to be equal. Rational function are fairly rare in the lives of most people, and finding a simply physical system that is modeled by one was a real plus.

I had intended to stop near 300 grams, the limit on my smaller hanging mass set. At around 300 grams I asked whether the meter stick could balance an even higher mass, such as a person. The class was in deep doubt. Some of the students have absorbed that experimental facts on the ground are what count, not asserted hypotheses.

I realized I had to go farther. I found a hanger that would handle the kilogram slot masses. One kilo. Two kilos. Three kilos. "Take a picture!" shouted an amazed student. I then used a second meter stick to remind the students that the meter stick had a mass of only 200 grams, yet was balancing 3050 grams (three kilograms plus a 50 gram hanger).

Few in the class have completed college algebra. In the present text, rational functions are in chapter four - some sections do not reach chapter four. Thus this is a mathematical functional form few students have seen. Terra incognita.

For homework the students are to graph the data using a spreadsheet and add a trend line for the regression type that best fits the data. No spreadsheet offers a fit to an arbitrary function such as a rational function, I am uncertain that such a capacity exists in anything easily and openly available. A WolframAlpha inquiry on fitting to a rational function has no response.

On the next Wednesday class I plan to try to "explain" how the function "works" at cantilever lengths (Lc) of 50 and 100 centimeters. The function produces physically real results only for 50 ≤ x < 100, x ≠ 100. Here the asymptote has a very physical meaning: the mass of the cantilever, or more accurately the torque, is irrelevant to the mass being suspended as the mass is directly under the suspension point. Mathematics made visual and concrete.

This summer I will be teaching a course in exponential and trigonometric equations. My plan to use physically real systems, spreadsheets, and WolframAlpha to bring a deeper level of insight and understanding to the students. A narrower but deeper river. Chapter four and four point two will definitely be on my syllabus. Along with the decay rate of the subsequent bounces of a super ball and the trigonometric sine function of a RipStik, and low mass chains. I may also play with the path function in SVG if I have a fair number of CIS majors in the class - using spreadsheets to generate the data points for an SVG chart. I have used SVG in MS 100 College Algebra in the past. Hard work and good fun.