First things first: Thanks for posting this Ralph. I'm sure a number of people will find it very useful, as will I after figuring out the syntax error.

As for the results opening an excel spreadsheet in openoffice, it's an equation syntax problem.
----------------------------------------------------------------------------------------
Error: 502
Invalid argument
Function argument is not valid, for example, a negative number for the root function.
----------------------------------------------------------------------------------------

...

Lee

Lee

Please note:

1. The top left graph is using a 2nd order polynominal to give a trend line.
2. The other graphs do not use trend lines, just 'smooth lines' through a few data points.
3. The bottom left graph does not use a trend line either but a 'smooth line' through the data points calculated in T43:T49. For this the coefficients for a 2nd order polynominal are calculated in T35:T37 for the data in M33:O37. I suspect that calculating these coefficients might be a hurdle for some software packages.

Last edited by RalphLambrecht; 01-04-2011 at 03:27 PM. Click to view previous post history.

I did not realize how many different versions of knock-off Offices were out there. I am also angered by the .xls vs .xlsx trouble, and that's how far I go with supplying different versions. However, if someone can supply working copies for other software packages, I will work with them to make them compatible and will post them alongside mine.

OpenOffice, LibreOffice and NeoOffice (Mac OS) are essentially the same, all use the same core and also file formats, and all stem from Star Office the individual component programs of which were around long before Microsoft Office and Windows

The original Star Office suite was DOS based and pre-dates Microsoft Office, so which is the Knock off

More seriously it's a case of making these types of files as compatible as possible, and in recent years I've found few issues, except with complex Spreadsheets.

The problem is that while International committees agree file standards Microsoft always tries to impose it's own variations on everyone, or rather their customers to ensure they have to use their products.

So hopefully Lee will fix this Spreadsheet for those of us not using Microsoft Office.

1. The top left graph is using a 2nd order polynominal to give a trend line.
2. The other graphs do not use trend lines, just 'smooth lines' through a few data points.
3. The bottom left graph does not use a trend line either but a 'smooth line' through the data points calculated in T43:T49. For this the coefficients for a 2nd order polynominal are calculated in T35:T37 for the data in M33:O37. I suspect that calculating these coefficients might be a hurdle for some software packages.

Thanks very much Ralph. I've noted this and will use it next time I get a shot at the spreadsheet. openoffice (and variations) can all easily do polynomials to as high an order as you want to go, but the openoffice LINEST function won't infer higher orders than second. You have to calculate the needed x^n values and then run the regression on the full matrix of x^n values. I know how to do that. I can also do whatever is necessary to regress against other equations, but not always in spreadsheet, or without a lot of calculation of intermediate values. I often use SciDAVis to explore for the best equations to regress against. Found a great fit for the Zone N-development vs. development time data with an exponential growth function with that. What I was trying to work out in the spreadsheet was your use of the INDEX function, which I haven't used before, but exists in openoffice as well. If I circumvent that I can easily regress and find the needed values from which to generate intermediate values and draw the graphs. That's probably the way I'll go initially, and leave the INDEX function out of the mix. I look forward to seeing your documentation.

NeoOffice works on openoffice for the Mac, Red Hat has long made their own tweaks for openoffice, GoOpenOffice has been used by many linux distributions, including Ubuntu as an improvement over what Sun/Oracle has supplied. With The Document Foundation now formed, and LibreOffice no longer controlled by Sun/Oracle, all of these folks are contributing their code (with proper credit and appreciation) to an independent central location that will be much more responsive and open. So I expect we'll see much more rapid and flexible development.

I often open spreadsheets in openoffice/LibreOffice from all versions of MS Excel and Word and port them back to another MS version for family and a few friends who only have MS Office and can't read among other versions. But as Ian says, once spreadsheets or documents start to get complex, compatibility can fail in spots.

The formats used by openoffice are human readable XML documents that are zipped with standard compression techniques on saving. If you use a standard unzip program on them, you can read and make sense of the contents and the markup syntax with anything that will read a flat ASCII file. I've also found them to be around 10% of the size of the same content saved in MS Office formats. Openoffice formats are the only fully disclosed office document formats approved by the ISO. Microsoft managed to wangle OOXML document format approval from the ISO after heavily stacking the board with small nations they paid to attend, but they won't disclose the entire standard, and what they did publish is something like 6000 pages long (if I recall correctly). So you can't really duplicate this standard even if you read the published 'standard'. (It's not much of a cheese shop, is it?) Microsoft also claims to support openoffice ODF formats, but they intentionally set Excel to import cells from ODF with many calculated values (cells with function results) read in as fixed values. Then they turn around and claim that openoffice isn't compatible with Excel.

