How can I solve this. I have two simple columns of data in excel.
(10 rows) These represent 10 data points. The left column is called
INDEX and the right column is called DATAPOINT.
For example, index 1 is .5, index 2 is .4, 3 is .37, 4 is .34, etc.
all the way to index 10 which has a value of .20 int he 2nd column.
1 .5
2 .4
3 .37
4 .34
....
10 .20
I now have another datapoint which is at index 20, say, a value of .1
So now I'm missing the datapoints between 11 and 19. I was hoping to
use the built in trend values you can gather just by highlighting my
string of values and dragging it down in excel, to reveal what excel
thinks is the trend in the values between 11 & 19. However, it does
not map properly to the 20th index.
Is there any way I can incorporate the value I have found for the 20th
index, somehow tie it to the other values I have for index 1-10, to
map out the proper datapoint trends between them, thereby achieving
reasonable values for index 11 to 19 ?

On Jul 31, 3:22 pm, wrote:
Is there any way I can incorporate the value I have found
for the 20th index, somehow tie it to the other values I have
for index 1-10, to map out the proper datapoint trends
between them, thereby achieving reasonable values for
index 11 to 19 ?

"Reasonable values"? Ah, there's the rub. What's reasonable? How do
you know?

TREND() assumes a straight line approximates the data. Moreover, it
finds a line that "best fits" the current data; by that I mean: it
tries to minimize the average deviation between existing real data
points and corresponding points on the trend line. For that reason,
it is very possible that the trend line does not pass through some/
many real data points.

I suggest that you start by graphing your data using the Chart tool.
Play with different trend lines (linear, exponential, etc) to see
which best fits.

After that, the next question you probably will want to ask is: what
Excel function(s) can you use to compute the trend line data points in
a spreadsheet? But first, find out which trend line you think is most
"reasonable". You might discover that it is a linear trend after all.

(I have not looked at your data at all.)


--- original posting ---

On Jul 31, 3:22 pm, wrote:
How can I solve this. I have two simple columns of data in excel.
(10 rows) These represent 10 data points. The left column is called
INDEX and the right column is called DATAPOINT.
For example, index 1 is .5, index 2 is .4, 3 is .37, 4 is .34, etc.
all the way to index 10 which has a value of .20 int he 2nd column.
1 .5
2 .4
3 .37
4 .34
...
10 .20
I now have another datapoint which is at index 20, say, a value of .1
So now I'm missing the datapoints between 11 and 19. I was hoping to
use the built in trend values you can gather just by highlighting my
string of values and dragging it down in excel, to reveal what excel
thinks is the trend in the values between 11 & 19. However, it does
not map properly to the 20th index.
Is there any way I can incorporate the value I have found for the 20th
index, somehow tie it to the other values I have for index 1-10, to
map out the proper datapoint trends between them, thereby achieving
reasonable values for index 11 to 19 ?

joeu2004, I appreciate your reply! I like your suggestions, I guess
it made me re-think my question.

I suppose ultimately this is what I need to know:

If I have two datapoints, what formula/method can I use to get the
approximate linear values between them given a certain range of
cells. For example, if I have "100" in cell A1 and 1000 in cell A10,
but cells A3 to A9 are empty, what dragging/mouse clicks/formulas can
I use to "autofill" the empty 8 cells in between them?

In this case, the numbers being so straightforward, it's easy to say
A2 would have 200, A3 would have 300, etc. But if the numbers are
more complex, what could I do to get those values filled in assuming
it's linear? Meaning, I know my two end points, but don't know what's
in between-- I want a linear set of values calculated between them
based on the beginning and end values. Maybe this is a simple task in
excel?

On Aug 1, 10:15 am, wrote:
If I have two datapoints, what formula/method can I use to get the
approximate linear values between them given a certain range of
cells. For example, if I have "100" in cell A1 and 1000 in cell A10,
but cells A3 to A9 are empty, what dragging/mouse clicks/formulas can
I use to "autofill" the empty 8 cells in between them?

On way:

A2: =A1 + ($A$10-$A$1)/(row($A$10)-row($A$1))

Copy-and-paste or drag A2 down through A9.

Caveat: In your original posting, you have a column of indexes. The
formula above assumes that the corresponding indexes increase
linearly, e.g. 1, 2,..., 10 -- as they did in your original posting.