[AccessD] average in matrix

Nicholson, Karen cyx5 at cdc.gov
Thu Jan 6 07:37:59 CST 2005


No way.  No mercy by us overbearing Americans!!!

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules,
Mark
Sent: Thursday, January 06, 2005 8:33 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] average in matrix


Dear "Grammer" (sic) (...LOL!!!) Lady,

You are commenting on a global conversation between people of disparate
countries...a little latitude should be granted within the confines of
an
informal discussion group, where English may be a second or third
language.

Mark



-----Original Message-----
From: Nicholson, Karen [mailto:cyx5 at cdc.gov] 
Sent: Thursday, January 06, 2005 7:57 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] average in matrix


Grammer Lady Check.  The plural of matrix is matrices.  If one googles
it, there is an interesting group affiliated with that word, of course,
the group does not interest me.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pedro at plex.nl
Sent: Thursday, January 06, 2005 8:51 AM
To: accessd at databaseadvisors.com
Subject: Re: [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
> 
> 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list