Dynamic charts using defined names, and trendlines in charts

[richbl]

Hello all,

I've been using OOO for about a year, and for about 90% of my work, all the suite applications fit the bill rather nicely.

Every once in a while, though, I come across an MS Office document that I cannot seem to convert properly.

Most recently, I have an Excel workbook with three worksheets. I use this workbook to track blood pressure data, and chart that data accordingly.

So far, there are two issues that I've run into with this workbook:

A) In a line chart, is there a way to generate a trendline that is the functional equivalent to Excel? My research suggests that this feature does not yet exist in OOO Calc.
B) In Excel, I'm able to generate a chart that tracks a rolling past 6-month dataset using defined names. In OOO Calc, I cannot seem to find a way to do this. Is this possible?

For reference, I've place the original XLS file, called blood_pressure.xls up on www.businesslearninginc.com/files/blood_pressure.xls.

In Excel, you'll notice trendlines in any of the three charts. On the worksheet entitled "BP (Last 6 months)," you'll see the 6-month rolling average of blood pressure data. In OOO Calc, neither of these aspects are functional.

Please advise.

Thanks much.

Rich

[RickRandom]

First, to get a trendline, you must have an X-Y chart (aka a scatter chart), and not a line chart, so you need to change the chart type. (With a line chart, the items along the horizontal axis are just labels and can be anything, words, numbers, etc., whereas with an XY chart, they must be numbers so they can be plotted correctly.)

Second, I often find editing charts in Calc a real pain, and I often re-create one rather than try to edit one that's no good.

There seem to be various ways to put a trendline in, such as right-click the chart, Edit, then click the series (if you get the chart wall, keep trying!) then when the series is selected, right-click on it, Object Properties, Statistics tab, then you can add a trendline.

You can't display the equation - sorry! Use the spreadsheet functions SLOPE( ) and INTERCEPT( ) if you just want a straight line equation in the spreadsheet, not on the chart.

At a quick glance your moving averages at least gave answers. Did you mean Calc wouldn't do them at all, or it did them wrong?

[richbl]

Thanks RickRandom for the response.

I'm aware of using an X-Y chart with trendlines. Unfortunately, an X-Y chart really won't work for my needs.

As for your question regarding moving averages, the calculations work fine in Calc, the problem is that the chart that uses moving averages does not seem to work.

To confirm this, go to the worksheet entitled "BP _Last 6 months" and you should see a chart of data taken from the most recent 6-month period. In OOO Calc, the chart is empty. So, for some reason, the data didn't carry from MS Excel into OOO Calc. I'm guessing that, since this chart makes use of defines names, that OOO Calc doesn't know how to use defined names in charts.

I hope this clarifies things a bit more.

thanks

[Villeroy]

Yes, charts and some other items can not deal with calculated range names like your

[richbl]

[Villeroy]

Open a new spreadsheet.
Hit F4, pick the table of the "Bibliography" datasource from the left pane of the datasource browser and drag it onto any cell.
Call Data>Select... and notice the named database range "Import1".
Add some records to the bibliography and call Data>Refresh
Remove some records from the bibliography and call Data>Refresh
A chart, bound to "Import1" should resize with the imported data.

Edit:
My currently openened sheet has a formula that helps me to select the data of a named range "criteria" below header in order to clear it's contents with a click and a key stroke.
=HYPERLINK(ADDRESS(ROW(criteria)+1;COLUMN(criteria);1;"#")&":"&ADDRESS(ROW(criteria)+ROWS(criteria)-1;COLUMN(criteria)+COLUMNS(criteria)-1))
You can copy the resulting address-string and paste it into the data range dialog of a chart. This makes it slightly easier to adjust the chart range manually.
Another way to handle growing/shrinking lists is insertion of new rows for each new input. I use custom shortcuts Ctrl+Ins and Ctrl+Del to insert and remove rows. All formula references and charts will (should?) adjust in this case.

[robina]

Charts and trendlines... try using the new chart module, available in "developer" OOo versions since last May.
http://download.openoffice.org/680/index.html?intcmp=1235

[Villeroy]

Just tried with import ranges. I can't get it to work. A macro can do the trick. The new chart module may be able do the trick.

[richbl]