How the #@^%$&#^% do I copy a formula from one cell to the next?

I've got ten values I want to average, and have the average show in an 11th
column.

No problem, I can do that.

However, I've got 20 rows of these ten columns.

In the past, I've simply highlighted the cell (let's say K1) in which the
formula resides, which would read: =AVERAGE(A1:J1).

Then dragged the little black square on the bottom of the cell selection
down to fill in the cells K2 through K whatever.

It would copy the formula into each K cell, so it would say:

=AVERAGE(A2:J2) for row 2, =AVERAGE(A3:J3) for row 3, and so on. The result
of the average would be correct.

It's not letting me do that anymore. Now it SAYS that the formula in cell K2
IS for row 2, or K3 is row 3, and so on, which looks correct. But it's not
returning the correct average for those cells. It's displaying the solution
from the original returned average. It's as if it's simply copying the
answer to the other cells.

I can input an average manually on row 2 and get the correct result, so I
know something's up.

I've got to do this like 100 times and I really don't feel like entering
every single one of them manually. I should be able to copy or fill them in
some way.

I've tried "paste special" and selected formulas only (and every other
option actually), and that still doesn't work. I'm thinking it must be some
option setting on the way it fills series or something, but I can't find it.

Help please. I'd hate to have to get out paper and pencil!


TIA
Steve

f9 or set calculation to automatic?

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Steve Latham" <(E-Mail Removed)> wrote in message
news:Suggh.7985$HX4.6538@trnddc03...
> How the #@^%$&#^% do I copy a formula from one cell to the next?
>
> I've got ten values I want to average, and have the average show in an
> 11th column.
>
> No problem, I can do that.
>
> However, I've got 20 rows of these ten columns.
>
> In the past, I've simply highlighted the cell (let's say K1) in which the
> formula resides, which would read: =AVERAGE(A1:J1).
>
> Then dragged the little black square on the bottom of the cell selection
> down to fill in the cells K2 through K whatever.
>
> It would copy the formula into each K cell, so it would say:
>
> =AVERAGE(A2:J2) for row 2, =AVERAGE(A3:J3) for row 3, and so on. The
> result of the average would be correct.
>
> It's not letting me do that anymore. Now it SAYS that the formula in cell
> K2 IS for row 2, or K3 is row 3, and so on, which looks correct. But it's
> not returning the correct average for those cells. It's displaying the
> solution from the original returned average. It's as if it's simply
> copying the answer to the other cells.
>
> I can input an average manually on row 2 and get the correct result, so I
> know something's up.
>
> I've got to do this like 100 times and I really don't feel like entering
> every single one of them manually. I should be able to copy or fill them
> in some way.
>
> I've tried "paste special" and selected formulas only (and every other
> option actually), and that still doesn't work. I'm thinking it must be
> some option setting on the way it fills series or something, but I can't
> find it.
>
> Help please. I'd hate to have to get out paper and pencil!
>
>
> TIA
> Steve
>

"Don Guillett" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> f9 or set calculation to automatic?
>


Hey, it worked!

I F9'd the selected portion after dragging and it magically swapped all of
them. Don't know why though :-)

I'll check into the set calculation to automatic and see if that's a
preference I can select/deselect so it stays the way I like it.

Thanks Don!!

Steve

Steve

Tools>Options>Calculation can be Auto or Manual.

Excel takes the Calculation mode each session from the settings on the first
workbook opened in that session.

i.e. If you saved Book1 with calc mode in manual and opened it first, calc
mode would be in Manual.

If you saved Book2 with calc mode in auto and opened it after Book1, Book2
would be in manual mode(Excel ignores the auto calc mode in this case).

If you close Book1 before opening Book2, Book2 will be in auto calc mode.

Confusing enough? <g>


Gord Dibben MS Excel MVP


On Thu, 14 Dec 2006 18:53:38 GMT, "Steve Latham" <(E-Mail Removed)> wrote:

