How to avergage colums

M

Milo Bloom

Is there a easier way to average a column that appears in many sheets i
a woorkbook.

I know how to average a column, but I have like 15 or so of these (ne
one every month)

here is what I use now.
where row 1 is a header so we start with row 2 and then the row enght i
number ofd days in a month.

=AVERAGE(Jan!$C$24:Jan!$C$54,Feb!$C$2:Feb!$C$30,Mar!$C$2:Mar!$C$32,Apr!$C$2:Apr!$C$31,May!$C31:May!$C$32,Jun!$C$2:Jun!$C$31,Jul!$C$2:Jul!$C$32,Aug!$C$2:Aug!$C$32,Sep!$C$2:Sep!$C$31,Oct!$C$2:Oct!$C$32,Nov!$C$2:Nov!$C$31,Dec!$C$2:Dec!$C$32,Jan13!$C$2:Jan13!$C$32,Feb13!$C$2:Feb13!$C$29,Mar13!$C$2:Mar13!$C$32,Apr13!$C$2:Apr13!$C31)

Thanks in advance for any help :-
 
S

Spencer101

Milo said:
Is there a easier way to average a column that appears in many sheets i
a woorkbook.

I know how to running average columns, but I have like 15 or so of thes
(new one every month)

here is what I use now.
where row 1 is a header so we start with row 2 and then the row enght i
number ofd days in a month.

=AVERAGE(Jan!$C$24:Jan!$C$54,Feb!$C$2:Feb!$C$30,Mar!$C$2:Mar!$C$32,Apr!$C$2:Apr!$C$31,May!$C31:May!$C$32,Jun!$C$2:Jun!$C$31,Jul!$C$2:Jul!$C$32,Aug!$C$2:Aug!$C$32,Sep!$C$2:Sep!$C$31,Oct!$C$2:Oct!$C$32,Nov!$C$2:Nov!$C$31,Dec!$C$2:Dec!$C$32,Jan13!$C$2:Jan13!$C$32,Feb13!$C$2:Feb13!$C$29,Mar13!$C$2:Mar13!$C$32,Apr13!$C$2:Apr13!$C2
then it would be $c3 then $c4.... to end of the month

Thanks in advance for any help :)

There are several ways of making this easier, but they depend on thing
like what version of Excel you're using for this workbook and what you
data layout is like. None of which we can tell from your post..
 
S

shanermuls

Spencer101;1610938 said:
There are several ways of making this easier, but they depend on thing
like what version of Excel you're using for this workbook and what you
data layout is like. None of which we can tell from your post...

Try the attached spreadsheet

The indirect function gathers text to make a formula - so you ca
specify in a formula what tab to look into. The other two tabs jus
format the inputs for the formula... if you require further explanatio
i can provide this

+-------------------------------------------------------------------
|Filename: average over sheets (2).zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=837
+-------------------------------------------------------------------
 
M

Milo Bloom

shanermuls;1610954 said:
Try the attached spreadshee

The indirect function gathers text to make a formula - so you ca
specify in a formula what tab to look into. The other two tabs jus
format the inputs for the formula... if you require further explanatio
i can provide this

I have attached the file with all the sheets in it, Column C is "Data
and the last column is the one I am looking for the answer to. "Run Avg

This is Excell 200

Thank

+-------------------------------------------------------------------
|Filename: question sheets.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=838
+-------------------------------------------------------------------
 
C

Claus Busch

Hi Milo,

Am Sat, 6 Apr 2013 05:11:49 +0000 schrieb Milo Bloom:
I have attached the file with all the sheets in it, Column C is "Data"
and the last column is the one I am looking for the answer to. "Run Avg"

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for your workbook "question sheets", right-click and download
For April to Juli I entered a suggestion in column I or J.
For January to March you can't do it in a simplier way.
You sometimes forgot to set the reference absolut.


Regards
Claus Busch
 
M

Milo Bloom

Claus said:
Hi Milo

Am Sat, 6 Apr 2013 05:11:49 +0000 schrieb Milo Bloom

Avg"

please look h
http://tinyurl.com/3s4zwa
for your workbook "question sheets", right-click and downloa
For April to Juli I entered a suggestion in column I or J
For January to March you can't do it in a simplier way
You sometimes forgot to set the reference absolut


Regard
Claus Busc

Thanks for the help but the problem with that is not all columns end a
row 32, they end between rows 29 and 32. Row 33 and below (i did no
include that) start doing a bunch of number crunching for the specifi
month
Is there a tool for formula absolut Value checking in Excell

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
C

Claus Busch

Hi Milo,

Am Sun, 7 Apr 2013 10:18:34 +0000 schrieb Milo Bloom:
Thanks for the help but the problem with that is not all columns end at
row 32, they end between rows 29 and 32. Row 33 and below (i did not
include that) start doing a bunch of number crunching for the specific
month.

you can use the greatest range e.g. C2:C32
Empty cells will be ignored


Regards
Claus Busch
 
C

Claus Busch

Hi Milo,

Am Sun, 7 Apr 2013 15:44:28 +0200 schrieb Claus Busch:
you can use the greatest range e.g. C2:C32
Empty cells will be ignored

have a look in the help for AVERAGE
Text and empty cells will be ignored.
You only would get a wrong result, if there are zeros in the cells
below.


Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top