Film Speed and Development Test (Excel spreadsheet supplied)

[RalphLambrecht]

I use Neo Office with my Mac, and the charts look identical, even though I too have some err messages.

Not sure if they are the same, but certainly not how the OO chart looks.

This one looks right.

 

[RalphLambrecht]

Ralph,
Would incident metering charts be too much to ask?

What is that?

 

[michaelbsc]

mikebarger,
For 4x5 sheet film, Use TP4x5-31. It is 31 Step Projection Scale 4" x 5".

OK, I admit that I haven't read the book. How would I go about exposing the film with this scale?

 

[RalphLambrecht]

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.

 

[squinonescolon]

Sorry. I meant, in BTZS and in one of your appendices you mentioned incident metering, I would love to try it. But we would need (I think) 2 more graphs:
sbr vs iso and sbr vs development.

 

[RalphLambrecht]

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.

 

[Ian Grant]

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.

Ian

 

[RalphLambrecht]

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

Ian

In the meantime, I will work on the instructions.

 

[Christopher Walrath]

It's all coming together... Excellent...

 

[Lee L]

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.

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.

Lee

 

[Lee L]

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.

For others wishing to try this at home I submit a hint from http://qa.openoffice.org/issues/show_bug.cgi?id=20819 for structuring a table for use in regressing higher order polynomials:

------- 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?)

 

[Lee L]

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.

Lee

 

[Lee L]

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.

Lee

 

[squinonescolon]

Fantastic!, you just save me $150.00
Thanks!!

 

[Ian Grant]

Thanks Lee, works fine now in LibreOffice and latest Open Office Beta.

Have fun & say hi to the APUGers your meeting up with.

Ian

 

[Christopher Walrath]

Very good and fast work, Lee. Looks good over here as well. Awesome.

 

[RalphLambrecht]

... 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. ...

Correct, the cells should read 'y=f(x)'. Just a typo.
While we're at it, cell T22 should read 'avgG' instead of 'N'. A copy/paste error.

... 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) ...

Correct again. I used 'y = c2*x^2 + c1*x + b.

Unfortunately, I cannot see what you did since I don't have the software, but many thanks for the effort!

 

[Lee L]

In order:

Steven, the thousands of folks who have contributed to Star Office/openoffice/LibreOffice over many years are the ones who saved you $150. Thank them, or send them a percentage donation of your choosing at thedocumentfoundation.org.

Glad it's working for the others who have reported in. Had a great get together this afternoon with some very nice folks, and viewing of some very fine original mid-late 19th century photos that one of our members has collected.

Ralph, you can, if you choose, get the Mac port of openoffice at NeoOffice.org. Free, and always will be.
Thanks for the additional info. I guess I could export the .ods version back to .xls, but I have no way to verify that the changes would carry over correctly, and I sort of have a personal policy of avoiding that sort of thing when openoffice is freely available to everyone under Mac, Windows, linux, et. al. From my perspective that's really about Microsoft trying to impose de facto standards that allow them to control the market rather than complying with fully public ISO standards that the open source office packages support.

Again the real thanks should go to Ralph for his work on this spreadsheet and for posting it for others to use freely.

Lee

 

[RalphLambrecht]

Lee

Thank you again. Now, it's my turn to create proper instructions. I need about a week to do this. It will be posted as pdf.

 

[squinonescolon]

In order:

Steven, the thousands of folks who have contributed to Star Office/openoffice/LibreOffice over many years are the ones who saved you $150. Thank them, or send them a percentage donation of your choosing at thedocumentfoundation.org..

Lee

Well said, and I will contribute.

 

[Louis Nargi]

This is great information. Thanks.

 

[eggen]

I'm using Linux Ubuntu release 10.04 (lucid), Kernel-Linux 2.6.32-27-generic, GNOME 2.30.2 and Open Office 3.2 version. Excel file works just fine.

Regards
Esko Hietaranta

 

[Lee L]

I'm using Linux Ubuntu release 10.04 (lucid), Kernel-Linux 2.6.32-27-generic, GNOME 2.30.2 and Open Office 3.2 version. Excel file works just fine.

Regards
Esko Hietaranta

So am I. Try checking the Summary page of the spreadsheet and see if you don't get Err:502 in cells column U, and to the right of c2, c1, and b in column T, or missing/misplaced curves in the two graphs to the left on that page.

Lee

 

[eggen]

So am I. Try checking the Summary page of the spreadsheet and see if you don't get Err:502 in cells column U, and to the right of c2, c1, and b in column T, or missing/misplaced curves in the two graphs to the left on that page.

Lee

Sorry, I was working on .ODS file not .xls file. My mistake. The .ODS works fine. I opened .xls file but it is read only mode even if I changed permissions. Strange! It looks same as .ODS file but I cannot input data. Any suggestions?

Regards
Esko

 

[Lee L]

Sorry, I was working on .ODS file not .xls file. My mistake. The .ODS works fine. I opened .xls file but it is read only mode even if I changed permissions. Strange! It looks same as .ODS file but I cannot input data. Any suggestions?

Regards
Esko

Don't bother with the .xls file, it won't work on openoffice as posted because of differences in syntax. The .ods file only contains modifications to correct for the syntax differences between openoffice and excel. If you want to look at the .xls file contents, you could probably just re-save it under a new name and then load, look at, and modify it. If it's really an OS file permissions issue (not just openoffice preventing you from losing data by editing something in .xls that openoffice doesn't deal with correctly), then just right click on the file in the file browser (Nautilus by default) and then click on Properties, then the Permissions tab in the window that opens and give it the read/write/execute permissions you wish it to have. You may need to enter your root password to change permissions.

Lee