slope #N/A in one but not other

[beng986]

I tried using the SLOPE function but got #N/A as my first 4 values in column A equal #N/A. In column B all the values are numbers. So i used

=SLOPE(IF(ISNUMBER(Monthly!$A2:$A37),Monthly!$A2:$A37),IF(ISNUMBER(Monthly!B2:B3),Monthly!B2:B3))

however i am now getting #Value !

the dataset is below:


[TABLE="width: 145"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]2.30242[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]-5.9716[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]-2.87481[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: right"]4.456949[/TD]
[/TR]
[TR]
[TD="align: right"]13.60761[/TD]
[TD][/TD]
[TD="align: right"]0.216091[/TD]
[/TR]
[TR]
[TD="align: right"]-6.96379[/TD]
[TD][/TD]
[TD="align: right"]-1.37079[/TD]
[/TR]
[TR]
[TD="align: right"]-3.83234[/TD]
[TD][/TD]
[TD="align: right"]-3.14611[/TD]
[/TR]
[TR]
[TD="align: right"]-5.66625[/TD]
[TD][/TD]
[TD="align: right"]0.297976[/TD]
[/TR]
[TR]
[TD="align: right"]13.20132[/TD]
[TD][/TD]
[TD="align: right"]1.474965[/TD]
[/TR]
[TR]
[TD="align: right"]3.950608[/TD]
[TD][/TD]
[TD="align: right"]0.780809[/TD]
[/TR]
[TR]
[TD="align: right"]3.293586[/TD]
[TD][/TD]
[TD="align: right"]0.235856[/TD]
[/TR]
[TR]
[TD="align: right"]-5.27762[/TD]
[TD][/TD]
[TD="align: right"]2.498126[/TD]
[/TR]
[TR]
[TD="align: right"]10.50494[/TD]
[TD][/TD]
[TD="align: right"]3.936338[/TD]
[/TR]
[TR]
[TD="align: right"]16.17646[/TD]
[TD][/TD]
[TD="align: right"]1.186693[/TD]
[/TR]
[TR]
[TD="align: right"]4.475582[/TD]
[TD][/TD]
[TD="align: right"]1.572824[/TD]
[/TR]
[TR]
[TD="align: right"]-4.05762[/TD]
[TD][/TD]
[TD="align: right"]0.340843[/TD]
[/TR]
[TR]
[TD="align: right"]-1.64835[/TD]
[TD][/TD]
[TD="align: right"]-2.01001[/TD]
[/TR]
[TR]
[TD="align: right"]14.99069[/TD]
[TD][/TD]
[TD="align: right"]4.897094[/TD]
[/TR]
[TR]
[TD="align: right"]-9.06882[/TD]
[TD][/TD]
[TD="align: right"]-0.38624[/TD]
[/TR]
[TR]
[TD="align: right"]4.407835[/TD]
[TD][/TD]
[TD="align: right"]2.466393[/TD]
[/TR]
[TR]
[TD="align: right"]15.5224[/TD]
[TD][/TD]
[TD="align: right"]0.925279[/TD]
[/TR]
[TR]
[TD="align: right"]11.59099[/TD]
[TD][/TD]
[TD="align: right"]-0.68948[/TD]
[/TR]
[TR]
[TD="align: right"]5.963445[/TD]
[TD][/TD]
[TD="align: right"]3.876102[/TD]
[/TR]
[TR]
[TD="align: right"]0.063753[/TD]
[TD][/TD]
[TD="align: right"]-2.7672[/TD]
[/TR]
[TR]
[TD="align: right"]-2.23001[/TD]
[TD][/TD]
[TD="align: right"]1.099896[/TD]
[/TR]
[TR]
[TD="align: right"]3.681982[/TD]
[TD][/TD]
[TD="align: right"]0.661862[/TD]
[/TR]
[TR]
[TD="align: right"]-4.39975[/TD]
[TD][/TD]
[TD="align: right"]-0.56691[/TD]
[/TR]
[TR]
[TD="align: right"]4.980183[/TD]
[TD][/TD]
[TD="align: right"]1.674116[/TD]
[/TR]
[TR]
[TD="align: right"]-0.38083[/TD]
[TD][/TD]
[TD="align: right"]-2.03633[/TD]
[/TR]
[TR]
[TD="align: right"]1.943302[/TD]
[TD][/TD]
[TD="align: right"]4.66007[/TD]
[/TR]
[TR]
[TD="align: right"]-8.06251[/TD]
[TD][/TD]
[TD="align: right"]-1.99345[/TD]
[/TR]
[TR]
[TD="align: right"]6.254244[/TD]
[TD][/TD]
[TD="align: right"]-3.77145[/TD]
[/TR]
[TR]
[TD="align: right"]-16.3148[/TD]
[TD][/TD]
[TD="align: right"]-2.1961[/TD]
[/TR]
[TR]
[TD="align: right"]5.504584[/TD]
[TD][/TD]
[TD="align: right"]5.996406[/TD]
[/TR]
[TR]
[TD="align: right"]-4.17035[/TD]
[TD][/TD]
[TD="align: right"]2.289903[/TD]
[/TR]
[TR]
[TD="align: right"]-0.70204[/TD]
[TD][/TD]
[TD="align: right"]-0.47251[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

This any better?

Excel 2010
	A	B	C	D	E
1
2	#N/A	2.30242			1.380907
3	#N/A	-5.9716
4	#N/A	-2.87481			1.380907
5	#N/A	4.456949
6	13.60761	0.216091
7	-6.96379	-1.37079
8	-3.83234	-3.14611
9	-5.66625	0.297976
10	13.20132	1.474965
11	3.950608	0.780809
12	3.293586	0.235856
13	-5.27762	2.498126
14	10.50494	3.936338
15	16.17646	1.186693
16	4.475582	1.572824
17	-4.05762	0.340843
18	-1.64835	-2.01001
19	14.99069	4.897094
20	-9.06882	-0.38624
21	4.407835	2.466393
22	15.5224	0.925279
23	11.59099	-0.68948
24	5.963445	3.876102
25	0.063753	-2.7672
26	-2.23001	1.099896
27	3.681982	0.661862
28	-4.39975	-0.56691
29	4.980183	1.674116
30	-0.38083	-2.03633
31	1.943302	4.66007
32	-8.06251	-1.99345
33	6.254244	-3.77145
34	-16.3148	-2.1961
35	5.504584	5.996406
36	-4.17035	2.289903
37	-0.70204	-0.47251
Monthly

Cell Formulas
Range	Formula
E4	=SLOPE($A$6:$A$37,$B$6:$B$37)
E2	{=SLOPE(IF(ISNUMBER($A$2:$A$37),$A$2:$A$37),IF(ISNUMBER($A$2:$A$37),$B$2:$B$37))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[beng986]

Thanks. forgot the ctrk shift enter