# Averaging cells across worksheets

#### mjansen

##### New Member
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.

Marc

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Yogi Anand

##### MrExcel MVP
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

##### New Member
And then where would this data end up?

Marc

#### Yogi Anand

##### MrExcel MVP
mjansen said:
And then where would this data end up?

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

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

##### MrExcel MVP

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

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
Sorry for the typo in my preceding post -- where I said 'cell referencing' I meant to say 'circular referencing'.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,817
Messages
5,855,801
Members
431,765
Latest member
RedleoUK

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

### Which adblocker are you using?

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

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