I created a spreadsheet that tracks manhours 7 days a week with the week
starting on Saturday. Our main workdays are Sat thru Thurs and we have a
skeleton crew on Fridays. This is a very large project so I track the average
manhours of the first 6 days ONLY. I don't use Fridays. So, there are 7
cells, the first 6 of which I calc the average in the 8th cell like this:
=AVERAGE(Cell1:Cell6)

Now here's the strange thing: whenever I enter a value in the 7th cell, my
formula changes automatically and calcs the average of ALL SEVEN! I don't
want the average of all seven, I only want the average of the first 6 cells!
I have to change the formula back every time I enter a value in the 7th cell.

What's happening here? This is a very large project and I'm spending a lot
of wasted time changing all my formulas back to average the first 6 cells
only!

Thanks.........TomCat

Assuming your manhours are entered in column A from A1:A7

Where is the formula =AVERAGE(A1:A6) located?

You could be the victim of ToolsOptionsEditExtend data range formats and
formulas.

Uncheck that option and see what occurs.


Gord Dibben MS Excel MVP

On Sat, 29 Sep 2007 19:53:00 -0700, TomCat
wrote:

I created a spreadsheet that tracks manhours 7 days a week with the week
starting on Saturday. Our main workdays are Sat thru Thurs and we have a
skeleton crew on Fridays. This is a very large project so I track the average
manhours of the first 6 days ONLY. I don't use Fridays. So, there are 7
cells, the first 6 of which I calc the average in the 8th cell like this:
=AVERAGE(Cell1:Cell6)

Now here's the strange thing: whenever I enter a value in the 7th cell, my
formula changes automatically and calcs the average of ALL SEVEN! I don't
want the average of all seven, I only want the average of the first 6 cells!
I have to change the formula back every time I enter a value in the 7th cell.

What's happening here? This is a very large project and I'm spending a lot
of wasted time changing all my formulas back to average the first 6 cells
only!

Thanks.........TomCat

Well, I'll be horsewhipped! That did it. I guess that means I have to
recheck that option for my other sheets? I do like that option on for most
everything. Or, will it just be turned off for this sheet only?

Thanks for the help, tho. That knocked it out........TomCat

"Gord Dibben" wrote:

Assuming your manhours are entered in column A from A1:A7

Where is the formula =AVERAGE(A1:A6) located?

You could be the victim of ToolsOptionsEditExtend data range formats and
formulas.

Uncheck that option and see what occurs.


Gord Dibben MS Excel MVP

On Sat, 29 Sep 2007 19:53:00 -0700, TomCat
wrote:

I created a spreadsheet that tracks manhours 7 days a week with the week
starting on Saturday. Our main workdays are Sat thru Thurs and we have a
skeleton crew on Fridays. This is a very large project so I track the average
manhours of the first 6 days ONLY. I don't use Fridays. So, there are 7
cells, the first 6 of which I calc the average in the 8th cell like this:
=AVERAGE(Cell1:Cell6)

Now here's the strange thing: whenever I enter a value in the 7th cell, my
formula changes automatically and calcs the average of ALL SEVEN! I don't
want the average of all seven, I only want the average of the first 6 cells!
I have to change the formula back every time I enter a value in the 7th cell.

What's happening here? This is a very large project and I'm spending a lot
of wasted time changing all my formulas back to average the first 6 cells
only!

Thanks.........TomCat

It is a global setting, not a sheet or workbook setting.

If you wanted it disabled for just one sheet you could use sheet activate code
to disable when that sheet is selected and re-enable when sheet is deactivated.

Private Sub Worksheet_Activate()
Application.ExtendList = False
End Sub

Private Sub Worksheet_Deactivate()
Application.ExtendList = True
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste the two sets of code above into that sheet module.


Gord

On Sat, 29 Sep 2007 20:24:01 -0700, TomCat
wrote:

Well, I'll be horsewhipped! That did it. I guess that means I have to
recheck that option for my other sheets? I do like that option on for most
everything. Or, will it just be turned off for this sheet only?

Thanks for the help, tho. That knocked it out........TomCat

"Gord Dibben" wrote:

Assuming your manhours are entered in column A from A1:A7

Where is the formula =AVERAGE(A1:A6) located?

You could be the victim of ToolsOptionsEditExtend data range formats and
formulas.

Uncheck that option and see what occurs.


Gord Dibben MS Excel MVP

On Sat, 29 Sep 2007 19:53:00 -0700, TomCat
wrote:

I created a spreadsheet that tracks manhours 7 days a week with the week
starting on Saturday. Our main workdays are Sat thru Thurs and we have a
skeleton crew on Fridays. This is a very large project so I track the average
manhours of the first 6 days ONLY. I don't use Fridays. So, there are 7
cells, the first 6 of which I calc the average in the 8th cell like this:
=AVERAGE(Cell1:Cell6)

Now here's the strange thing: whenever I enter a value in the 7th cell, my
formula changes automatically and calcs the average of ALL SEVEN! I don't
want the average of all seven, I only want the average of the first 6 cells!
I have to change the formula back every time I enter a value in the 7th cell.

What's happening here? This is a very large project and I'm spending a lot
of wasted time changing all my formulas back to average the first 6 cells
only!

Thanks.........TomCat

Try one of these formulas

=AVERAGE($A$1:$A$6)
or
=AVERAGE(INDIRECT("A1:A6"))
or
=AVERAGE(OFFSET($A$1,,,6,))


"TomCat" wrote:

I created a spreadsheet that tracks manhours 7 days a week with the week
starting on Saturday. Our main workdays are Sat thru Thurs and we have a
skeleton crew on Fridays. This is a very large project so I track the average
manhours of the first 6 days ONLY. I don't use Fridays. So, there are 7
cells, the first 6 of which I calc the average in the 8th cell like this:
=AVERAGE(Cell1:Cell6)

Now here's the strange thing: whenever I enter a value in the 7th cell, my
formula changes automatically and calcs the average of ALL SEVEN! I don't
want the average of all seven, I only want the average of the first 6 cells!
I have to change the formula back every time I enter a value in the 7th cell.

What's happening here? This is a very large project and I'm spending a lot
of wasted time changing all my formulas back to average the first 6 cells
only!

Thanks.........TomCat