>
>"Don Guillett" <(E-Mail Removed)> wrote in message
>news:%(E-Mail Removed)...
>> f9 or set calculation to automatic?
>>
>
>
>Hey, it worked!
>
>I F9'd the selected portion after dragging and it magically swapped all of
>them. Don't know why though :-)
>
>I'll check into the set calculation to automatic and see if that's a
>preference I can select/deselect so it stays the way I like it.
>
>Thanks Don!!
>
>Steve
>

Gord Dibben MS Excel MVP

Thanks for the explanation.

I upgraded to 2003 at some point, and this must be new (of course it's
actually old since 2007 is out, but it's new to me) and my previous version
did it "the makes sense way" :-)

Preesh.

Steve

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> Steve
>
> Tools>Options>Calculation can be Auto or Manual.
>
> Excel takes the Calculation mode each session from the settings on the
> first
> workbook opened in that session.
>
> i.e. If you saved Book1 with calc mode in manual and opened it first,
> calc
> mode would be in Manual.
>
> If you saved Book2 with calc mode in auto and opened it after Book1, Book2
> would be in manual mode(Excel ignores the auto calc mode in this case).
>
> If you close Book1 before opening Book2, Book2 will be in auto calc mode.
>
> Confusing enough? <g>
>
>
> Gord Dibben MS Excel MVP
>
>
> On Thu, 14 Dec 2006 18:53:38 GMT, "Steve Latham" <(E-Mail Removed)>
> wrote:
>
>>
>>"Don Guillett" <(E-Mail Removed)> wrote in message
>>news:%(E-Mail Removed)...
>>> f9 or set calculation to automatic?
>>>
>>
>>
>>Hey, it worked!
>>
>>I F9'd the selected portion after dragging and it magically swapped all of
>>them. Don't know why though :-)
>>
>>I'll check into the set calculation to automatic and see if that's a
>>preference I can select/deselect so it stays the way I like it.
>>
>>Thanks Don!!
>>
>>Steve
>>
>
> Gord Dibben MS Excel MVP

Versions back to 97 at least all did the same way.

Calc mode was set by the first woekbook opened.

Gord

On Fri, 15 Dec 2006 01:29:35 GMT, "Steve Latham" <(E-Mail Removed)> wrote:

>Thanks for the explanation.
>
>I upgraded to 2003 at some point, and this must be new (of course it's
>actually old since 2007 is out, but it's new to me) and my previous version
>did it "the makes sense way" :-)
>
>Preesh.
>
>Steve
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:(E-Mail Removed)...
>> Steve
>>
>> Tools>Options>Calculation can be Auto or Manual.
>>
>> Excel takes the Calculation mode each session from the settings on the
>> first
>> workbook opened in that session.
>>
>> i.e. If you saved Book1 with calc mode in manual and opened it first,
>> calc
>> mode would be in Manual.
>>
>> If you saved Book2 with calc mode in auto and opened it after Book1, Book2
>> would be in manual mode(Excel ignores the auto calc mode in this case).
>>
>> If you close Book1 before opening Book2, Book2 will be in auto calc mode.
>>
>> Confusing enough? <g>
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On Thu, 14 Dec 2006 18:53:38 GMT, "Steve Latham" <(E-Mail Removed)>
>> wrote:
>>
>>>
>>>"Don Guillett" <(E-Mail Removed)> wrote in message
>>>news:%(E-Mail Removed)...
>>>> f9 or set calculation to automatic?
>>>>
>>>
>>>
>>>Hey, it worked!
>>>
>>>I F9'd the selected portion after dragging and it magically swapped all of
>>>them. Don't know why though :-)
>>>
>>>I'll check into the set calculation to automatic and see if that's a
>>>preference I can select/deselect so it stays the way I like it.
>>>
>>>Thanks Don!!
>>>
>>>Steve
>>>
>>
>> Gord Dibben MS Excel MVP
>

Gord Dibben MS Excel MVP