Function Key for Calculating Average?

R

robin

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.
 
K

Ken Puls

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
 
R

robin

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

Epinn

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

Epinn

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

Epinn

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

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
 
R

robin

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

Epinn

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

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

Biff

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?
Click to expand...

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

Biff

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

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

robin

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.
 
K

Ken Puls

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
 
E

Epinn

.....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

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
 
R

robin

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?
 
K

Ken Puls

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
 
E

Epinn

My formula was based on the following question.
but that's what I need to do on 13 different worksheets.<<

Biff has already answered you.

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

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