Function Key for Calculating Average?
 

Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

Ken wasn't talking about the pull down or down arrow beside AutoSum.

Epinn

"robin" wrote in message ...
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

thank you! - I printed out your article.

"Ken Puls" wrote:

Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

Ken,

That's very efficient of you. I like that feature and I have been using it for quite a few months now. I use it to count so I don't see SUM. If a user had "none" chosen, he/she would not see anything displayed. I hope they know they can just right-click anywhere on the taskbar towards the right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text, numbers, special signs, nulls etc. The only thing not counted is a true blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably asking for too much?

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using it for quite a few months now. I use it to count so I don't see SUM. If a user had "none" chosen, he/she would not see anything displayed. I hope they know they can just right-click anywhere on the taskbar towards the right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text, numbers, special signs, nulls etc. The only thing not counted is a true blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably asking for too much?

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet ......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells (A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round numbers. Experts, can you confirm that it is also okay for numbers with decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message ...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using it for quite a few months now. I use it to count so I don't see SUM. If a user had "none" chosen, he/she would not see anything displayed. I hope they know they can just right-click anywhere on the taskbar towards the right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text, numbers, special signs, nulls etc. The only thing not counted is a true blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably asking for too much?

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of cells?
I'm looking for either a function key or shortcut key method. Please advise
ASAP.

Function Key for Calculating Average?
 

can you confirm that it is also okay for numbers with decimals?

Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?

Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet ......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?

Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?

Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet ......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

Hi Robin,

The formula that Epinn provided you will work across sheets. Say you
have Sheet1, Sheet2, Sheet3, then the following formula will average
A1:C10 of all three sheets:
=AVERAGE(Sheet1:Sheet3!A1:C10)

One thing to note, however, is that if your sheets were ordered (from
left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2. The formula to span sheets spans them as
they are ordered in the workbook. I hope that makes sense.

The quick info feature I described above only works on the active
worksheet, as Biff said.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?
Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?
Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet ......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

well all i can do is give it a try...thank you all for all your help!

"Ken Puls" wrote:

Hi Robin,

The formula that Epinn provided you will work across sheets. Say you
have Sheet1, Sheet2, Sheet3, then the following formula will average
A1:C10 of all three sheets:
=AVERAGE(Sheet1:Sheet3!A1:C10)

One thing to note, however, is that if your sheets were ordered (from
left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2. The formula to span sheets spans them as
they are ordered in the workbook. I hope that makes sense.

The quick info feature I described above only works on the active
worksheet, as Biff said.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?
Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?
Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet ......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

.....if your sheets were ordered (from left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2.....<<

Good point. Following is for future reference ......

If you don't want to drag Sheet2 before Sheet3, you can set up "Firstsheet" to the left of Sheet1 and "Lastsheet" to the right of Sheet2. Firstsheet and Lastsheet are blank and can be hidden.

Then change the formula to =AVERAGE(Firstsheet : Lastsheet!A1:C10)

(Note: There are no spaces before and after the colon. I included the spaces to make it legible in this post.)

With the above set up and formula, the sheets between Firstsheet and Lastsheet can be in any order. You can even insert Sheet4, Sheet9, Sheet99 in between Firstsheet and Lastsheet without changing the formula. All six sheets will be averaged.

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

The formula that Epinn provided you will work across sheets. Say you
have Sheet1, Sheet2, Sheet3, then the following formula will average
A1:C10 of all three sheets:
=AVERAGE(Sheet1:Sheet3!A1:C10)

One thing to note, however, is that if your sheets were ordered (from
left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2. The formula to span sheets spans them as
they are ordered in the workbook. I hope that makes sense.

The quick info feature I described above only works on the active
worksheet, as Biff said.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?
Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?
Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet .......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

I'm not taking the average of the sheets, I'm taking the average of 3 cells
on a separate sheet from where I'm wanting my average total to be placed.
I'm creating a summary sheet whereby 1 cell will reflect the average of 3
cells from another sheet in the workbook. This will work with the method
described?

"Epinn" wrote:

.....if your sheets were ordered (from left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2.....<<

Good point. Following is for future reference ......

If you don't want to drag Sheet2 before Sheet3, you can set up "Firstsheet" to the left of Sheet1 and "Lastsheet" to the right of Sheet2. Firstsheet and Lastsheet are blank and can be hidden.

Then change the formula to =AVERAGE(Firstsheet : Lastsheet!A1:C10)

(Note: There are no spaces before and after the colon. I included the spaces to make it legible in this post.)

With the above set up and formula, the sheets between Firstsheet and Lastsheet can be in any order. You can even insert Sheet4, Sheet9, Sheet99 in between Firstsheet and Lastsheet without changing the formula. All six sheets will be averaged.

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

The formula that Epinn provided you will work across sheets. Say you
have Sheet1, Sheet2, Sheet3, then the following formula will average
A1:C10 of all three sheets:
=AVERAGE(Sheet1:Sheet3!A1:C10)

One thing to note, however, is that if your sheets were ordered (from
left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2. The formula to span sheets spans them as
they are ordered in the workbook. I hope that makes sense.

The quick info feature I described above only works on the active
worksheet, as Biff said.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?
Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?
Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet .......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

Try this:

To get the average of cells A1:A3 from Sheet3 into a cell in any other
sheet, just enter the following in the desired sheet's cell

=AVERAGE(Sheet3!A1:A3)

Is that more what you're after?

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I'm not taking the average of the sheets, I'm taking the average of 3 cells
on a separate sheet from where I'm wanting my average total to be placed.
I'm creating a summary sheet whereby 1 cell will reflect the average of 3
cells from another sheet in the workbook. This will work with the method
described?

"Epinn" wrote:

.....if your sheets were ordered (from left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2.....<<

Good point. Following is for future reference ......

If you don't want to drag Sheet2 before Sheet3, you can set up "Firstsheet" to the left of Sheet1 and "Lastsheet" to the right of Sheet2. Firstsheet and Lastsheet are blank and can be hidden.

Then change the formula to =AVERAGE(Firstsheet : Lastsheet!A1:C10)

(Note: There are no spaces before and after the colon. I included the spaces to make it legible in this post.)

With the above set up and formula, the sheets between Firstsheet and Lastsheet can be in any order. You can even insert Sheet4, Sheet9, Sheet99 in between Firstsheet and Lastsheet without changing the formula. All six sheets will be averaged.

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

The formula that Epinn provided you will work across sheets. Say you
have Sheet1, Sheet2, Sheet3, then the following formula will average
A1:C10 of all three sheets:
=AVERAGE(Sheet1:Sheet3!A1:C10)

One thing to note, however, is that if your sheets were ordered (from
left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2. The formula to span sheets spans them as
they are ordered in the workbook. I hope that makes sense.

The quick info feature I described above only works on the active
worksheet, as Biff said.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?
Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?
Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet .......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

yes...can i use the right-click/pull-up menu at the bottom like recommended?

"Ken Puls" wrote:

Try this:

To get the average of cells A1:A3 from Sheet3 into a cell in any other
sheet, just enter the following in the desired sheet's cell

=AVERAGE(Sheet3!A1:A3)

Is that more what you're after?

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I'm not taking the average of the sheets, I'm taking the average of 3 cells
on a separate sheet from where I'm wanting my average total to be placed.
I'm creating a summary sheet whereby 1 cell will reflect the average of 3
cells from another sheet in the workbook. This will work with the method
described?

"Epinn" wrote:

.....if your sheets were ordered (from left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2.....<<

Good point. Following is for future reference ......

If you don't want to drag Sheet2 before Sheet3, you can set up "Firstsheet" to the left of Sheet1 and "Lastsheet" to the right of Sheet2. Firstsheet and Lastsheet are blank and can be hidden.

Then change the formula to =AVERAGE(Firstsheet : Lastsheet!A1:C10)

(Note: There are no spaces before and after the colon. I included the spaces to make it legible in this post.)

With the above set up and formula, the sheets between Firstsheet and Lastsheet can be in any order. You can even insert Sheet4, Sheet9, Sheet99 in between Firstsheet and Lastsheet without changing the formula. All six sheets will be averaged.

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

The formula that Epinn provided you will work across sheets. Say you
have Sheet1, Sheet2, Sheet3, then the following formula will average
A1:C10 of all three sheets:
=AVERAGE(Sheet1:Sheet3!A1:C10)

One thing to note, however, is that if your sheets were ordered (from
left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2. The formula to span sheets spans them as
they are ordered in the workbook. I hope that makes sense.

The quick info feature I described above only works on the active
worksheet, as Biff said.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?
Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?
Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet .......

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.

Function Key for Calculating Average?
 

My formula was based on the following question.

Will this feature average cells on different worksheets? ......
but that's what I need to do on 13 different worksheets.<<

can i use the right-click/pull-up menu at the bottom like recommended? <<

Biff has already answered you.

Haven't tried it, <<
well all i can do is give it a try... <<

That's a good idea. It won't bite. But make sure you have a backup copy .......

I am signing off from this thread.

Epinn

"robin" wrote in message ...
yes...can i use the right-click/pull-up menu at the bottom like recommended?

"Ken Puls" wrote:

Try this:

To get the average of cells A1:A3 from Sheet3 into a cell in any other
sheet, just enter the following in the desired sheet's cell

=AVERAGE(Sheet3!A1:A3)

Is that more what you're after?

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I'm not taking the average of the sheets, I'm taking the average of 3 cells
on a separate sheet from where I'm wanting my average total to be placed.
I'm creating a summary sheet whereby 1 cell will reflect the average of 3
cells from another sheet in the workbook. This will work with the method
described?

"Epinn" wrote:

.....if your sheets were ordered (from left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2.....<<

Good point. Following is for future reference ......

If you don't want to drag Sheet2 before Sheet3, you can set up "Firstsheet" to the left of Sheet1 and "Lastsheet" to the right of Sheet2. Firstsheet and Lastsheet are blank and can be hidden.

Then change the formula to =AVERAGE(Firstsheet : Lastsheet!A1:C10)

(Note: There are no spaces before and after the colon. I included the spaces to make it legible in this post.)

With the above set up and formula, the sheets between Firstsheet and Lastsheet can be in any order. You can even insert Sheet4, Sheet9, Sheet99 in between Firstsheet and Lastsheet without changing the formula. All six sheets will be averaged.

Epinn

"Ken Puls" wrote in message ...
Hi Robin,

The formula that Epinn provided you will work across sheets. Say you
have Sheet1, Sheet2, Sheet3, then the following formula will average
A1:C10 of all three sheets:
=AVERAGE(Sheet1:Sheet3!A1:C10)

One thing to note, however, is that if your sheets were ordered (from
left to right at the bottom) as Sheet1, Sheet3, Sheet2, the above
formula will ignore Sheet2. The formula to span sheets spans them as
they are ordered in the workbook. I hope that makes sense.

The quick info feature I described above only works on the active
worksheet, as Biff said.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
What I'm creating is a summary sheet in a workbook. What this is to show is
the average percentage of 3 cells on a different worksheet within the same
workbook. (I'm doing a quarterly averages.) - Please tell me this will work
for what I'm doing.

"Biff" wrote:

can you confirm that it is also okay for numbers with decimals?
Yes. Numbers are numbers whether they're integers or decimals.

Will this feature average cells on different worksheets?
Do you mean more than one sheet at a time? If so, no. It only works on the
active sheet.

Biff

"Epinn" wrote in message
...
Robin,

You may already know how to do it with a formula?

Assuming the position of the ranges are identical on each sheet ........

=AVERAGE(Sheet1:Sheet13!A1:C10) will give you an average of all the cells
(A1:C10) from sheet 1 to sheet 13.

Is that what you want as an end result? This formula is good for round
numbers. Experts, can you confirm that it is also okay for numbers with
decimals? Wonder if some kind of rounding is needed?

Epinn

"robin" wrote in message
...
Will this feature average cells on different worksheets? Haven't tried it,
but that's what I need to do on 13 different worksheets.

"Epinn" wrote:

Ken,

That's very efficient of you. I like that feature and I have been using
it for quite a few months now. I use it to count so I don't see SUM. If
a user had "none" chosen, he/she would not see anything displayed. I hope
they know they can just right-click anywhere on the taskbar towards the
right half of the screen to bring up the pull down list.

Today I experiment some more and realize that COUNT actually counts text,
numbers, special signs, nulls etc. The only thing not counted is a true
blank. Null is treated as text or non-blank.

I wish there is a way to capture whatever displayed into a cell. Probably
asking for too much?

Epinn

"Ken Puls" wrote in message
...
Hi Robin,

I wrote up an article on my site to more clearly describe what I was
talking about. Set this feature once, and it should be very quick to use.

http://www.excelguru.ca/node/82

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
I was actually looking for a keyboard shortcut, as it takes longer to
pull-down & select like I've been doing. Thanks anyway.

"Ken Puls" wrote:

Highlight the cells you want to average

On the very bottom of the Excel window, you'll see probably see (on the
right), something that says SUM=xxx.xx where the xxx is your total.
Right click that, and choose average from the list.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

robin wrote:
Is there a way, beyond using the pull-down, to calculate an average of
cells?
I'm looking for either a function key or shortcut key method. Please
advise
ASAP.