[Milo Bloom]

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

I know how to running average columns, but I have like 15 or so of these (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 is number ofd days in a month.

=AVERAGE(Jan!$C$24:Jan!$C$54,Feb!$C$2:Feb!$C$30,Ma r!$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:J an13!$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 :-)

[Spencer101]

Originally Posted by Milo Bloom

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

I know how to running average columns, but I have like 15 or so of these (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 is number ofd days in a month.

=AVERAGE(Jan!$C$24:Jan!$C$54,Feb!$C$2:Feb!$C$30,Ma r!$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:J an13!$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 things like what version of Excel you're using for this workbook and what your data layout is like. None of which we can tell from your post...

[shanermuls]

Originally Posted by Spencer101

There are several ways of making this easier, but they depend on things like what version of Excel you're using for this workbook and what your 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 can specify in a formula what tab to look into. The other two tabs just format the inputs for the formula... if you require further explanation i can provide this.

[Milo Bloom]

Originally Posted by shanermuls

Try the attached spreadsheet

The indirect function gathers text to make a formula - so you can specify in a formula what tab to look into. The other two tabs just format the inputs for the formula... if you require further explanation 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 2003


Thanks

[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 he
https://skydrive.live.com/#cid=9378A...121822A3%21191
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Milo Bloom]

Originally Posted by 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 he
https://skydrive.live.com/#cid=9378A...121822A3%21191
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.
Is there a tool for formula absolut Value checking in Excell?

[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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2