average data from diff sheets

[shwethav@gmail.com]

need a quick formulae help from you in excel.





This is what am trying to do.



I have 2 sheets (A and B ) . From sheet B am taking the average of (A1:
B3) and want it in the A1 cell of sheet A. To put it concisely



sheet A



A1 Average (SheetB!(A1:B3))
B1 Average( SheetB!(C1:D3))



A2 Average (SheetB!(A4:B6))
B2 Average( SheetB!(C4:D6)) and so on.





sheet B has (60*48) cells



I want a common formulae or macro to do this but not sure how to go
about using offset or index functions for the same.



Will be helpful if you have some thoughts on this.

[Peo Sjoblom]

Please refrain from multiposting, post only in one newsgroup, it is really
looked down upon, it's better to crosspost since the post and answers are
visible

=AVERAGE(OFFSET(Sheet2!$A$1,ROWS($A$1:A1)*2-2,COLUMNS($A$1:A1)*2-2,2,2))

copied across and down
--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




<shwethav@gmail.com> wrote in message
news:1143672767.866593.221570@e56g2000cwe.googlegroups.com...
> need a quick formulae help from you in excel.
>
>
>
>
>
> This is what am trying to do.
>
>
>
> I have 2 sheets (A and B ) . From sheet B am taking the average of (A1:
> B3) and want it in the A1 cell of sheet A. To put it concisely
>
>
>
> sheet A
>
>
>
> A1 Average (SheetB!(A1:B3))
> B1 Average( SheetB!(C1:D3))
>
>
>
> A2 Average (SheetB!(A4:B6))
> B2 Average( SheetB!(C4:D6)) and so on.
>
>
>
>
>
> sheet B has (60*48) cells
>
>
>
> I want a common formulae or macro to do this but not sure how to go
> about using offset or index functions for the same.
>
>
>
> Will be helpful if you have some thoughts on this.
>

[Bob Phillips]

Try this

=AVERAGE(INDEX(Sheet2!$1:$100,(ROW()-1)*3+1,(COLUMN()-1)*2+1):INDEX(Sheet2!$
1:$100,ROW()*3,(COLUMN())*2))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

<shwethav@gmail.com> wrote in message
news:1143672767.866593.221570@e56g2000cwe.googlegroups.com...
> need a quick formulae help from you in excel.
>
>
>
>
>
> This is what am trying to do.
>
>
>
> I have 2 sheets (A and B ) . From sheet B am taking the average of (A1:
> B3) and want it in the A1 cell of sheet A. To put it concisely
>
>
>
> sheet A
>
>
>
> A1 Average (SheetB!(A1:B3))
> B1 Average( SheetB!(C1:D3))
>
>
>
> A2 Average (SheetB!(A4:B6))
> B2 Average( SheetB!(C4:D6)) and so on.
>
>
>
>
>
> sheet B has (60*48) cells
>
>
>
> I want a common formulae or macro to do this but not sure how to go
> about using offset or index functions for the same.
>
>
>
> Will be helpful if you have some thoughts on this.
>

[Domenic]

Assuming that Sheet2!A1:AV60 contains the data, try...

Sheet1!A1, copied down and across:

=AVERAGE(INDEX(Sheet2!$A$1:$AV$60,ROWS(A$1:A1)*3-3+1,COLUMNS($A1:A1)*2-2+
1):INDEX(Sheet2!$A$1:$AV60,ROWS(A$1:A1)*3,COLUMNS($A1:A1)*2))

Hope this helps!

In article <1143672767.866593.221570@e56g2000cwe.googlegroups.com>,
shwethav@gmail.com wrote:

> need a quick formulae help from you in excel.
>
>
>
>
>
> This is what am trying to do.
>
>
>
> I have 2 sheets (A and B ) . From sheet B am taking the average of (A1:
> B3) and want it in the A1 cell of sheet A. To put it concisely
>
>
>
> sheet A
>
>
>
> A1 Average (SheetB!(A1:B3))
> B1 Average( SheetB!(C1:D3))
>
>
>
> A2 Average (SheetB!(A4:B6))
> B2 Average( SheetB!(C4:D6)) and so on.
>
>
>
>
>
> sheet B has (60*48) cells
>
>
>
> I want a common formulae or macro to do this but not sure how to go
> about using offset or index functions for the same.
>
>
>
> Will be helpful if you have some thoughts on this.

[shwethav@gmail.com]

Thanks bob and other guys .. it works