average function in Excel 2002

Hello all!
I wish to obtain the average of a number of numeric cells but wish this
average to be ongoing rather than restricted to a range of cells.

For example
=AVERAGE(D1317) will only give the average of the cells D13 to D17 but I
will be continually adding more data and wish for this to be calculated in
the average also.

My overall objective here is to carry out evaluations on staff performance
and as each new review arrives from a client regarding a staff member, I will
be adding a new row to include this data.Therefore need to have the number of
all reviews received whether it be five or fifty to be calculated in the
overall average.

I have not been able to identify anything in the application help to cover
this use of the average function and help will be greatfully appreciated

Cheers Sherry

Define a dynamic range...

Insert Name Define

Name: Data

Refers to:
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536))

Click Ok

Then use the following formula...

=AVERAGE(Data)

Hope this helps!

In article ,
Sherry wrote:

Hello all!
I wish to obtain the average of a number of numeric cells but wish this
average to be ongoing rather than restricted to a range of cells.

For example
=AVERAGE(D1317) will only give the average of the cells D13 to D17 but I
will be continually adding more data and wish for this to be calculated in
the average also.

My overall objective here is to carry out evaluations on staff performance
and as each new review arrives from a client regarding a staff member, I will
be adding a new row to include this data.Therefore need to have the number of
all reviews received whether it be five or fifty to be calculated in the
overall average.

I have not been able to identify anything in the application help to cover
this use of the average function and help will be greatfully appreciated

Cheers Sherry

I risk causing some upset here but...............Ummmmm
What does that all mean Domenic? Some dumb questions to follow!!

Which cell do I name Data???

Where do I place
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536)) ????

Where do I use the formula... =AVERAGE(Data)

Perhaps I should have included the fact that I am very new to this functions
and macro stuff. A point that I am sure after this post will become painfully
obvious. LOL

"Domenic" wrote:

Define a dynamic range...

Insert Name Define

Name: Data

Refers to:
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536))

Click Ok

Then use the following formula...

=AVERAGE(Data)

Hope this helps!

In article ,
Sherry wrote:

Hello all!
I wish to obtain the average of a number of numeric cells but wish this
average to be ongoing rather than restricted to a range of cells.

For example
=AVERAGE(D1317) will only give the average of the cells D13 to D17 but I
will be continually adding more data and wish for this to be calculated in
the average also.

My overall objective here is to carry out evaluations on staff performance
and as each new review arrives from a client regarding a staff member, I will
be adding a new row to include this data.Therefore need to have the number of
all reviews received whether it be five or fifty to be calculated in the
overall average.

I have not been able to identify anything in the application help to cover
this use of the average function and help will be greatfully appreciated

Cheers Sherry

Well, you posted to the .newusers group, so don't be shy about asking
follow-ups.

Dominic was suggesting that you define a dynamic range. That means you
don't name specific cells.

Instead, choose the Insert/Name/Define menu item. In the "Names in
workbook" textbox enter "Data" (without the quotes).

In the "Refers to:" textbox, enter the formula, then click "Add". You
can use Dominic's formula, or, if you don't have any blank rows, this
formula will also work:

=OFFSET(Sheet1!$D$13,0,0,COUNTA(Sheet1!$D$13:$D$65 536),1)


Adjust the references for your sheet (e.g., if you sheet is named
"Staff", use

=OFFSET(Staff!$D$13...

For an alternative explanation, see

http://cpearson.com/excel/named.htm#Dynamic


In article ,
Sherry wrote:

I risk causing some upset here but...............Ummmmm
What does that all mean Domenic? Some dumb questions to follow!!

Which cell do I name Data???

Where do I place
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536)) ????

Where do I use the formula... =AVERAGE(Data)

Perhaps I should have included the fact that I am very new to this functions
and macro stuff. A point that I am sure after this post will become painfully
obvious. LOL

Hi Sherry!

No problem, let me gear down a bit...

First we need to define a dynamic range. In doing so, the range will
automatically adjust as new data is entered. But we'll need to name
this range and provide a reference for it.

Go to the top of your Excel menu and select Insert. Then select Name,
and then Define. A new window will open. Where it says 'Name:', enter
the name you wish to use for this range. In my example, I used Data,
but you can use whichever name you wish. Once you've entered the name,
enter the reference where it says 'Refers to:'. The reference would
be...

