(G&P']``'B)QL!_0`!XB<;`?T``>(G&P']``+B)QL!_0`!XB<;`?T`
M`>(G&P']``'B)QL!_0`!XB<;`?T``>(G&P']``'B)QL!_0`!XB<;`?T``>(G
M&P']``'B)QL!^P`!XB<;`?L``>(G&P'[``'B)QL!^P`!XB<;`?L``>(G&P'[
M``'B)QL!^P`!XB<;`?L``>(G&P'[``'B)QL!^P`!XB<;`?L``>(G&P'[``'B
M)QL!^P`!XB<;`?T``>(G&P'X``'B)QL![P`!XB=:`><``>(G&P'G``'B)QL!
MYP`!XB<;`><``>(G&P'G``'B)QL!YP`!XB<;`><``>(G&P'G``'B)QL!YP`!
MXB<;`0`````````'```F&0@G&0@H&0@I&0@`"```!0$F&0@G&0@H&0@I&0@`
M`@``!0,``0````(```4!*4@(``!)"```5P@``%@(``!9"```6@@``%L(``!<
M"```90@``&8(``!G"```(G&P'X``'B)UH!]0`!XB?K`/4``>(GZP#U``'B)^L`\P`!XB?K`.P`
M``````#J````````\P```````.P```````#J````````Z@```````/,`````
M``#S``'B)^L`\P``````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M``````````````````````````````````````````````$/```&#P`=8!K\
M_QL!`"4"``$````"```%`0`'```F&0@G&0@H&0@I&0@0#@`2``@``0!+``\`
M`````!H``$#Q_P(`&@`&3F]R;6%L``(````#`&$)"```````````````````
M```````B`$%`\O^A`"(`%D1E9F%U;'0@4&%R86=R87!H($9O;G0`````````
M`````"P`($`!`/(`+``&1F]O=&5R``P`#P`/"``"X!#`(0$""P!=`P!A"01B
M`6,8```8`"E`H@`!`1@`"U!A9V4@3G5M8F5R`````"``'T`!`!(!(``&2&5A
M9&5R``P`$0`/"``".1!R(`$"````````=@4```4`B`@`````_____P$`"B'_
M_Q0``````'8%``````````!``0``RP$``*D#``#'`P``$P0``!0$``#2!```
M104``$@%``!)!0``=@4````!XB<;`0`!XB<;`0`!XB<;`0`!XB<;`0`!XB<;
M`0`!XB=:`0`!XB<;`0`!XB=:`0`!XB?K`
``````````````+````*@```"T`
M`````P``M0@```4```,``$@(``"S"```!@`'```````'````"P```!(````5
M````+0```!,A%0`3(13_%8"C``Q!;F1Y($QI;F9O;W0@0SI<4U!54T52.35<
M5D],,DY/,EQ"04-+4$%'12Y$3T,':F%C:W-O;B!#.EQ34%5315(Y-EQ63TPS
M3D\Q7$)!0TM004=%+D1/0RQ38VAO;VP@;V8@16YG:6YE97)I;F<@26YF;W)M
M871I;VX@5&5C:&YO;&]G>1Q#.EQ34%5315(Y-EQ63TPS3D\Q7%-50E,N1$]#
M_T!(4"!,87-E`!46
MD`$``%1I;65S($YE=R!2;VUA;@`,%I`!`@!3>6UB;VP`"R:0`0``07)I86P`
M+`"0`0`33F5W($-E;G1U0````U3
+=6)S8W)I<'1I;VX`
`
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: HELGEJ@pfizer.com
Subject: New member
Date: Thu, 31 Oct 1996 16:47:02 -0500
Our company manufactures medical devices. During testing of our
products we generally use statistical methods to evaluate performance.
My main area of interest is Experimental Design.
My email address is: helgej@pfizer.com
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: srx033@coventry.ac.uk
Date: Fri, 1 Nov 1996 08:43:45 GMT
Subject: DESCRIBE BY
One of the features of Minitab that I feel is not satisfactorily replicated
in Excel is the DESCRIBE BY feature, i.e. the ability to calculate a whole
range of statistics at once for various obvious subsets of the data. OK
one can use a Pivot Table to obtain the count, mean and s.d. but not the
quartiles, trimmed mean, etc. The Data Filter is an excellent way of
obtaining the required subset, but any function calculated on the filtered
data automatically includes the hidden data as well.
What I do to get around this is to "unstack" data into different columns.
That is, if I have two columns of data, say Gender and Height, I create two
further columns Male and Female (assume I am using columns A,B,C,D).
In C2 I type the formula:
=IF($A2=C$1,$B2,"")
and fill down the rest of column C. This extracts all the male heights and
leaves the females blank. Copy across into column D and you get the female
heights and the male blanks. At the foot of column C you can then calculate
your various statistics on the males (the blanks are ignored, at least for
most of the simple statistics) then copy the formulae across into column
D for the female statistics.
This works fine, but it seems unnecessarily tortuous.
My questions are twofold:
(a) how do the rest of you do it?
(b) shouldn't we ask Microsoft to allow statistics to be calculated on
filtered data ignoring hidden rows?
Neville Hunt
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: John Kulig
Subject: new member
Date: Sat, 2 Nov 1996 09:37:54 -0500 (EST)
>Since this is a new list, members might wish to send a short message to
>the list to introduce themselves, giving an outline of their particular
>area of interest and any thoughts on how the the list should develop.
I use Excel as a research tool to organize and describe data sets
(my current area of research is in social cognition - specifically
peoples estimates of the prevalence of health relevant behaviors in the
population - smoking, drinking, driving habits and so forth. I am
investigating the extent to which these estimates are influenced by social
factors such as whether people themselves engage in these behaviors, as
well as whether friends engage in them).
I also teach undergraduate research/statistics courses that
utilize Excel.
Finally, I have written macro programs that organize and graph
on-line laboratory data (operant chamber data with rats).
--
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: stephen pollard 02-20-91
Date: Nov 02, 1996 10:07:53
Subject: Re: DESCRIBE BY
In-Reply-To: DESCRIBE BY, From: srx033@coventry.ac.
I do the following.Use the data filter to create my subset of data then
copy this subset to a new work sheet. Then analyze the data.
Steve Pollard
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 4 Nov 1996 09:07:48 +1100 (EST)
From: Rodney Carr
Subject: Re: DESCRIBE BY
At 08:43 AM 1/11/96 GMT, srx033@coventry.ac.uk wrote:
>
>One of the features of Minitab that I feel is not satisfactorily replicated
>in Excel is the DESCRIBE BY feature, i.e. the ability to calculate a whole
>range of statistics at once for various obvious subsets of the data. OK
>one can use a Pivot Table to obtain the count, mean and s.d. but not the
>quartiles, trimmed mean, etc. The Data Filter is an excellent way of
>obtaining the required subset, but any function calculated on the filtered
>data automatically includes the hidden data as well.
>
>What I do to get around this is to "unstack" data into different columns.
>
>That is, if I have two columns of data, say Gender and Height, I create two
>further columns Male and Female (assume I am using columns A,B,C,D).
>In C2 I type the formula:
>
> =IF($A2=C$1,$B2,"")
>
>and fill down the rest of column C. This extracts all the male heights and
>leaves the females blank. Copy across into column D and you get the female
>heights and the male blanks. At the foot of column C you can then calculate
>your various statistics on the males (the blanks are ignored, at least for
>most of the simple statistics) then copy the formulae across into column
>D for the female statistics.
>
>This works fine, but it seems unnecessarily tortuous.
>
>My questions are twofold:
>
>(a) how do the rest of you do it?
Neville,
If you just want the count, mean and standard deviation you can use the
database functions DCOUNT, DAVERAGE and DSTDEV. There is no DMEDIAN, etc -
in XLSTATS I work them out after unstacking the way you've done it. The only
slight modification is to use a macro that will do the filling down
automatically.
By-the-way, I've figured out a way of drawing boxplots showing outliers (the
way Minitab does it) automatically from raw data. The working is not a
pretty sight (!) - it's in Version 3 of XLSTATS (due for release in December).
>(b) shouldn't we ask Microsoft to allow statistics to be calculated on
> filtered data ignoring hidden rows?
That would be a nice solution!
Rodney
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 04 Nov 1996 13:22:15 +0100
From: Michael Mittag
Subject: Re: DESCRIBE BY
>The Data Filter is an excellent way of
>obtaining the required subset, but any function calculated on the filtered
>data automatically includes the hidden data as well.
Hint: there's a function called
SUBTOTAL or something very much like it (TEILERGEBNIS in german, anyway)
which only works on visible cells. It includes an argument where you can
specify what type of subtotal you'd like, like 1 for mean, 5 for max or so.
Well, it's not really very intuitive or anything, and I found it by
coincidence only.
Also, there's some icon which you can place on the bar to select the visible
cells only, but I shied from using it so far since it results in quite a
large address of the thing.
I know it does not really solve the problem in question, just thought I mention it.
Michael
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Wed, 6 Nov 1996 08:45:58 +0000 (GMT)
From: "N. Hunt"
Subject: Re: DESCRIBE BY
On Mon, 4 Nov 1996, Michael Mittag wrote:
> Hint: there's a function called
> SUBTOTAL or something very much like it (TEILERGEBNIS in german, anyway)
> which only works on visible cells. It includes an argument where you can
> specify what type of subtotal you'd like, like 1 for mean, 5 for max or so.
> Well, it's not really very intuitive or anything, and I found it by
> coincidence only.
> Also, there's some icon which you can place on the bar to select the visible
> cells only, but I shied from using it so far since it results in quite a
> large address of the thing.
> I know it does not really solve the problem in question, just thought I
> mention it.
Yes, this is a good facility. What we need is for Microsoft to include
more statistics in the list of summary options - at present it offers
COUNT, SUM, AVERAGE, MIN, MAX, STDEV, STDEVP, VAR, VARP. Why not include
the quartiles instead of the last three (which can easily be derived from
STDEV anyway)? This would solve the whole problem.
Neville Hunt
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Wed, 06 Nov 1996 12:12:06 +0100
From: Michael Mittag
Subject: Introducing myself
Hello everyone!
I'm a student of psychology at Basel, Switzerland. I do a lot
of statistics for all kind of projects, and I mainly use
EXCEL as far as possible, then SPSS. On occasion BMDP.
So much for myself.
I also have a few addings to the good/bad/desirable list:
I'd suggest a category called "ambiguous", featuring:
Excel works with Word, but there's bugs around. On an up-to-date,
standard PC resizing figures usually does not work correctly,
I had occasional crashes of Excel while I was operating a figure
in Word plus a few minor things.
There's a tool for 2-factor ANOVA, but I could not find any
reasonable documentation in the program (I guess I'll find one on
the net, but I think there should be one in the help files).
As for the bad stuff:
Last time I tried printing a large report (70 pages, 15 or so figures),
printing from Word (95) on a new Canon printer, the grayscales printed
were entirely different from what was on screen, and the whole thing
further depended on printing quality.
As for the desirable stuff:
Some more output managment. I'd like to tell the thing to use exactly
these two colors in any figure with two colors, those three in figures
with three and so on. So when I change my mind about what colors or greys
to use, that happens immediately. Also, when my printer messes up, this
is easily corrected.
In the table auto-format a scientific table option. (As for our science,
it's horizontal lines only).
As for the good stuff:
I think it's generally a very good product, and usually very easy to handle,
with good help and everything. It's a bit of a shame that exactly where it
can really hurt you (finishing some overdue project with the last of your
breath), it does.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: J.Deely@math.canterbury.ac.nz
Date: Sat, 09 Nov 1996 13:11:33 +0000
Subject: Re: DESCRIBE BY
neville, if i understand u correctly u have 2 colums and u want to
do separate statistics on column 2( height) partitioned by values in
col 1 (gender)
my solution is to type" gender" in C1 and put ="M" in C2 assuming
your gender column is in the form of "M" and "F"; ( if numerical
coding for gender do the obvious)
copy "height" into D1 then
i go to the menu DATA , FILTER, ADVANCED FILTER and use the " copy
to another location" option and type in A1:B100 for the list range
assuming the data ends at row 100; put C1:C2 for the criteria range;
put D1 for the copy to range; then click OK and bob's your uncle.
because in colum D u now have ONLY male heights, no blanks or
anything untoward
maybe i misunderstood the question.
cheers for now
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 11 Nov 1996 09:45:42 +0000 (GMT)
From: "N. Hunt"
Subject: Re: DESCRIBE BY
On Sat, 9 Nov 1996 J.Deely@math.canterbury.ac.nz wrote:
> Neville, if I understand you correctly you have 2 columns and you want to
> do separate statistics on column 2( height) partitioned by values in
> col 1 (gender). My solution is to type" gender" in C1 and put ="M" in
> C2 assuming your gender column is in the form of "M" and "F";
> ( if numerical coding for gender do the obvious)
> copy "height" into D1 then
> I go to the menu DATA , FILTER, ADVANCED FILTER and use the " copy
> to another location" option and type in A1:B100 for the list range
> assuming the data ends at row 100; put C1:C2 for the criteria range;
> put D1 for the copy to range; then click OK and bob's your uncle.
> In column D you now have ONLY male heights, no blanks or anything untoward
Yes, that solves the problem. Having done the males, type "=F" in C2 and
repeat the process to stick the female heights in column E. I still think
the best solution is for DATA SUBTOTALS to be improved by Microsoft.
Neville
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: Garry Dickinson
Date: 12 Nov 96 8:37:48
Subject: New member information
Someone suggested it would be a good idea for people joining the group to
outline briefly their interests relating to Excel. So here's my contribution.
Statistics New Zealand is the central statistical agency of the NZ
government. It has about 700 staff collecting, processing, analysing and
distributing a wide range of economic and social statistics. Excel is used
as one of a range of software tools; others include SAS, Splus and more
specialised products. (Data conversion between products is therefore of
importance). For many of the staff Excel is the primary analysis tool
available.
I'm one of a group of 25 or so mathematical statisticians. We work mainly on
the design of surveys and the subsequent analysis of data from them. For this
group Excel is used for simple sorts of analysis, and for graphical
presentation. There is among the group some suspicion about the validity and
accuracy of the more statistical parts of Excel.
I also run a (small) private statistical consulting business and use Excel
extensively for that.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: FUSSEY BERYL K
Date: Tue, 19 Nov 1996 08:57:27 GMT
Subject: Re: DESCRIBE BY
>
> Subject: Re: DESCRIBE BY
Beryl Fussey writes:
> Yes I do exactly what J Deely does but cannot send the filtered
> data to a new worksheet as you(Neville) originally implied.It keeps telling me
> I cannot do this. Do you know a trick I don't?
> it would be nice when dealing with large data sets to be able to do this
> rather than cut and paste into a new sheet.
Neville Hunt replies:
> I will have to try this out. I would have thought you simply typed
> Sheet2!a1:b100 in Deely's instructions?
Beryl's reply:
I had already tried this but Excel tells me I can only copy filtered
data to an active sheet. Any suggestions please?
>
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Wed, 20 Nov 1996 13:38:38 +0100
From: Michael Mittag
Subject: Re: DESCRIBE BY
>Beryl's reply:
>I had already tried this but Excel tells me I can only copy filtered
>data to an active sheet. Any suggestions please?
I don't know if it helps, but there's a button which lets you mark
visible cells only. The only way to reach this function, as far as
I know, is to customize your icon bar and put it up.
Michael
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: "Callender, John T"
Date: Fri, 22 Nov 96 11:02:00 GMT
>From the amount of E-mail I now get from Assume, the interest in Excel as a
tool for Statistical analysis is growing - and quite rightly so.
The recent messages are in areas that I am not directly involved in, at the
moment at least. As I teach only 2nd year undergraduate Engineers, my time
is devoted to developing Excel in the areas of Probability, Sampling
distributions, Design and Analysis of Experiments, etc. It does not leave a
lot of time to investigate the ideas from Assume.
Is it time we had another Assume day as organised by Neville Hunt at
Nottingham last Summer? This Easter? This time we could have speakers on a
variety of topics in Statistics by Excel?
If you agree with me, perhaps you could let Neville know - I'm sure he would
organise it.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: FUSSEY BERYL K
Date: Mon, 25 Nov 1996 09:41:25 GMT
Subject: Re: next assume meeting
What a good idea. I have sent a reply to Neville endorsing this.
Easter seems a good time. I teach 2nd year Biostatistics and 2nd year
Sports Science and am experimenting in transferring data from
hundreds of questionnaires in to Excel and then graphing it. Would like to do a
quick chi-squared, not found a quick way yet. Beryl Fussey
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: srx033@coventry.ac.uk
Date: Mon, 25 Nov 1996 13:31:07 GMT
Subject: ANNUAL MEETING
By popular request (!) I am happy to organise another ASSUME meeting
next Easter. The Midlands seems a good compromise for a venue and
I am happy to hold it at Coventry if folk would like a change of
scenery. Our Easter Vacation (so called) is from 23 March 1997 to
11 April. Last year Wednesday seemed to be a popular day, avoiding
Fridays because of the increased cost of rail fares. That would
suggest 26 March, 2 April and 9 April. Maybe other institutions
have a different vacation period? How about folk in the "real"
world - is it best to avoid the Easter fortnight?
I have some ideas for speakers, but anyone with anything to say is
welcome to volunteer.
Please send some feedback to my personal email address
dnhunt@coventry.ac.uk
rather than clogging up the list.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: Paul Barnwell
Subject: Real and imaginary numbers in Excel
Date: Thu, 28 Nov 1996 01:26:37 -0800
I have come across no easy way to split the real and imaginary numbers
directly in Excel. I do not claim to know anything about FFT but I have
written two Functions that allow you strip the Real and Imaginary parts
of a FFT result cell. They are as follows (I have to include the
disclaimer to cover myself legally);
Microsoft provides examples of Visual Basic procedures for illustration
only, without warranty either expressed or implied, including but not
limited to the implied warranties of merchantability and/or fitness for
a particular purpose. This Visual Basic procedure is provided 'as is'
and Microsoft does not guarantee that it can be used in all situations.
Microsoft does not support modifications of this procedure to suit
customer requirements for a particular purpose.
Function FFTReal(InputCell As Object)
Application.Volatile
I = InStr(InputCell, "i")
If I > 0 Then
plus = InStr(InputCell, "+")
If plus > 0 Then
FFTReal = Left(InputCell, plus - 1)
Else
minus = InStr(2, InputCell, "-")
If minus > 0 Then
FFTReal = Left(InputCell, minus - 1)
Else
FFTReal = ""
End If
End If
Else
FFTReal = InputCell
End If
End Function
Function FFTImaginary(InputCell As Object)
Application.Volatile
I = InStr(InputCell, "i")
If I > 0 Then
plus = InStr(InputCell, "+")
If plus > 0 Then
FFTImaginary = Mid(InputCell, plus + 1)
Else
minus = InStr(2, InputCell, "-")
If minus > 0 Then
FFTImaginary = Mid(InputCell, minus + 1)
Else
FFTImaginary = InputCell
End If
End If
Else
FFTImaginary = ""
End If
End Function
Put these two functions in to a module sheet in your workbook (or
Personal.xls if you want them globally available). You will then get
them showing in the User Defined section of the Function Wizard. You
can then generate two additional columns, one with Real numbers and the
other with the imaginary ones. I have tried to test them for all
combinations I am aware of but you may need to tweek them if there is a
problem.
Hope this Helps
Paul Barnwell
Microsoft Technical Support
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: srx033@coventry.ac.uk
Date: Thu, 5 Dec 1996 14:47:13 GMT
Subject: WEB PAGE
We now have an ASSUME web page set up for us by MAILBASE. It is located
at:
http://www.mailbase.ac.uk/lists/assume/
I have placed some text files on this page. If anyone has ideas regarding
other material that should be posted there, please let me know.
Neville Hunt
ASSUME list owner
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: "Jackson, Roger"
Subject: Spreadsheet User
Date: Fri, 06 Dec 96 12:55:00 GMT
Dear All
Spreadsheet User now has its own web page containing details of all back
issues. As well as details of the the latest issue, one of the papers is
available for readers to down load. The page also contains a subscription
form the can also be down loaded.
The full address of the page is:
http://www.shu.ac.uk/schools/sci/maths/ssuser/index.htm
As well as welcoming new subscribers I would greatly appreciate receiving
articles on novel spreadsheet applications for consideration for
publication.
Yours sincerely
Roger Jackson
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 09 Dec 1996 13:49:04 +0100
From: Michael Mittag
Subject: Re: Spreadsheet User Home Page
Hello!
Just a short one I discovered:
The function ISERROR(expr) (or anyway the english equivalent to
the german ISTFEHLER) lets you check whether the expression
delivers an error message.
Use:
= IF(ISERROR(expr);"";1)
gives 1 if expr is computable.
= IF(ISERROR(expr);"";expr)
gives the expression if computable.
(no more nasty error messages througout my tables...)
Michael Mittag
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 9 Dec 1996 13:54:27 +0000 (GMT)
From: "R. Allan Reese"
Subject: Re: Spreadsheet User Home Page
On Mon, 9 Dec 1996, Michael Mittag wrote:
> Use:
> = IF(ISERROR(expr);"";1)
> gives 1 if expr is computable.
>
> = IF(ISERROR(expr);"";expr)
> gives the expression if computable.
>
>
> (no more nasty error messages througout my tables...)
Before anyone gets carried away, a reminder that you have to do something
sensible when the expression is not computable. We once had a student
whose program output negative sums of squares. After some months,
someone asked her how she had taken the square roots to get SDs. "Oh, I
just ignored the minus signs."
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 9 Dec 1996 16:13:07 +0000 (GMT)
From: "R. Allan Reese"
Subject: Latin Names (of birds) in messages (fwd)
Here's a handy spreadsheet for afficionadoes of this list.
---------- Forwarded message ----------
Date: Sun, 8 Dec 1996 11:21:17 -0600
From: David Sarkozi
To: BIRDCHAT@LISTSERV.ARIZONA.EDU
Subject: Latin Names in messages
It has been a while since I have offered my latin name lookup speadsheet to
the list. This is the speadsheet I use to insert latin names in the RBA's I
transcibe. It is in Excel 5.0 but I'm willing to convert it to a few other
formats (Lotus and Quatro I've done) if you need these formats or an older
version of Excel contact me directly.
Instructions on how to use it are contained in the sheet itself. This
version has the latest ABA checklist changes, plus a field that the user can
fill in for their own used, such as review species or state checklist, etc.
It can be downloaded directly from my Webpage,
http://www.infocom.net/~dsarkozi
If this does not work for you contact me directly and I can send it attached
to e-mail. Please specify if you need the file UUEncoded.
If you do download it from the Webpage I'd like to get a note from you, I'm
curious how many people are interested, last I did this sent out about 150
copies by e-mail.
---------------------------------
David Sarkozi, WB5N
Houston, TX
dsarkozi@infocom.net
see my Web Page "Birds of the Upper Texas Coast"
http://www.infocom.net/~dsarkozi/
(713) 520-5906
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: Guido.Wyseure@agr.kuleuven.ac.be
Date: Wed, 11 Dec 96 10:09:49 CET
Subject: Round circles
Dear spreadsheet users,
As I am developing spreadsheets for different applications in teaching, one
is the circle of Mohr in Solid mechanics, I have not discovered how to make
the circle round in an automatic way.
One way would be by Macro (but that has to be invoked by the user), this is my
last resort.
It is not possible to use fixed scales as the possible values differ several
orders of magnitude.
Ideally, I would enter cell-addresses in the scale-page of the axis format,
but that seems to be impossible.
Maybe it good to the students to learn them the automatic scale problem but it
looks ugly to see circles which are for from round.
Thanks and regards,
Guido
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Wed, 11 Dec 1996 12:22:16 +0000 (GMT)
From: "N. Hunt"
Subject: Re: Round circles
On Wed, 11 Dec 1996 Guido.Wyseure@agr.kuleuven.ac.be wrote:
> Ideally, I would enter cell-addresses in the scale-page of the axis format,
> but that seems to be impossible.
Yes, it is very illogical that you cannot enter variable axis formats,
given that it is possible to enter variable TITLES on charts!
Which is more important, the title or the scale?
Neville Hunt
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: srx033@coventry.ac.uk
Date: Thu, 30 Jan 1997 13:58:33 GMT
Subject: ANNUAL MEETING
Dear UK members
I am pleased to confirm the arrangements for the 1997 meeting of ASSUME.
It will be held in the Harmer Building (Room 2503) of Sheffield Hallam
University from 11.00 until 4.00. Speakers include:
Paul Barnwell Microsoft Excel97
Steven Walton Unistat Unistat as an Excel add-in
Joanna Tidball CLUES Creating CAL materials
Katrina Todd Pfizer
Neville Hunt Coventry Unbalanced ANOVA in Excel
John Callender Hallam Probability in Excel
To avoid charging a "conference fee" lunch is not provided, but John
Callender is making arrangements for us to eat at a nearby pub.
Would all those wishing to attend please let me know by email. Please
reply to: dnhunt@coventry.ac.uk NOT the ASSUME list, to avoid
clogging up members' mailboxes.
I will send travel details to all those planning to attend. Hallam
University is ideally situated next to the railway station.
I look forward to hearing from you.
Neville Hunt
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Thu, 13 Feb 1997 15:46:30 +0000 (GMT)
From: "R. Allan Reese"
Subject: Excel stats wizard
A student had great difficulty setting up a frequency table. When you
start the function wizard it brings up the list of functions and then a
dialogue box for inserting the ranges of operands. At that pooint it also
shows the result as a value array. Click on Finish ... and all that appears
in the result cell is a single number - 0 in our case. It took
considerable probing and experiment to find that the wizard had generated
an "array function" that had to be further highlighted and copied down the
result array using Crtl/Shift/Enter.
Is this a general experience, or were we being thick?
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Thu, 13 Feb 1997 16:34:03 +0000 (GMT)
From: "N. Hunt"
Subject: Re: Excel stats wizard
I have had this problem too. In fact students do not seem to cope very
well with array formulae. I am more inclined now to use COUNTIF to
get the cumulative frequency distribution, then use subtraction to
get the class frequencies. I cannot persuade COUNTIF to accept an
OR or AND condition - has anyone else managed this?
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: stephen pollard 02-20-91
Date: Feb 13, 1997 15:09:59
To: "N. Hunt" , assume@mailbase.ac.uk
Subject: Re: Excel stats wizard
In-Reply-To: Re: Excel stats wizard, From: "N. Hunt"
Subject: RE: Excel stats wizard
Date: Fri, 14 Feb 1997 00:52:54 -0800
There does not seem to be a way to get Countif to accept more than one
criteria. The simplest way to get round it is to use an array function
such as
{=SUM(IF(A1:A10=1,1,IF(A1:A10=3,1,0)))}
to count all the values in range a1:a10 that match either 1 or 3.
Alternatively you could look at using DCount which allows you to setup a
range of cells that contains the criteria you are testing for. This is
more complex to setup but if you are testing for lots of criteria it
maybe the only way of doing it as you are only permitted 7 levels of
nested If statements.
Hope this helps
Paul Barnwell
PSS UK - VBA Team
http://ltdvba - VBA 5.0 Examples and more...
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: "Callender, John T"
Subject: Arrays
Date: Fri, 14 Feb 97 15:05:00 GMT
Problems with the frequency function? My Engineering undergraduates have
more difficulty in determining appropriate bin limits than entering the
frequency function as an array.
Possibly this is because we use the matrix functions quite a lot (Eigen
values/vectors, etc.) and all of these have to be entered as arrays. As a
consequence, they are quite familiar with array functions. The suggestions
for Countif or for determining the cumulative freq. distributions will not
apply to matrix arrays.
It is not hard to get students to understand about arrays - easier than in
the Fortran, Basic, ...computer language days where you had to use Dimension
or Dim statements. For the latter, they thought you were becoming personal!
The most basic use of a spreadsheet, I believe, needs an understanding of
the difference between relative cell addresses (Why, in macro's, do you have
to the cell address of C5,say, as cells(5,3) [plural] and not cell(3,5)
[singular]?) and fixed, or partially fixed , cell addresses. Entering the
frequency function into the first cell and dragging down will soon show the
need for fixed references to the data array and the bin array. Now dragging
down shows you only repeat the first calculation and hence the need for a
different procedure (control/shift and then enter) for entering an array.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: Jonathan Moss
Date: 14 Feb 97 11:14:32
Subject: RE: Excel stats wizard
I have another titbit to add for the whole group that kind of slipped my mind
till now.
You can use the formula N() to get a sum of a set of values in an array. The
process is given in the example below.
Example:
Assume you have an array a1:a10 with values. You will need a second column of
1's of the same size to do this method as typed. Assume the 1's are in b1:b10.
1. Now you want to count the occurrences of 1's and 3's in a1:a10. The formula
for this is
SUMPRODUCT(N(A1:A10=1),B1:B10)+SUMPRODUCT(N(A1:A10=3),B1:B10).
2. For completeness, you also want the number of occurrences of values greater
than or equal to 5. The formula is
SUMPRODUCT(N(A1:A10>=5),B1:B10)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 17 Feb 1997 11:01:38 +1100
From: Rodney Carr
Subject: Re: Excel stats wizard
Dear Allan,
You might be interested in having a look at XLSTATS, it will produce
frequency tables, together will the most commonly-used analysis,
automatically, as soon as the data is entered (there are workbooks for
different combinations of types of variables). A demonstration version of
XLSTATS is available from my www page (address below)
Rodney
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Date: Mon, 17 Feb 1997 11:11:58 +1100
From: Rodney Carr
Subject: Re: Arrays
I really doubt if most undergraduates can every really figure out how to use
array functions without a lot of help! Same goes with many other (especially
statistical) functions in Excel. Even putting error bars on graphs can be a
problem! To cure this (for statsistics) I've put together a set of
workbooks that each handle a different number of variables of different
types. The package - XLSTATS - is becomming quite popular amoung many of my
colleages here at Deakin university and at secondary colleges around the
state. If you would like to see a demonstartion version of the package it is
available from my www page (address below).
Rodney
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: "Mary Jackson"
Date: Mon, 17 Feb 1997 09:58:23 UTC
Subject: Re: Excel stats wizard
The crucial thing about entering array formulae is to decide the
intended array dimensions in advance
eg. in the case of the FREQUENCY function, decide how many cells are
to receive frequency expressions and 'select' the appropriate range
of cells. Then click the function dictionary, choose the FREQUENCY
function, enter the arguments, 'finish' with the function wizard and
with the array formulae still poised on the entry line, press
Ctrl-Shift-Enter (instead of simply Enter).
If you get nonsense, or the entry in a single cell, make certain
the appropriate range of cells is selected, press F2 for 'edit' and
correct the array formulae entry on the edit line, then
Ctrl-Shift-Enter once again.
Hope this makes sense!
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From land3902@mstr.hgc.edu Wed Mar 5 05:14:21 1997
Received: from mstr.hgc.edu by naga.mailbase.ac.uk id
(8.7.x for naga.mailbase.ac.uk) with SMTP; Wed, 5 Mar 1997 05:14:18 GMT
Received: from dialup1.hgc.edu (sd2.hgc.edu) by mstr.hgc.edu (5.67a/IDA-1.5/HGC-1.10)
id AA07156; Wed, 5 Mar 1997 00:14:18 -0500
Received: from localhost by dialup1.hgc.edu (SMI-8.6/SMI-SVR4)
id AAA28179; Wed, 5 Mar 1997 00:14:19 -0500
Date: Wed, 5 Mar 1997 00:14:18 -0500 (EST)
From: Stephen Landry
To: assume@mailbase.ac.uk
Subject: Forwarded mail: normal distribution
Message-Id:
Mime-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-List: assume@mailbase.ac.uk
X-Unsub: To leave, send text 'leave assume'
to mailbase@mailbase.ac.uk
Reply-To: assume@mailbase.ac.uk
Sender: assume-request@mailbase.ac.uk
Precedence: list
Please respond to Magued, not myself.
Steve Landry
---------- Forwarded message ----------
Date: Tue, 4 Mar 1997 01:29:30 -0500
From: Magued I Osman
To: Multiple recipients of list
From: Magued Osman,
Department of Statistics, Emirates University.
Does any one know what approximation EXCEL uses to calculate the F(x ;0,1)
for the normal distribution. Your help will be mostly valuable.
Magued Osman.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: "Premlin Pillay"
To: assume@mailbase.ac.uk
Date: Fri, 7 Mar 1997 14:27:10 +0200
Greetings all
I am a statistician, working for the Department of Health in Northern
Cape Province, South Africa. I joined initially as a statistician
but have since been co-opted to do just about anything information or
computer related.
Our province is huge (one third of South Africa) but has only one
percent of the population so we have a very small budget. Our
personnel is therefore small. My directorate thus handles
everything related to information technology, information systems,
biostatistics, epidemiology and research for the directorates of
health, welfare and environmental affairs.
Much of my work involving statistical analyses is done on epi-info
and excel. I find excel meets all my needs and have been persuading
other staff members to give it a try.
I hope the traffic increases though as I find the topics and
discussions very interesting.
Kind regards
Premlin Pillay
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
From: srx033@coventry.ac.uk
Date: Thu, 27 Mar 1997 11:44:39 GMT
Subject: REPORT ON ASSUME97 MEETING
The second annual meeting of the Association of Statistics Specialists
Using Microsoft Excel took place at Sheffield Hallam University on
Wednesday 26th March with a good mix of academics and industrial
representatives present. Neville Hunt (Coventry University) began
proceedings with a report on the first year of ASSUME's activities
including the setting up of an email discussion list and associated website.
In the enforced absence through bereavement of the keynote speaker
from Microsoft, Tony Greenfield (Derbyshire) stepped into the breach
to give a user's perspective on Excel97. Despite ASSUME's lobbying
of Microsoft there are no improvements in the statistical functionality of
Excel97. Changes are largely related to the user interface - for example,
an automatic correction facility when entering formulae. Tony
suggested that Microsoft was more likely to respond to 100
individual complaints from ASSUME members than to a single representation
made on the group's behalf. In the resulting discussion it was
suggested that the group liaises with other bodies such as the ASA
to increase pressure on Microsoft.
Due to the late arrival of another speaker due to the bomb-scare at
Doncaster, Neville Hunt was promoted up the order to bat second. He
showed how a full analysis of variance could be conducted for an
unbalanced block experiment using nothing more than Excel's COUNT,
AVERAGE and SUMSQ functions.
Following lengthy discussions over lunch, members were treated to a
demonstration of Unistat by Dr Mehmet Toker (Unistat Limited).
Although Unistat is a comprehensive stand-alone package it can be used
as an Excel add-in, with an option for formatted output to be sent
directly to Word. The range of chart options is particularly
impressive, with a full graphic editing facility available.
An extremely cheerful John Callender (Sheffield Hallam - just retired!)
then gave an animated demonstration of how probability and the Central
Limit Theorem can be taught using spreadsheets. It was generally
agreed that John was the fastest macro writer in town!
Katrina Todd (Pfizer Central Research) described the FIT CURVE add-in
produced at Pfizer to enable biologists to model dose response data
in the same Excel environment used to record the experimental data.
FIT CURVE provides all the user-friendliness of an Excel "wizard",
backed up by all the algorithmic competence of NAG, who provide
the model-fitting subroutines - the perfect marriage?
A rather full day was brought to a close by Joanna Tidball (Aberdeen
University) who reported on the LoCAL project which aims to provide
teachers in all subjects with the tools to produce their own CAL
materials in Excel. Joanna presented several case studies produced
by other higher education lecturers, as well as her own excellent
self-teaching tutorial. The cases included a "black-box" package where
students have no direct interaction with the spreadsheet, a fixed
exercise where students enter data or formulae in certain cells but
much of the spreadsheet remains hidden, and a problem-solving unit
where students are provided with a calculation "toolkit" but
have to construct the solution themselvesUse of uninitialized value in concatenation (.) or string at E:\listplex\SYSTEM\SCRIPTS\filearea.cgi line 455, line 2666.
. All the case studies
produced through the project will be available on the web later this
year.
Neville Hunt
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%