I’m curious how one could solve the following physics problem with LibreOffice Calc.
In a class room a lighting meter was used to measure the illuminance E at the distance r. The data is as follows
E (lx), r (m)
663, 1
158, 2
71, 3
42, 4
19, 6
11, 8
What was the distance when the illuminance was 350 lx and what was the illuminance when the distance was 3,4 m?
This isn’t consistent.
Interpolation needs to be specified concerning the type or means. Sometimes linear interpolation is implicit, but there also are different types or means (spline interpolation e.g.)
As a physics problem the task can either be to find a law or an appropriate approximation or “estimation” function - or to apply a known physical law.
In the given case the “apply the law” situation is realistic. The law here is that the illuminance is inversely proportional to the square of the distance of the illuminated surface from the light source. Based on this knowledge (or reasonable assumption) you can now ask if the measured values comply, or what reasons may have caused an aberration. To create a formula in Calc to the effect of E(r) = E(r0) / (r/r0)^-2
E(r) = E(r0) * (r/r0)^-2 or E(r) = E(r0) / (r/r0)^2 (sorry!) is simple - and it’s no interpolation. A reverse resolution of the formula also is simple seen as a math task.
If you don’t know or suppose a law, you may start with a diagram to get ideas. Interpolation (in specific linear interpolation) should not occur as one of the first or relevant steps.
See attached.
disask85600illuminance.ods (26.0 KB)
Create an x-y graph based on the data (x values = r values, y values = E(lx) values), and insert a regression line with diplayed eqution and its coefficients. Try more than one type of the regression lines: linear, polynomial, exponetial, etc… Then you will able to calculate the lx value for any r values.
Another way without Graph: there are array functions LINEST() and LOGEST(). They are for the regession calculations in the cells. See the Help.