OK, I think some things just clicked into place for me after reading up on Excel's LINEST syntax and Ralph's notes on what he's doing with regressions. So I have a couple of questions for Ralph just to clarify my thinking.

First, I think the notation in cells T13 and T33 of the summary page threw me. Is there a typo in those cells with a minus sign instead of an intended equals sign, '-' rather than '='? That would make sense from the equations below, finding y as a function of x in column T and x as a function of y in column U. I was trying to make sense of subtracting f(x) from y.

Second, taking the following second order polynomial notation:

f(x)= a + b*x + c*x^2

column S notation in the summary page is such that the following correspondences to the equation above are correct:

b = a
c1 = b = coefficient of x)
c2 = c = (coefficient of x^2)

Thanks in advance for the help Ralph. Knowing this will get us there with an openoffice version, but I don't promise to meet any deadlines as I have way too many irons in the fire right now, and will be a single parent for most of the next month.

------- Additional comments from jumbo444 Wed Nov 26 18:09:00 +0000 2008 -------

Hello,

Gathering information about polynomial trend line, I noticed that (again)
solution is in front our eyes.
An easy way to get polynomial trend line equation is given in Help of trend line
dialog box : simply use LINEST function, with proper construction of X data.
From Help :
The polynomial regression equation
A polynomial regression curve cannot be added automatically. You must calculate
this curve manually.
Create a table with the columns x, x², x³, … , xⁿ, y up to the desired degree n.
Use the formula =LINEST(Data_Y,Data_X) with the complete range x to xⁿ (without
headings) as Data_X.
The first row of the LINEST output contains the coefficients of the regression
polynomial, with the coefficient of xⁿ at the leftmost position.

Lee

(BTW, Excel before 2003 used a deficient method for LINEST regressions, and returned inaccurate results. Perhaps one reason that openoffice preferred another syntax?)

Last edited by Lee L; 01-05-2011 at 12:42 AM. Click to view previous post history.

OK, I've got it done. The important parts to know are that Excel will take only X and Y values and then internally calculate any higher order polynomial values (x^2, x^3, x^4... x^n) without them being explicityly stated in the formula. You tell Excel what order of polynomial you want. You could even try to run a polynomial regression against too small a data set given the LINEST function syntax, but I don't have Excel and can't test for what kind of error it would throw.

openoffice calc on the other hand requires a full set of explicit numbers for x^2, x^3, x^4... x^n, so you have to calculate or state those values in the spreadsheet and include those cells in the LINEST, and then LINEST regresses against the correct order of polynomial for the number of variables in the dataset. For this reason I've added x^2 and y^2 columns in the Summary sheet for the 'time' and 'adjust' columns. I've also run the full set of regression statistics out in columns Y through AE, then pulled the needed values for the coefficients and axis intercepts (the lines Ralph has labeled c2, c1, and b) from those statistical tables.

I also set up the graphs to work 'properly' with openoffice calc.

One other warning for those working with this stuff at home: in order for the regression routine to recognize the full array properly for a higher order polynomial regression with LINEST, you must press Control+Shift+Enter when finishing off the LINEST formula cell in both Excel and Calc.

I'll attach the spreadsheet, but .odf may not be supported by the forum software at APUG. It hasn't been in the past. I'll try it, and if it fails to work, I'll just change the file name from *.ods to *.zip and re-upload, then you should be able to download it, change the name from *.zip to *.ods and then open it in openoffice, neooffice, libreoffice, etc. I may run out of time to try all the necessary submissions of the spreadsheet, but I should be back to work that out within a day or so if the first tries fail.

The .odf file seems to have 'taken' successfully with the forum software. Let me know if you have a problem and I need to resubmit. I haven't looked over anything but the Summary sheet carefully. No warranty, support and suggestions only as my limited time allows, all the usual disclaimers, etc. I'm off to meet some local APUGgers for lunch and print viewing.

And thanks and full credit to Ralph for doing the actual heavy lifting and supplying it for free.