ASSUME Archive File: October 1996 to May 1997 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Date: Tue, 29 Oct 1996 10:06:39 +0000 (GMT) From: "N. Hunt" Subject: CAL in Excel I have recently acquired an excellent publication called: An introduction to creating CAL courseware with Microsoft Excel 5 produced by Joanna Tidball at University of Aberdeen, Scotland. It is a 100 page tutorial which teaches you all the tricks of the trade needed to produce very professional looking Excel interactive worksheets for teaching purposes. Among the things I have learned is how to use sliders and spinners for adjusting parameters (rather than entering direct from the keyboard), how to have "hot" words onscreen which are linked to help messages, etc, etc. Although the tutorial context is "Using the Normal Distribution" the ideas can be applied to other statistical topics or to completely different subject areas. Having produced a great deal of CAL materials in Excel myself I (rather arrogantly) did not expect to learn much from this publication - but I did! For further details, contact: CLUES@aberdeen.ac.uk The cost was about 28 pounds I think. CLUES also produce a similar tutorial on how to construct your own Windows Help (.HLP) files. People who have tried to do this using the Help Compiler tell me it is a horrendous task, but CLUES have found a way through the morass! Again, I highly recommend it. Neville Hunt %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Date: Wed, 30 Oct 1996 09:14:51 +1300 From: Murray Jorgensen Subject: Archives, I assume? Does the assume list have any archives? If so, how do I search them? %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Date: Wed, 30 Oct 1996 09:03:46 +0000 (GMT) From: "N. Hunt" Subject: Re: Archives, I assume? On Wed, 30 Oct 1996, Murray Jorgensen wrote: > Does the assume list have any archives? If so, how do I search them? > Have a look at our ASSUME website at: http://www.mailbase.ac.uk/lists/assume This contains searchable archives for the past two years. There are also edited highlights produced by the list owner under "Files added by List Owner". %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: PAUL SEED To: ASSUME@MAILBASE.AC.UK CC: pts74@portia.umds.ac.uk Dear All, I am a medical statistician, working at the medical & dental school attached to St. Thomas's Hospital, London. Much of my work is in consultancies with doctors, research students & others who need statistical help to see a project through to completion. As most of them do not have access to any particular statistical packages, but can use Excel, I do more & more work with Excel spreadsheets, so that they can continue with it on their own. This is particularly useful for power calculations, and standard analysis of frequency data. I am hampered by only having access to Excel 4.0a, while the hospital had Excel 5, with the statistical add-ons. I recently did a sheet for power calculations using Frison/Pocock adjustments for repeated measures. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: "R. Allan Reese" To: Spreadsheets discussion list Subject: Excel Help I've just helped a user with a basic spreadsheet query and conclude that the on-line Help in Excel 5 is not designed to help anyone who is not already familiar with the program. It seems to have odd scraps of info on the latest widgets inserted by oddbods in the development team and little fundamental or useful info. For example, this user wants to do the most basic spreadsheet operation: type in a formula and propagate it. Absolute/relative addressing is a fundamental spreadsheet operation, but try searching under "address" and you find the topic "Creating and editing a routing slip" or the "ADDRESS function" whose page contains examples of addresses (A1, A$1, $C$2) but no cross-reference. "Absolute" brings up "absolute references" but that then becomes GOTO "Changing a cell's reference type" and "relative" leads to the same help page or to "relative position" which becomes GOTO "MATCH". "Use Relative References" leads to a page about the macro recorder. "Formulas, cell references in" leads to "Overview of Using References" which describes "A1 reference style" and "R1C1 style" but not absolute and relative. "Overview of Copying and Moving Cells" mentions "several ways ... using the Cut, Copy and Paste commands, toolbar buttons, or shortcut keys" but not A/R addressing. AND SO ON. Believe me, I've spent far longer than I would expect a user to do in the Help system, and that was because I know there's an answer. In several places it does refer to specific chapters of the Manual, so I guess the philosophy is RTFM - and for most users that's a turn off. So once again, I ask why Excel should be a market leader. Would you put up with shoddy and unusable components in any other consumer product? Would you accept a car whose gearbox had twenty seven positions, though only three or four did something useful? The myth is that "Windows is easy", so a Windows application must be "intuitive". All the new users for the last several years have been brainwashed into accepting that the "new computers" are easy, so they (the users) are inadequate. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: "P.B.Silcocks" Date: Tue, 14 May 1996 13:14:07 +0100 Subject: Re: Excel Help I imagine that it's yet another example of the" good" not necessarily being the "best". If a product works reasonably well it can capture the main market share, and if - as with windows - other products are tailored to it, you enter a low-energy state, as it were, that requires a huge activation energy or suitable catalyst to produce change. Huge activation energy might correspond to a totally new package + related software being marketed by the sort of multinational that can afford it, while a catalyst would be something more subtle: a change in copyright law, or a new kind of hardware. The same principle applies in biology. The basic rule is: get in first and get lucky %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Paul Barnwell To: "'assume@MAILBASE.AC.UK'" Subject: RE: Excel Help The Excel 5 help system actually has 19 examples and demos specifically aimed at getting a new/novice user up to speed with the basic principles of Excel. These are accessible by choosing Examples and Demos... from the help menu. With regards to the relative & absolute references this is indeed one of the cases where tracking things down in a help system can be difficult, mind you it can be frustrating in a books index sometimes as well. With regards to intuitiveness that is an argument that has been running for at least 15 years and will run for much longer I'm sure. In terms of easy, what do you classify as easy, programming a video, driving a car? Getting the best out of a computer and it's software is (my personal point of view) more difficult than either of these. It can take many hours of training (everyone is different) to pass a driving test and yet how many people actually get the training they require to start off with (or the time to play with) a computer and yet they are expected to produce wonderful results simply by being plonked in front of one? Computers and software are tools and it is upto us to use the ones that suit best the goals we wish to achieve. Paul Barnwell (Any views expressed here are mine rather than those of the company I work for.) %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: srx033@coventry.ac.uk To: assume@mailbase.ac.uk Subject: MACROS Suppose I am writing a Visual Basic macro in which I want to incorporate a standard Excel dialog - for example the Chart Wizard. In other words I want the person running the macro to sit back while I set up some data, then I want them to be presented with the 5 steps of the Chart Wizard and make appropriate choices, then I want the macro to regain control and do something else. How do I do it? If you record a session in which Chart Wizard is invoked, there is no way to stop recording when you reach Step 1, so you cannot see the command which initiates Chart Wizard. It seems a perfectly normal thing to want to incorporate standard dialogs in your own macros - otherwise folk will keep re-inventing the wheel. Any offers? %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: srx033@coventry.ac.uk To: assume@mailbase.ac.uk Subject: MACROS I can see it is a case of "Physician heal thyself!". I have now found the answer to my earlier query on how to incorporate standard Excel dialogs into your own macros. If you have a macro sheet active and select View - Object Browser - Excel - Constants there is a long list of the inbuilt dialogs, e.g. xlDialogChartWizard. So, to invoke this dialog in your own macro, include the line: Application.Dialogs(xlDialogChartWizard).Show Once the dialog is terminated, control passes back to your macro. This could be a very useful feature in classroom demonstrations where you want there to be an interactive element, but you want to maintain control of the learning experience using a macro. One thing I would like to do is to have an add-on (not add-in!) to the Chart Wizard which inspected the data the student had used and the chart the student had drawn, then gave a suitable warning if they had done something daft. For example, Excel allows you to draw a pie chart when you have several hundred categories - the effect is very colourful but total garbage. My proposed add-on would stop the student in his tracks and query whether this was quite what he had in mind. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Paul Barnwell Subject: RE: MACROS There is a way of using built in Dialog boxes within your own macros. It involves using the Dialogs method along with a constant value. The example code below selects a cell, brings up the Note Dialog Box, and when the user clicks on OK or Cancel the rest of the macro (which displays a message box) then completes. Sub Test() Worksheets(1).Activate Range("a1").Select testy = Application.Dialogs(xlDialogNote).Show MsgBox "Finished" End Sub The list of Dialog Constants can be found by looking at the Excel Constants within the Object Browser. The xlDialogOpen & xlDialogSaveAs constants are the most commonly used ones. xlDialogChartWizard is one of the available dialogs, however it only works on an already created chart, and cannot be used to create a new chart from your own macro. Close, but not quite what you wanted I'm afraid. Hope this helps Paul Barnwell %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: John Douglas <100673.2546@CompuServe.COM> To: ASSUME Subject: List of Excel-Related World Wide Web Sites The following list of Web sites, in HTML format, may be of use (pickup from CompuServe MS Excel Forum) For Web surfers, cut & paste between Begin/End into you favorite browser. (Begin -----------------------------------------------------------------) Excel-Related World Wide Web Sites

