Argh. Please Help

[Steve Latham]

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]

f9 or set calculation to automatic?

 

[Steve Latham]

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]

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

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

 

[Steve Latham]

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]

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

Calc mode was set by the first woekbook opened.

Gord

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 MS Excel MVP