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