[AccessD] average in matrix

Hi Pedro

All cells within the Named Range of your spreadsheet must be empty or
contain numbers only.
If you receive #Num! for some cells, those cells contain text. That's a
no-no.

The query will give you the average of all cells if they all contain
zero or a number.
I don't think SQL is useful for the strange calculation you request.
I would do that in VBA - read in the recordset, then move right and
down as to your rules.

/gustav

>>> pedro at plex.nl 05-01-2005 20:31:59 >>>
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.