need to calculate intermediate values

[Chris Alexander]

I'm not well enough versed in spreadsheet use to calculate some
intermediate values I need. I would be greatful for anyone who can offer
a formula or suggestion.

I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are
beside it. The values in A & B change as they go from 1-30, they change
at different rates. I have some intermediary values at given points but
need to calculate what the values in between would be, based on the
fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as
best I can without including an attachment. Thanks.

(e-mail address removed)



1 1 1
2 2.5 3
3
4 6 7
5
6
7
8 11 15
9
10
11
12
13
14
15 27 37
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 65 95

 

[Frank Kabel]

Hi
not really sure HOW you want to calculate the other values. what is
yiour mathematical function?

 

[Chris Alexander]

Frank,

Thanks for your interest. I'm sure I didn't explain it well so I'll be
specific and see if that clears it up.

The values I want are times (in seconds) to calculate the Reciprocity
failure of film stocks.

The 1-30 represents exposure time in seconds as recommended by the light
meter. Two film types (A&B) each react differently when exposed to light
from between 1-30 seconds thereby needing extra exposure time. Some of the
values I have (at 4 seconds "A" requires 6 seconds and "B" needs 7
seconds) as shown in the chart I included. At 1 second both A&B need 1
second of exposure, at 30 seconds "A" needs 65 and "B" needs 95 seconds.
With this beginning and end points I want to establish the other times in
between, however it is not a straight line but rather a curve! I am hoping
that having established some of the other points in between will somehow
make it possible to calculate the other values on the curve.

Clear as mud? Thanks.

Chris

 

[Tushar Mehta]

Plot the data in a XY scatter chart so that you have 2 series, one for
the A values the other for the B values. The 1st column should be the
x-values.

There is nothing like knowing the nature of the relationship between
the parameters (power, exponential, polynomial, logarithmic, etc.)
However, if you don't, a little trial and error might be in order.

For each series add a trendline (Chart | Add Trendline...) Based on
the shape of the sparse data, I chose a Power series. Double-click
each trendline. From the options tab, check 'show equation' and 'show
r-squared'. Try different types of trendlines to see which makes most
sense to you (double-click the trendline then the Type tab).

I found both a power and a polynomial of order 2 to be excellent fits
with R-squared values greater than 99.7%

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions