[AccessD] average in matrix

Hello Gustav,

I don't understand what you mean by:  "as all cells except those (in your example) containing 1 are counted twice."   Why are the 1's counted twice.

Can you explain how to do this in excel with a routine. I have several matrixes with about 100 samples. To do that by hand (even per sample) costs me a lot of time.

Pedro Janssen




In antwoord op:

> From: "Gustav Brock" <Gustav at cactus.dk>
> To: <accessd at databaseadvisors.com>
> Date: Thu, 06 Jan 2005 11:54:01 +0100
> Subject: Re: [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).
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>