Averaging cells across worksheets

mjansen

New Member
Joined
Jan 23, 2004
Messages
20
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
mjansen said:
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
GorD said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Yogi Anand said:
GorD said:
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Sorry for the typo in my preceding post -- where I said 'cell referencing' I meant to say 'circular referencing'.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top