Autofilling

[MrSak87]

Hey guys,


I have 3 columns. A and B contain data and they go to about row 40,000. Column C contains the formula


A B correlation coefficient
-5.86603E-05 -0.000100231 0.704674723
-1.05436E-05 -2.15795E-05 0.756371125
4.63519E-07 -1.60549E-05 0.802302214
-5.53306E-06 -2.32108E-05 0.841670011
-4.74393E-06 -2.16407E-05 0.873679808
-6.32212E-06 -2.21338E-05 0.897609191
2.38413E-06 -9.34249E-06 0.912849595
2.42768E-05 2.88144E-05 0.91889318
2.85189E-05 4.72981E-05 0.915369812
2.64054E-05 4.24488E-05 0.902173589
3.65818E-05 5.96418E-05 0.8793864
3.97801E-05 7.1664E-05 0.847304912
4.36574E-05 7.62719E-05 0.806428796


Column C contains the formula:


=CORREL(A4:A40576,B4:B40576)


I want this formula to fill down but in a way I'm not sure is possible on excel with the usual methods. Manually I can do this but obviously this isn't a viable solution. The manual method is (starting on row number 4):


Row 4: =CORREL($A$4:A40576,B4:$B$40576)
Row 5: =CORREL($A$4:A40575,B5:$B$40576)
Row 6: =CORREL($A$4:A40574,B6:$B$40576)
Row 7: =CORREL($A$4:A40573,B7:$B$40576)
Row 8: =CORREL($A$4:A40572,B8:$B$40576)


As you can see:
$A$4 is locked so stays the same - this is what I want
$B$40576 is locked so stays the same - this is what I want
B4 becomes B5,B6,B7,B8 - this is also what I want


The problem is A40576 becoming A40575, A40574, A40573, A40572. I want this number to become smaller each time like the example above. When I fill though it will always increase. Is there anyway around this!? There is absolutely noway to manually enter all this data!!


Thank you for any help
Sean

 

[Dendro]

Hi,

I know you can make number lower each time by right clicking and pulling down and adjust with step-1. With a cellreference this doesn't seem to work... maybe you can start looking there