Hello everybody,
In this posting, I will describe how I implemented the new high-power screen gain calculator. I want to go into some technical detail here so that others can double-check my work.
I decided to protect and hide the formulas used in the spreadsheet simply to make it simpler for people to use. The spreadsheet does not use a password. To get access to the formulas, simply do the following:
- Click on Review and then on Unprotect Sheet
- Select rows 30-64 and right-click; select Unhide from the pop-up menu.
The vector calculation math is unchanged from the original all-screen gain calculator.
To better model the change in gain by angle for the high-power screen material, I decided to piece-wise curve fit the measured data. For the following discussion, see the High-Power curve fitting spreadsheet attached to this posting.
To accurately read the data from the Da-Lite graph, I saved a copy of the graph to my PC and opened it in Photoshop. I then turned on the ruler tool and set it up for millimeters. With this tool, I was able to read off the location of each sample point in the Y (gain) dimension. By measuring the location of the origin of the graph, I was able to easily compute how many millimeters in the Y (gain) dimension each data point is. Finally, I derived the gain/mm by measuring the full range of the graph (3.0 gain). Because the data points were measured at even multiples of 5 degrees (e.g., 5, 10, 15 degrees), the X axis values were trivial to determine.
You can see the measurements I took at the top of the spreadsheet.
As a double check of my math, I plotted these points in Excel. This is the first plot in the spreadsheet. You can easily see that the shape of the lines corresponds to the original plot.
I then analyzed each material (2.4 gain and 2.8 gain) separately. By using Excels curve fitting tool, I was able to curve fit polynomials to sections of the measured graph. In the case of the 2.4 gain material, I fit three segments, and in the case of the 2.8 gain material, I only needed to fit two segments. This is shown in the next two graphs.
In order to make sure that the curves are well behaved at the ends of each segment, I fit additional points on both sides, and extended the fit lines so that they can be visually inspected. For example, in the high-power screen gain calculator, I use one curve for the range 0-10 degrees for the 2.4 gain material. In order to make sure that the curve is well behaved at 0 and 10 degrees, I fit the curve from 0-15 degrees, and plotted this fit curve from -5 degrees to 20 degrees. You can see that the R^2 for 0-10 degrees is 1.0 (this is not a surprise, as you can fit any four points perfectly to third degree polynomial.) What is important is that the shape of the red curve looks right beyond the region for which it is being used (0-10 degrees).
For the 2.4 gain material, I used three polynomials (shown on the graph), the first for the range 0 <= angle <= 10 degrees; the second for the range 10 < angle <= 20 degrees; and the third is for the range 20 < angle. I read the coefficients for these polynomials directly from the trend line labels on the Excel graph for 2.4 gain.
For the 2.8 gain material, I used the same process, except that I only needed two curves (0-20 degrees and 20-55 degrees) to fit the entire data set.
The rest of the spread sheet should be pretty self explanatory. If anybody spots any bugs in this approach, or can offer any suggestions for improving it, Im happy to make modifications to it.
--Mark
High-Power curve fitting.zip 12.4462890625k . file