Strange thing happens...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TomCat - 30 Sep 2007 03:53 GMT

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

Reply to this Message

Gord Dibben - 30 Sep 2007 04:08 GMT

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 Tools>Options>Edit>Extend data range formats and
formulas.

Uncheck that option and see what occurs.

Gord Dibben MS Excel MVP

>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
[quoted text clipped - 13 lines]
>
>Thanks.........TomCat

Reply to this Message

TomCat - 30 Sep 2007 04:24 GMT

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

> Assuming your manhours are entered in column A from A1:A7
>
[quoted text clipped - 24 lines]
> >
> >Thanks.........TomCat

Reply to this Message

Gord Dibben - 30 Sep 2007 04:40 GMT

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

>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
[quoted text clipped - 30 lines]
>> >
>> >Thanks.........TomCat

Reply to this Message

Teethless mama - 30 Sep 2007 16:17 GMT

Try one of these formulas

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

> 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
[quoted text clipped - 13 lines]
>
> Thanks.........TomCat

Reply to this Message