[AccessD] average in matrix

Hi Pedro

OK, that explains.
The calculation can be easily done in Excel as well as with VBA in
Access.
The "strange" part is that the calculation is not a straight average of
the matrix as all cells except those (in your example) containing 1 are
counted twice.

/gustav

>>> pedro at plex.nl 05-01-2005 23:40:25 >>>
Hello Gustav,

In the cells, there was no text present, but after making the format of
the
cells, Number (all cells were general), i saw all data in the linked
excel
range. Strange that it is seen an text from column/row 26 (column AA
in
Excel).

You were right saying that the query gave the average of all the cells
and
not only the last row. I don't know why i said that.

I will ask at a excel group if this "strange calculation" (its a matrix
for
result of genetic DNA samples. The values give the differences between
a
sample according to the other samples) can be done in Excel.

Thanks for your help.


Pedro Janssen


----- Original Message -----
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Wednesday, January 05, 2005 9:30 PM
Subject: Re: [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).