Trend data points

What I have is a column (column A) that only has two data entries at any one time.
I need column B to fill in the trend of those two data points.
My problem is I don't know how to deal with the cells that say #N/A. Which is every cell other than the two cells with data.
If I replace the #N/As with say 1, then it messes up the trend data points.
It seems like I need to be using the trend command, but any solution would be welcome.
Thanks.

A B
1 #N/A 12
2 #N/A 11.5
3 #N/A 11
4 #N/A 10.5
5 #N/A 10
6 #N/A 9.5
7 9 9
8 #N/A 8.5
9 #N/A 8
10 #N/A 7.5
11 #N/A 7
12 #N/A 6.5
13 #N/A 6
14 #N/A 5.5
15 #N/A 5
16 4.5 4.5
17 #N/A 4
18 #N/A 3.5
19 #N/A 3
20 #N/A 2.5
21 #N/A 2
22 #N/A 1.5
23 #N/A 1
24 #N/A 0.5
25 #N/A 0
26 #N/A -0.5
27 #N/A -1
28 #N/A -1.5
29 #N/A -2

I know that sounds clear to you, but we need you to upload a sample book showing some sample data and sample results you expect to see. Highlight the cells that are problematic, much clearer than trying to describe it long form.

Just a suggestion,

Try this,

IF(ISNA(Insert your formula here),"",Insert your formula here)

this formula will return a blank cell instead of #N/A

HTH

Now that I've read up some on TREND in Excel (have you?), it's clear you need at least two sets of clear data for the comparison. You don't have that in this example, you have 1.

http://support.microsoft.com/kb/828801

D1 and copy to E1: =INDEX(A$1:A$29, MATCH(TRUE, NOT(ISNA($A$1:$A$29)), 0) )

D2 and copy to E2: =INDEX(A$1:A$29, MATCH(2, 1 / NOT(ISNA($A$1:$A$29)), 1) )

F2 and copy down: =TREND($D$1:$D$2, $E$1:$E$2, B1)

However, it's a pretty boring trend, since y-x.

Sorry, should have pointed out that the first two are array formulas -- they MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

This has been a roundabout path ...

shg

You're right, it has been round about, but getting closer.

I worked with your suggestion, it's close, but not quite right.
I was able to reproduce your results when copying exactly.
In fact, I can change the data (when using the same two cells) in column A and it gives the correct results.
When I add a row to the top of the list, it still works.

It doesn't work if I fill in one of the data entries with #N/A and replace the #N/A in another cell with data.

for example:
If I change the two data entries (from the attatchment you gave me to try) from A15 and A20 to A1 and A16, then it doesn't calculate correctly.
This is the type of situation that occurs in this application. A1 being a new data entry meeting my criteria, A15 being moved down to A16 with the addition of new data in A1, and A21(formally A20) simply showing #N/A since only the two newest data entries are shown at any one time.

Oops.

A3: =MATCH(TRUE, ISNUMBER($A$1:$A$29), 0)

A4: =MATCH(2, 1 / ISNUMBER($A$1:$A$29), 1)

Both are still array formulas.

Post the workbook.

Here's my attempt at the workbook. The cell in question is D3.

The formula in C3:D3 should be a SINGLE array formula entered across the two cells, not the same array formula entered in each separately.