[AccessD] average in matrix

Hello Gustav,

your explanation, doesn't give me the result that i want.
This only gives the average of the last row .
I will explain what i want by an example.
Also the linked xls range gives #Num! in al fields beyond column F25 and
beyond row25. I can't link more Columns and rows??

Example
i have

      A     B     C     D
A   1
B   2       1
C   3      5      1
D  4       8     3      1

I want

Avg[A] = (1+2+3+4)/4
Avg[B] = (2+1+5+8)/4
Avg[C] = (3+5+1+3)/4
Avg[D] = (4+8+3+1)/4

Is this possible in Access or otherwise in Excel


Pedro Janssen


----- Original Message -----
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Wednesday, January 05, 2005 10:11 AM
Subject: Re: [AccessD] average in matrix


> Hi Pedro
>
> In Access you can link to the Excel workbook.
>
> In the workbook, create a Named Range which exactly contains the
> matrix. Save the file.
> In Access, attach the workbook, choose Named Range and not Worksheet.
> Pick your Named Range.
>
> Now, create a query like this:
>
>   SELECT
>     Avg(([F1]+[F2]+[F3])/3) AS AvgMatrix
>   FROM
>     xlsMatrixTest;
>
> where you, of course, will need to adjust the number of fields
> (columns, here 3) and the included fields (F1 to Fn) and the name of the
> linked Excel range (xlsMatrixTest).
>
> /gustav
>
> >>> pedro at plex.nl 05-01-2005 08:54:22 >>>
> Hello Group,
>
> i have a matrix in Excel and would like the average of all the values
> in the fields. Kan this been done with access or is it better to do this
> in Excel, in a way that i don't have to change formula for each field
> for the horizontal values and vertical values.
>
> Pedro Janssen
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>