View Full Version : Weird Excel autofill issue

29-06-2009, 02:41 PM

I have a set of data in columns, and I'm trying to create a new column to the right that, for each row, averages across the columns.

The data are numbers, and are formatted as General. I've also tried converting them to Number format too. The data were imported from a .dat file using the Tools | Import External Data | Import Data function.

The issue is this...

In a blank column to the right, in the first row, I type the formula: =average(A1:K1)
This obviously gives me the average as expected. Now I want to simply use autofill and click and drag the "little box in the right hand corner of the cell" down the column to the bottom.

However, when I do this, it just copies the value of the first cell to all the others - without "reapplying" the formula. What is interesting however is that Excel does update the cell references in the formula as it autofills, but doesn't recalculate the answer. So if I go into a particular cell (i.e. double click it) and then press Enter, it will recalculate it and give me the proper result for that particular row.

Also, the little icon/popup thing that comes up after autofilling only gives me the option to autofill values - not series. Which is weird.

I was thinking this is a cell formatting issue, but I've tried a few formats and it doesn't work. Normally a function like average() should autofill right? Also, autofill isn't working with other things - like trying to autofill something like this down a column: =VALUE('2 judge(S170).dat'!M2)

I can't find any options or anything like that for autofill. Any ideas guys? :)

Thanks in advance.

kahawai chaser
29-06-2009, 06:20 PM
Maybe try renaming a copy of the file .dat extension to xls and work with that...

29-06-2009, 06:42 PM
Sounds like calculate automatically has been turned off. What happens when you fit F9 after the autofill?