Averaging cells across worksheets

[mjansen]

Good evening!

I have a script which downloads stock quotes from Yahoo!, and in doing so creates multiple sheets (not the same number ever), all named for the stockticker. What I would like to do is average each individual cell in column H, across the sheets, so for example average Cell H2 on sheets MSFT, YHOO and SUN. Is this possible in script?

H2 is the first cell with data, but the end cell is always different.

Thanks for your help in advance!

Marc

 

[Yogi Anand]

Hi Marc:

To account for the variable number of sheets, you could envelop all your sheets between two worksheets named First and Last, then to computer average of the values in cell H2 you can use the following formula ...

=AVERAGE(First:Last!H2)

and then copy it down to get the average for cell H3 and so forth.

 

[mjansen]

And then where would this data end up?

Thanks in advance,

Marc

 

[Yogi Anand]

And then where would this data end up?

Thanks in advance,

Marc

Hi Marc:

Where would you like the average of the cells H2 , H3, and so on to show up? I envision you having a Summary sheet, where you will write the formula (say in cell H2, or any other cell of your choice) for the average of the value in cell H2 of all of your stock sheets. And when you will copy this cell down, that will be the average of the values in cell h3, and then h4, and so on.

I hope this helps!

 

[GorD]

Hi, yogi, I've learned something here too. I didn't know you could do that. I take it that you would put the formula in as worksheet that is not housed between first and last.

 

[Yogi Anand]

Hi, yogi, I've learned something here too. I didn't know you could do that. I take it that you would put the formula in as worksheet that is not housed between first and last.

Hi Gord:

That's right -- the sheet that I have called the Summary sheet must be out side of the envelop that houses the First and Last sheets.

 

[Aladin Akyurek]

Hi, yogi, I've learned something here too. I didn't know you could do that. I take it that you would put the formula in as worksheet that is not housed between first and last.

Hi Gord:

That's right -- the sheet that I have called the Summary sheet must be out side of the envelop that houses the First and Last sheets.

There is no such restriction. The 3d formula can be anywhere as long as not in the target cell on any sheet involved.

 

[Yogi Anand]

Hi Aladin:

I had indicated earlier in response to Marc that he may put the formula for averaging in cell H2 and then copy it down. If the formula is put in cell H2 and copied down in the Summary Sheet, and the Summary Sheet is within the envelop of the First and Last Sheet, I would get into the problem of cell referencing.

So, to guard against that eventuality, I suggested that the Summary sheet be outside the envelop of the First and last sheet.

 

[Yogi Anand]

Sorry for the typo in my preceding post -- where I said 'cell referencing' I meant to say 'circular referencing'.