=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536))

Once you've entered the reference, click OK. Now you can use the
following formula to give you your average...

=AVERAGE(Data)

So as you can see, the range we're using for the AVERAGE function is
Data, which we defined in the first step.

Hope this helps!

In article ,
Sherry wrote:

I risk causing some upset here but...............Ummmmm
What does that all mean Domenic? Some dumb questions to follow!!

Which cell do I name Data???

Where do I place
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536)) ????

Where do I use the formula... =AVERAGE(Data)

Perhaps I should have included the fact that I am very new to this functions
and macro stuff. A point that I am sure after this post will become painfully
obvious. LOL

"Domenic" wrote:

Define a dynamic range...

Insert Name Define

Name: Data

Refers to:
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536))

Click Ok

Then use the following formula...

=AVERAGE(Data)

Hope this helps!

In article ,
Sherry wrote:

Hello all!
I wish to obtain the average of a number of numeric cells but wish this
average to be ongoing rather than restricted to a range of cells.

For example
=AVERAGE(D1317) will only give the average of the cells D13 to D17 but
I
will be continually adding more data and wish for this to be calculated
in
the average also.

My overall objective here is to carry out evaluations on staff
performance
and as each new review arrives from a client regarding a staff member, I
will
be adding a new row to include this data.Therefore need to have the
number of
all reviews received whether it be five or fifty to be calculated in the
overall average.

I have not been able to identify anything in the application help to
cover
this use of the average function and help will be greatfully appreciated

Cheers Sherry

Sherry

An easy way that doesn't use any formula.
Right-click the Status Bar, and put a checkmark next to 'Average'.
Highlight the cells you wish to average, the average of these cells
will be displayed on the Status Bar!

George Gee



*Sherry* has posted this message:

Hello all!
I wish to obtain the average of a number of numeric cells but wish
this average to be ongoing rather than restricted to a range of cells.

For example
=AVERAGE(D1317) will only give the average of the cells D13 to D17
but I will be continually adding more data and wish for this to be
calculated in the average also.

My overall objective here is to carry out evaluations on staff
performance and as each new review arrives from a client regarding a
staff member, I will be adding a new row to include this
data.Therefore need to have the number of all reviews received
whether it be five or fifty to be calculated in the overall average.

I have not been able to identify anything in the application help to
cover this use of the average function and help will be greatfully
appreciated

Cheers Sherry

Thank you.This has acheived my objective and more importantly helped me to
understand what I am doing. I am most grateful and suitably impressed with
the amount of interest and support that was forthcoming in such a timely
manner. I know I will have many more challenges to overcome during this
learning curve so I am sure I will post again soon. TY

Cheers Sherry

"Domenic" wrote:

Hi Sherry!

No problem, let me gear down a bit...

First we need to define a dynamic range. In doing so, the range will
automatically adjust as new data is entered. But we'll need to name
this range and provide a reference for it.

Go to the top of your Excel menu and select Insert. Then select Name,
and then Define. A new window will open. Where it says 'Name:', enter
the name you wish to use for this range. In my example, I used Data,
but you can use whichever name you wish. Once you've entered the name,
enter the reference where it says 'Refers to:'. The reference would
be...

=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536))

Once you've entered the reference, click OK. Now you can use the
following formula to give you your average...

=AVERAGE(Data)

So as you can see, the range we're using for the AVERAGE function is
Data, which we defined in the first step.

Hope this helps!

In article ,
Sherry wrote:

I risk causing some upset here but...............Ummmmm
What does that all mean Domenic? Some dumb questions to follow!!

Which cell do I name Data???

Where do I place
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536)) ????

Where do I use the formula... =AVERAGE(Data)

Perhaps I should have included the fact that I am very new to this functions
and macro stuff. A point that I am sure after this post will become painfully
obvious. LOL

"Domenic" wrote:

Define a dynamic range...

Insert Name Define

Name: Data

Refers to:
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536))

Click Ok

Then use the following formula...

=AVERAGE(Data)

Hope this helps!

In article ,
Sherry wrote:

Hello all!
I wish to obtain the average of a number of numeric cells but wish this
average to be ongoing rather than restricted to a range of cells.