Excel-Related World Wide Web Sites

Compiled by John Walkenbach

Following is a list of WWW sites that contain Excel-related material. If you know of any other sites, please let me know. I will upload updated versions of this file periodically.

(End -------------------------------------------------------------------) %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: srx033@coventry.ac.uk To: assume@mailbase.ac.uk Subject: EXCEL XY-Charts I am still messing about with the charts in Excel. Have you noticed how ridiculous the default axis scales are on XY-Scatter charts? I have just plotted some data where the range of x-values went from 6000 to 7500 and the chart used zero as the origin on the x-axis. Not being able to locate a magnifying glass I decided to change the scale. Double-clicking on the x-axis labels brings up a dialogue which allows you to change the default scale, so I changed the lower limit to 6000. Fine. Then I thought, maybe I can automate this with a macro. As a first step I retraced my steps and recorded the process of double-clicking and changing the scale. No problem. Just to check that it worked, I retraced my steps yet again, this time running the recorded macro. Guess what - the macro failed, telling me that it was unable to set the MinimumScale property of the Axes object (or something similar). The macro was not particularly complicated, just ActiveSheet.ChartObjects("Chart 71").Activate With ActiveChart.Axes(xlCategory) .MinimumScale = 6000 ... ... End With By way of a metaphor, it is like recording Match of the Day on video, then when you play it back you find it is Coronation Street! Even if you can't solve my macro problem, is it generally agreed that the choice of scales on XY-Scatter charts is somewhat inept? %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Paul Barnwell To: "'assume@mailbase.ac.uk'" Subject: RE: EXCEL XY-Charts This is a known problem with Excel 5 and has now been fixed. There is however a workaround which involves calling XLM functions from within VBA. I have attached the bulk of the Knowledge Base article that outlines this below. If you would like to view the whole article or search for other issues check out the Knowledge Base at http://www.microsoft.com/kb. The exact article is available at http://www.microsoft.com/kb/deskapps/excel/q111972.htm Hope this helps. XL5: Can't Get or Set Properties of X Axis in XY Scatter Chart[excel] ID: Q111972 CREATED: 27-FEB-1994 MODIFIED: 22-APR-1996 5.00 WINDOWS PUBLIC | kbprg kbcode kbmacro kbbuglist kbfixlist ---------------------------------------------------------------------- The information in this article applies to: - Microsoft Excel for Windows, version 5.0 ---------------------------------------------------------------------- SYMPTOMS ======== In Microsoft Excel version 5.0, you may receive one of the following error messages when you attempt to set or return the x axis scale properties for an xy (scatter) chart: Run-time error '1005': "Unable to set the property of the axis class" -or- Run-time error '1005': "Unable to get the property of the axis class" CAUSE ===== The following Visual Basic axis class properties for the x axis of an xy (scatter) chart may cause the above error messages: .Axes(xlCategory).MinorUnit .Axes(xlCategory).MajorUnit .Axes(xlCategory).MinimumScale .Axes(xlCategory).MaximumScale .Axes(xlCategory).MinimumScaleIsAuto .Axes(xlCategory).MaximumScaleIsAuto .Axes(xlCategory).MinorUnitIsAuto .Axes(xlCategory).MajorUnitIsAuto .Axes(xlCategory).ScaleType WORKAROUND ========== To set any of the above properties when the axis in question is the x axis in an (xy) scatter chart, use the Application.ExecuteExcel4Macro method with the Microsoft Excel 4.0 SCALE() macro command. Below is the syntax of the Visual Basic command: ' Enter the following line of code as a single, continuous line: Application.ExecuteExcel4Macro _ ("SCALE(min_num,max_num,major,minor,cross,logarithmic,reverse,max)") The following values are acceptable for the arguments for this method Argument value equivalent to --------------------------------------------------- min_num TRUE MinimumScaleIsAuto = True MinimumScale = max_num TRUE MaximumScaleIsAuto = True MaximumScale = major TRUE MajorUnitIsAuto = True MajorUnit = minor TRUE MinorUnitIsAuto = True MinorUnit = cross TRUE Crosses = xlAutomatic (if max = FALSE) Crosses = xlCustom, CrossesAt = (if max = FALSE) logarithmic TRUE ScaleType = xlLogarithmic FALSE ScaleType = xlLinear reverse TRUE ReversePlotOrder = True FALSE ReversePlotOrder = False max TRUE Crosses = xlMaximum FALSE see "cross", above For example, to set the minimum scale of the X axis to 5, the maximum scale to 20, and reverse the plot order of the axis, you would use this command: ' This line selects the axis. ActiveSheet.Axes(xlCategory).Select ' This line makes the changes. Application.ExecuteExcel4Macro ("scale(5,20,,,,,true)") Note that the above macro will work only if the chart is created as a separate chart sheet (that is, it will not work with a chart embedded on a worksheet). STATUS ====== Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Paul Barnwell To: "'assume@mailbase.ac.uk'" Subject: RE: EXCEL XY-Charts With regards to the "inept axis selection" question I'm not sure. I have seen so many charts (computer magazines are one of the worst offenders along with advertisers) which seem to show one thing until you actually realise that the data range is miles away from zero at which point the wonderful improvement being shown drops from a perceived 100% increase to an actual 1% increase. I guess it depends what you are trying to show. It's pretty difficult for another human sometimes, let alone a piece of software, to grasp what you want. At least with Excel you can change the Axis range and even automate it... when you know the trick. For anybody new on this alias Microsoft has a freely available version of the Knowledge Base on the WWW. All the Product Support Technicians here at Microsoft use an internal version (which is almost exactly the same) to answer many of the obscurer questions we get asked. If you have any technical/usage queries relating to Microsoft Software I strongly advise you to check it out at http://www.microsoft.com/kb. Have a browse of the rest of the site at http://www.microsoft.com as well, there are well over 100,000 pages of info on the site and links to many other sites as well. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: R. Allan Reese To: srx033@coventry.ac.uk Subject: Re: EXCEL XY-Charts A very un-political answer - Yes, I agree unreservedly. The choice of axes scales and labelling is something that it's probably impossible to automate. In our graphics course we used MS Graph (the back-end to Excel) and Stata (a general stats package) to illustrate the difference. MS Graph does not (unless I've overlooked some pop-up choice) allow anything other than equal divisions along the whole scale. Naive users whose experience consists of plotting a small number of graphs by hand accept this, because hand-plotting requires that you draw axes and use them to locate data points. Computer-drawn graphics do not need this, and the axes should be drawn as an aid to the reader; this may mean labelling the exact values that occur in the data, and fixing the range of the axis to emphasize (or de-emphasize) a particular spacial comparison. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: srx033@coventry.ac.uk To: assume@mailbase.ac.uk Subject: XY-Scatter Charts I agree with Paul Barnwell that the important thing is to be able to modify the axis scales, which is clearly very easy. I am suitably chastened for not having searched the KnowledgeBase for the answer to my problem about automating the scale changes! Anyway, ASSUME has triumphed and I have the solution to my problem. By the way, in case there is any confusion about how to talk to this list, you simply send your mailing to assume@mailbase.ac.uk and it is automatically forwarded to the whole group. Neville Hunt %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Paul Barnwell To: "'assume@mailbase.ac.uk'" Subject: VBA - the next step For any of you that do any VBA coding check out http://www.microsoft.com/officedev/vba/. Print the page out as the screen shot is actually a lot better then. If you look carefully and think about what the article says you can get a fair bit of info about VBA may well be going in the next release of Office. If you are feeling slightly brave and are running either Windows 95 or NT v 4 (Beta) then you can try the Beta 1 version of Internet Explorer 3. Just follow the links from the www.microsoft.co m home page to find out info and download the files. %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Guido.Wyseure@agr.kuleuven.ac.be To: Subject: List of problems in statistics with Excel Dear Members, I would like to compile a list with the real and perceived problems with the statistics in Excel ( version 5 for win3.1 and version 7 for win95). There are a few known issues: 1) negative det. coeff. with zero-intercept regression ( however, after looking in detail into the matter this is not as big a problem as some people cry out) 2) approximations of the probability functions at low probabilities ( less than 1%) are not good. 3) numerical approximations under high multi-collinearity are not good ( I tried a few difficult and very artificial regressions, Excel always gave me an answer, while SAS refused the matrix inversion and warned that the regression was meaningless). It would be good to have a complete list. I will compile the anwers to me and present a summary to this mail-list. Thank You for your cooperation, Kind regards, Guido %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: srx033@coventry.ac.uk To: assume@mailbase.ac.uk Apologies to folk who have already seen this, but recent members of the list may like to know what we discussed at Nottingham. RSS SPREADSHEET FORUM 27 March 1996 REPORT OF DISCUSSION Good features of Excel * good software to use with non-specialists * has a strong user base in industry * good for preparing ready-made templates for particular analyses * dynamic linking - change the data and the output updates * common environment with non-statistical users * good range of functions * Visual Basic in the background gives flexibility for add-ins * ubiquitous - everybody has it * excellent graphics for both teaching and using * relatively cheap for individuals to purchase * excellent value for institutions in terms of cost per hour of use * flexibility in data handling - few restrictions * allows students to get hands on data * ability to give interactive demonstrations * ability to attach notes to cells is useful (especially in Excel 7) * continuous improvement * durability - it will still be there in several years time Errors in Excel * moving average "trendline" is out of phase * regression through the origin gives incorrect output * x-axis labels on histogram wrongly positioned * histogram cannot cope with unequal class intervals * CONFIDENCE function uses z not t * help on CONFIDENCE is totally wrong * error bars are wrong in versions prior to 5.0c * the function wizard cannot cope with array functions (e.g. linest) * ranking does not handle ties properly * there are errors in the inverse distribution functions * RANDBETWEEN gives first value only half the frequency * in two-sample t-test unequal variances, df are rounded not truncated Things that need changing in Excel: * consistency between functions (e.g. with respect to missing values) * subscript/superscript tool on the Format toolbar * a deleted cell should not be remembered in range operations * an option to include/exclude hidden cells in calculations * general facility to use non-contiguous ranges (e.g. multiple regression) * ability to lock a data series on a chart to prevent dragging by mistake * data analysis tools should dynamically link output with data * better quality control - errors should be found before release * Microsoft is too secretive - e.g. how does Solver really work? * View Manager does not work on protected worksheets New features that would be desirable in Excel: * box and whisker plots * proper histograms * chi-squared test for contingency tables * non-parametric test procedures * a formal missing value indicator Neville Hunt 02/04/96 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Guido.Wyseure@agr.kuleuven.ac.be To: Subject: Solver in Excel Thank You Neville, As I joined after your forum was reported, I was not aware of your report. The optimizer for nonlinear multivariate problems and the single variable solver are some of the better things in Excel and Quattro Pro. They are great tools. For a long time I felt quite uneasy to use such tools without any documentation. This applies for Quattro and Excel. Not so long ago I discovered that both spreadsheets have bought their tools from the same software supplier: Frontline Systems Inc Their home page is "http://WWW.frontsys.com/" which contains extensive documentation on the Excel-solver starting from: "http://WWW.frontsys.com/pages/xlhelp.htm" I hope this helps to demystify the Solver in Excel. Regards, Guido %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Conrad Carlberg To: Multiple recipients of list Subject: Re: Ranking and Correlation in Excel 7 >Can someone tell me WHY the ranking function in Excel returns the >HIGHEST rank for each of a group of tied values? Well, calculating Spearman's r isn't the only use there is for ranking data, and some usages call for the highest rank among ties. Admittedly, those usages are rare. >When calculating Spearman's coefficient of Rank Correlation I was >always taught to use the AVERAGE rank. Is there a quick, NON-manual >work around to produce this? What is the overall effect on the >reliability of a coefficient based on Excel's ranking system? Suppose that one of your variables' values are in A1:A20, and that you have named the range A1:A20 as Range1. In B1, array-enter this formula: =SUM(1*(A1>=Range1))-(SUM(1*(A1=Range1))-1)/2 (You array-enter a formula by holding down Ctrl and Shift as you press Enter.) Copy-and-paste that formula into B2:B20. You'll now have ranks as assumed by Spearman's r in B1:B20. Repeat the process for your other variable, and then use CORREL() on the ranks. I suppose that you could consider this a "manual" workaround, but apart from writing a macro it's the only way. It comes from the Bob Umlas Array Formula Boutique. The overall effect of the difference between the two ranking methods would depend on the particular data set. But it would be unusual to see a difference between the two resulting correlations that was greater than 0.05, granted real-world data. >Whilst on the subject, on which type of correlation is the CORREL() >function based? CORREL(), and equivalently PEARSON(), are both calculated by means of the usual product moment equation, the covariance divided by the product of the standard deviations. >Many thanks for your help, in advance. >BTW, I have NO choice as to the program used. I am 'stuck' with >Excel, so please no comments on its suitability unless suggesting >freeware, TA. Since you already have Excel, it's sunk cost freeware to you. Excel is entirely suitable for even sophisticated procedures such as multiple discriminant function analysis. I've seen some complaints about its use as a statistical platform, but (apart from some minor problems with the Analysis Toolpak) these complaints come from people who have only a partial clue. Hope this helps, Conrad %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: G.W.Davis@tees.ac.uk To: assume@mailbase.ac.uk Subje ct: Re: Ranking and Correlation in Excel 7 (fwd) Neville, Excel returns the lowest rank for tied ranks using my version of Excel Glyn %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% To: assume@mailbase.ac.uk From: Rodney Carr Subject: Hello and some questions Hello everyone. I've just joined the ASSUME group. Here's a bit about me. I'm a lecturer in mathematics and statistics at Deakin University in Australia. I'm at a fairly small campus and work mainly with science and management people. The main science here is Aquatic Science, which includes things like biology, ecology, limnology, hydrological work. It's a small dept with about 15 staff and 200-or-so students. I teach the undergraduates and have a lot of consultative-type work with postgrads and staff. The management faculty does fairly standard stuff - I work with them for their business statistics and do some consultative-type work. I used to teach and work mainly with Minitab and Systat, and still do for many-variable problems. But a couple of years ago I discovered that Excel will do essentially all that an undergrad needs, and the students seemed to like it better. So did I, so now I use Excel for all "small" problems. I realized that it was a bit much to expect students to write out the formulas all the time, so I developed a set of templates for them. They have been a hugh success and I've refined them to such an extent that now they effectively automate most of the analysis that undergrads (and most people for that matter) need. I've built in all the standard graphs and tests and included most of the non-parametric tests and power analysis, too. There are about 20 templates in the set - each designed to handle different combinations of variables (continuous/ordinal and categorical). Once a user pastes his or her data in all the relevant tests are automaticallly carried out and graphs drawn. As far as possible macros are not used, but there are a few that update the graphs properly (mainly scaling!). It's all called XLSTATS and is available from my home page http://www.cm.deakin.edu.au/~rodneyc if you would like to see it. I have a number of questions. All relate to making Excel do certain tasks that I'd like to include in XLSTATS. For a start..... Does anybody know if anyone has written macros that will make Excel draw boxplots draw contour plots do LOWESS smoothing do Tukey's test (what's the formula for the distribution?) None of these are all that hard, but I don't want to do them myself if someone else already has! Rodney %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: Paul Barnwell To: "'assume@mailbase.ac.uk'" Subject: RE: Hello and some questions Rodney, I have an answer to the first of your questions. Neville Hunt (the gentleman who has been the catalyst and a major work horse behind the events that lead to this forum being formed) has actually come up with a solution for the creation of BoxPlots. As my part of the forum (I currently work in the team that supports Excel/VBA at Microsofts Product Support services in the UK) I have put the article together and submitted it for inclusion in the public Knowledge Base that can be accessed on the Web at http://www.microsoft.com/kb. Hopefully it will appear on there in the next few weeks, in the mean time here is a draft copy. If you have any views, comments or suggestions about it please let me know. >-------------------------------------------------------------------- >The information in this article applies to: > > - Microsoft Excel for Windows, version 5.0 > - Microsoft Excel for the Macintosh, version 5.0 > - Microsoft Excel for Windows 95, version 7.0 >-------------------------------------------------------------------- > > >SUMMARY >======= > >Microsoft Excel does not have a BoxPlot/Box & Whisker Chart Type built in. >However, a reasonable representation of this type of chart can be created by >following the steps outlined in this article. > >MORE INFORMATION >================ > >Do the following steps in order to create a sample BoxPlot/Box & Whisker >Chart: > >1. Enter the following data into a new worksheet: > > A1: Statistic B1: a C1: b D1: c > A2: median B2: 40 C2: 45 D2: 50 > A3: q1 B3: 20 C3: 22 D3: 30 > A4: min B4: 10 C4: 15 D4: 18 > A5: max B5: 100 C5: 110 D5: 90 > A6: q3 B6: 70 C6: 75 D6: 57 > > >2. Select cells A1:D6 and on the Insert menu, point to Chart and then > click "On This Sheet". > >3. Draw the area for the embedded chart object and in Step 1 of 5 of the > Chart Wizard click Next. > >4. In Step 2 of 5 click the Combination Chart type and click Next. > >5. In Step 3 of 5 click option 6 from the combination chart styles and > click Next. > > You will now see a Alert Box with the warning: > > A volume-open-high-low-close stock chart must contain five series > >6. Click OK. > >7. In Step 4 of 5 click the Rows option for "Data Series in" and then > click Next. > >8. In step 5 of 5 click No for "Add a Legend?" and then click Finish. > >9. Activate the embedded chart. On the Insert menu, click Axes. In the > Axes dialog box, clear the check box for "Value (Y) Axis" in the > "Secondary Axis" group box and then click OK. > >10. On the Format menu click Line Group. > >11. Click once on any one of the colored columns (do not click one > of the white ones) to select the series. > >12. On the Format menu, click Chart Type, and in the Chart Type dialog box > click Line and then click OK. > >You will now have a line connecting the three white columns. > >13. Click once on the line and on the Format menu click Selected > Data Series. > >14. In the Pattern Tab set the following properties: > > Line: None > Marker: Custom Style: + > Marker: Foreground: Black > Marker: Background: None > > and then Click OK. > >You now have a reasonable BoxPlot. > >REFERENCES >========== > >For more information about Creating Charts in Microsoft Excel version 7.0, >click Answer Wizard on the Help menu and type: > > how do I create a chart > >For more information about Creating Charts in Microsoft Excel version >5.0, choose the Search button in Help and type: > > charts > > >KBCategory: >KBSubcategory: > >Additional reference words: 5.00 5.00a 5.00c 7.00 With regards to your other problems I am afraid to say I am not a statistician so I cannot help. It might be worth checking out the Knowledge Base on the off chance there is something there that might help. I'd be interested in any positive responses you get regarding these issues. I'll also take a look at your Excel Templates later this week when I get a chance to look at the 15MB of Data. Hope this helps. Paul Barnwell (paulbarn@microsoft.com) ACTIVATE the NET Microsoft Internet Explorer 3.0 - http://www.microsoft.com/ie %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: srx033@coventry.ac.uk To: assume@mailbase.ac.uk Subject: BOXPLOTS With reference to Paul Barnwell's note about boxplots, details of my method are to be published in the next issue of The Spreadsheet User. May I commend this publication to you. It is produced at Sheffield Hallam University by John Callender and Roger Jackson, who are both members of our group. John will correct me, but I think it comes out twice a year and the subscription is 10 sterling pounds (approx). I am sure John and Roger would welcome articles of a statistical nature, although the readership covers a much wider remit. Neville Hunt %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% To: assume@mailbase.ac.uk, "'assume@mailbase.ac.uk'" From: Rodney Carr Subject: On drawing boxplots Paul, Thanks for this. I'd never used Up-down Lines and High Low lines - they do the job. Maybe a simpler way of making a chart from scratch is .... Enter the data like A B C D 1: Group name a b c 2: q1 20 22 30 3: median 40 45 50 4: min 10 15 18 5: max 100 110 90 6: q3 70 75 57 Select B1:D6 Insert -> Chart -> On this sheet Step 2/5 Line Step 3/5 Option 2 Step 4/5 Rows Step 5/5 No legend Select embedded chart Select horizontal axis Format -> Selected Axis -> Scale -> Value (Y) axis crosses between categories Select 2nd series (S2 in name box at left of formula bar - it's in pink usually) Format -> Selected Data series -> Patterns -> Marker -> Style "+" Select each series in turn and for each Format -> Selected Data series -> Patterns -> Line -> None Format -> Chart type -> Options -> Up-down lines and High-Low lines %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% From: "Jackson, Roger" To: "'Assume '" Subject: Spreadsheet User Dear all Attached are subscription details for Spreadsheet User. We are keen to receive novel spreadsheet applications for publication particularly those of a relatively low level. Spreasdsheets are now in the national curriculum and we would like to publish more articles that schools are likely to be interested in. Roger Jackson [[ SUBS.DOC : 4147 in SUBS.DOC ]] The following binary file has been uuencoded to ensure successful transmission. Use UUDECODE to extract. begin 600 SUBS.DOC MT,\1X*&Q&N$`````````````````````.P`#`/[_"0`&```````````````! M````%P``````````$```&@````$```#^____`````!@```#_____________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M_______________________]____"````/[___\$````!0````8````'```` M"0```/[___\*````_O___PP````-````#@````\````0````$0```!(````3 M````%````!4````6````_O______________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M_____________________________________________U(`;P!O`'0`(`!% M`&X`=`!R`'D````````````````````````````````````````````````` M```````````6``4`__________\#``````D"``````#`````````1@`````` M`````````(:G;;!#D+L!`P```(`,`````````0!#`&\`;0!P`$\`8@!J```` M```````````````````````````````````````````````````````````` M`!(``@'_______________\````````````````````````````````````` M````````````````8@````````!7`&\`<@!D`$0`;P!C`'4`;0!E`&X`=``` M````````````````````````````````````````````````````&@`"`?__ M__\$````_____P`````````````````````````````````````````````` M``L```"+%P```````$\`8@!J`&4`8P!T`%``;P!O`&P````````````````` M```````````````````````````````````````````6``$!`0````(```#_ M____``````````````````````````"&>CRO0Y"[`89Z/*]#D+L!```````` M`````````0```/[_____________________________________________ M____________________________________________________________ M____________________________________________________________ M_________________________R8````G````*````"D````J````*P```"P` M```M````+@```"\````P````,0```/[_____________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M______________________________\!`/[_`PH``/____\`"0(``````,`` M``````!&'````$UI8W)O$'8`/P$``$`!```'````_____P#````` M8`$"__\``````````````````,4 "X2<`````8@&(!9X0=@!``0``70$```<` M``#_____`,````!@`0+__P``````````````````Q0+A)P````!B`?X%GA!V M`%T!``!N`0``!P```/____\`P````&`!`O__``````````````````#%`N$G M`````&(!=`:>$'8`;@$``($!```'````_____P#`````8`$"__\````````` M`````````,4"X2<`````8@'J!E-U8G-C6UE;G0@ M*&-H97%U97,@FAA"AL1KA M````````_O\```,*`````````````````````````0```."%G_+Y3V@0JY$( M`"LGL]DP````\`(```X````'````F`````(```#<````!``````!```(```` M1`$```P```"(`0``"P```*P!```-````T`$```\```#T`0``$````!@"```* M````/`(``!(```!@`@``#@```(0"```)````J`(``!,```#,`@``________ M________________________!0!3`'4`;0!M`&$`<@!Y`$D`;@!F`&\`<@!M M`&$`=`!I`&\`;@```````````````````````````````````"@``@#_____ M__________\````````````````````````````````````````````````E M````(`,````````````````````````````````````````````````````` M`````````````````````````````````````````````/______________ M_P`````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M````````````````````````````````````________________```````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M``````````````````````````#_______________\````````````````` M``````````````````````````````````````````````"(B(B(B(B(B!X` M```H````0SI<35-/1D9)0T5<5TE.5T]21%Q414U03$%415Q.3U)-04PN1$]4 M````````````````````````````'@````X```!3=6)S8W)I<'1I;VYS```` M````````````````'@```"T```!38VAO;VP@;V8@16YG:6YE97)I;F<@26YF M;W)M871I;VX@5&5C:&YO;&]G>0`````````````````````>````+0```%-C M:&]O;"!O9B!%;F=I;F5E"AL1KA`````````````````````#L``P#^_PD` M`0```/[___\``````````/______________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M________________________________W*5E`#/`"0@```$`90`````````` M``````,``(@(``"+%P```````````````````````%P%````````*P`````` M```````````````````````````````0``#4`````!```-0```#4$``````` M`-00````````U!````````#4$````````-00```4````_A````````#^$``` M`````/X0````````_A````````#^$```$`````X1```*````&!$``!````#^ M$````````&`6``!>````*!$````````H$0``(@```$H1````````2A$````` M``!*$0```````$H1````````2A$```````!*$0```````.T1```"````[Q$` M``````#O$0```````.\1```C````$A(``!@"```J%```&`(``$(6```>```` MOA8``%0````2%P``>0```&`6`````````````````````````````-00```` M````2A$```````````4`!@`!``(`2A$```````!*$0`````````````````` M``````````!*$0```````$H1````````8!8```````!*$0```````-00```` M````U!````````!*$0`````````````````````````````H$0```````$H1 M````````2A$```````!*$0```````$H1````````U!````````!*$0`````` M`-00````````2A$```````#M$0````````````````````````````#H$``` M"````/`0```.````U!````````#4$````````-00````````U!````````!* M$0```````.T1````````2A$``*,```!*$0`````````````````````````` M```````````````````````````````````````````````````````````` M````````````````````````````````````4W5B2D@=&\Z#0U*+E0N($-A;&QE;F1E M\`Y^/GX^?O``#A```````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M``````````````````````````)U`0`&4!``8Q0```YU`40$`````%`0`&,4 M```#4!``"W4!1`0`````4!``!56!8Q@``V,8``55@6,<```4``,```X#```/ M`P``8P,``&0#``"P`P``L0,``#\$``!`!```700``&X$``"!!```HP0``+\$ M``#+!```W00``"D%```J!0``:@4``*D%``#V!0``(08``"(&```\!@``/08` M`&$&``!_!@``G08``+L&``#9!@``)0<``"8'```G!P``,`<``#$'``!G!P`` M:`<``+,'``"T!P``XP<``.0'``!("```_0`!XB=+`?T``>(GZP#]``'B)QL! M_0`!XB<;`?T``>(G&P']``'B)QL!_0`"XB<;`?T``>(G&P']``'B)QL!_0`! MXB<;`?T``>(G&P']``'B)QL!_0`!XB<;`?T``>(G&P']``'B)QL!_0`!XB<; M`?L``>(G&P'[``'B)QL!^P`!XB<;`?L``>(G&P'[``'B)QL!^P`!XB<;`?L` M`>(G&P'[``'B)QL!^P`!XB<;`?L``>(G&P'[``'B)QL!^P`!XB<;`?L``>(G M&P']``'B)QL!^``!XB<;`>\``>(G6@'G``'B)QL!YP`!XB<;`><``>(G&P'G M``'B)QL!YP`!XB<;`><``>(G&P'G``'B)QL!YP`!XB<;`><``>(G&P$````` M````!P``)AD()QD(*!D(*1D(``@```4!)AD()QD(*!D(*1D(``(```4#``$` M```"```%`2E("```20@``%<(``!8"```60@``%H(``!;"```7`@``&4(``!F M"```9P@``',(``"%"```A@@``(<(``"("```^``!XB<;`?@``>(G&P'X``'B M)UH!]0`!XB?K`/4``>(GZP#U``'B)^L`\P`!XB?K`.P```````#J```````` M\P```````.P```````#J````````Z@```````/,```````#S``'B)^L````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````!#P``!@\`'6`:_/\;`0`E`@`! M`````@``!0$`!P``)AD()QD(*!D(*1D(#PX`$@`(``$`2P`/```````:``!` M\?\"`!H`!DYO2!38VAO;VQB;V]K`-8B``0` M`0B(&```T`(``&@!``````6T"(8%M`B&``````(``0`````````````````` M````!`"#$```````````````````````````````````60)Y````#5-U8G-C M0`````````````````````````````` M`````````````````````````````!8`!0#__________P,`````"0(````` M`,````````!&````````````````AL.[OD.0NP$<````P`,````````!`$,` M;P!M`'``3P!B`&H````````````````````````````````````````````` M````````````````````$@`"`?_______________P`````````````````` M``````````````````````````````````!B`````````%<`;P!R`&0`1`!O M`&,`=0!M`&4`;@!T```````````````````````````````````````````` M```````````:``(!_____P0```#_____```````````````````````````` M````````````````````'@```&@8````````3P!B`&H`90!C`'0`4`!O`&\` M;``````````````````````````````````````````````````````````` M`!8``0$!`````@```/____\``````````````````````````(9Z/*]#D+L! MAGH\KT.0NP$```````````````#___________________________[_____ M____________________________________________________________ M_____________________________QD```#]_____O____[___\%````&P`` M`/[___\?````(````"$````B````(P```"0````E````)@```"<````H```` M*0```!T```#_________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M_________________________________________________P4`4P!U`&T` M;0!A`'(`>0!)`&X`9@!O`'(`;0!A`'0`:0!O`&X````````````````````` M```````````````H``(`________________```````````````````````` M`````````````````````````@```"`#```````````````````````````` M```````````````````````````````````````````````````````````` M``````````#_______________\````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M`/_______________P`````````````````````````````````````````` M```````````````````````````````````````````````````````````` M````````````````````````````````````````````````````________ M________```````````````````````````````````````````````````` M`````````````0```/[___\#````!`````4````&````!P````@````)```` M"@````L````,````#0````X```#^________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M__________________________________]O;"!O9B!%;F=I;F5E"AL1KA M`````````````````````#L``P#^_PD`:61U86PL(&$@;&EB2P@82!D M97!A````*````$,Z7$U33T9&24-%7%=)3E=/4D1<5$5-4$Q! M5$5<3D]234%,+D1/5````````````````````````````!X````.````4W5B M2Q38VAO;VP@;V8@16YG:6YE97)I;F<@26YF M;W)M871I;VX@5&5C:&YO;&]G>0````````````#0SQ'@H;$:X0`````````` M```````````[``,`_O\)``8```````````````$````7```````````0```: M`````0```/[___\`````&````/__________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M____________________________________________________________ M___________________________________________6]U6%B M;&4@=&\@4VAE9F9I96QD($AA;&QA;2!5;FEV97)S:71Y*2!T;SH-#4HN5"X@ M0V%L;&5N9&5R(&]R(%(N($IA8VMS;VX-4W!R96%D0U0;VYD(%-T6%B;&4)7U]?7U]?7U]?7U]?7U\-7U]?7U]?7U]?7U]?7U]? M7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]?7U]? M7U]?7U]?7U]?7U]?7U]?#0T-0V]N=&5N=',-#3$N($L@0F]E9V4Z($=R861I M96YT3H@36]D96QL:6YG M('1H92!3<')E860@;V8@82!$:7-E87-E+@T--"X@4B!*($9I(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 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%