[AccessD] average in matrix

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).
> >
> > /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.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>