For example
=AVERAGE(D1317) will only give the average of the cells D13 to D17 but
I
will be continually adding more data and wish for this to be calculated
in
the average also.

My overall objective here is to carry out evaluations on staff
performance
and as each new review arrives from a client regarding a staff member, I
will
be adding a new row to include this data.Therefore need to have the
number of
all reviews received whether it be five or fifty to be calculated in the
overall average.

I have not been able to identify anything in the application help to
cover
this use of the average function and help will be greatfully appreciated

Cheers Sherry

Thank You also JE McGimpsey
The site that you referenced is excellent. I found the writting style to be
very well laid out and easy to understand. (It's in my favourites folder now)
Thank You JE

"JE McGimpsey" wrote:

Well, you posted to the .newusers group, so don't be shy about asking
follow-ups.

Dominic was suggesting that you define a dynamic range. That means you
don't name specific cells.

Instead, choose the Insert/Name/Define menu item. In the "Names in
workbook" textbox enter "Data" (without the quotes).

In the "Refers to:" textbox, enter the formula, then click "Add". You
can use Dominic's formula, or, if you don't have any blank rows, this
formula will also work:

=OFFSET(Sheet1!$D$13,0,0,COUNTA(Sheet1!$D$13:$D$65 536),1)


Adjust the references for your sheet (e.g., if you sheet is named
"Staff", use

=OFFSET(Staff!$D$13...

For an alternative explanation, see

http://cpearson.com/excel/named.htm#Dynamic


In article ,
Sherry wrote:

I risk causing some upset here but...............Ummmmm
What does that all mean Domenic? Some dumb questions to follow!!

Which cell do I name Data???

Where do I place
=Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She
et1!$D$13:$D$65536)) ????

Where do I use the formula... =AVERAGE(Data)

Perhaps I should have included the fact that I am very new to this functions
and macro stuff. A point that I am sure after this post will become painfully
obvious. LOL

Thankyou George,
I wasn't aware that existed.
(I am not game to reveal how I was counting cells before this)
Best Regards,
Rodney




| Sherry
|
| An easy way that doesn't use any formula.
| Right-click the Status Bar, and put a checkmark next to 'Average'.
| Highlight the cells you wish to average, the average of these cells
| will be displayed on the Status Bar!
|
| George Gee
|
|
|
| *Sherry* has posted this message:
|
| Hello all!
| I wish to obtain the average of a number of numeric cells but wish
| this average to be ongoing rather than restricted to a range of cells.
|
| For example
| =AVERAGE(D1317) will only give the average of the cells D13 to D17
| but I will be continually adding more data and wish for this to be
| calculated in the average also.
|
| My overall objective here is to carry out evaluations on staff
| performance and as each new review arrives from a client regarding a
| staff member, I will be adding a new row to include this
| data.Therefore need to have the number of all reviews received
| whether it be five or fifty to be calculated in the overall average.
|
| I have not been able to identify anything in the application help to
| cover this use of the average function and help will be greatfully
| appreciated
|
| Cheers Sherry
|
|

Rodney

Being only an 'average' user of Excel, myself, it is sometimes the simple
things that get overlooked by the 'experts', that are of some use to us
'mere mortals'!

Let me guess how you were counting cells... one, two, three, four........?

Good to know I have helped someone!

George Gee



*Rodney* has posted this message:

Thankyou George,
I wasn't aware that existed.
(I am not game to reveal how I was counting cells before this)
Best Regards,
Rodney




Sherry

An easy way that doesn't use any formula.
Right-click the Status Bar, and put a checkmark next to 'Average'.
Highlight the cells you wish to average, the average of these cells
will be displayed on the Status Bar!

George Gee



*Sherry* has posted this message:

Hello all!
I wish to obtain the average of a number of numeric cells but wish
this average to be ongoing rather than restricted to a range of
cells.

For example
=AVERAGE(D1317) will only give the average of the cells D13 to D17
but I will be continually adding more data and wish for this to be
calculated in the average also.

My overall objective here is to carry out evaluations on staff
performance and as each new review arrives from a client regarding a
staff member, I will be adding a new row to include this
data.Therefore need to have the number of all reviews received
whether it be five or fifty to be calculated in the overall average.

I have not been able to identify anything in the application help to
cover this use of the average function and help will be greatfully
appreciated

Cheers Sherry