Softpanorama (slightly skeptical) Open Source Software Educational Society

May the source be with you, but remember the KISS principle ;-)

Excel 2003 Macro Programming

Excel is not only commodity spreadsheet it is also a powerful development platform. You can develop VBA custom applications where the user can't see the underlying application (Excel).

Excel can help to solve problems quicker then with a standalone procedural language or an expensive application such as Matlab or Mathematica. Since Excel has such advanced computational capabilities  and is ubiquitous in offices and homes, it would make sense to learn the capabilities of this application.

While this product is marketed by Microsoft as user-oriented, but in reality it has "professional" capabilities. and is suitable for solving wide range of numerical problem and can perform pretty complex financial simulations including simple statistical analysis tasks such as correlating data.

You can also perform curve fitting and regression, solve equations, perform integration and differentiation, and solve both ordinary and partial differential equations. Some linear programming optimization tasks also can be done with Excel.

There are tremendous amount of high quality book developed to this application including a dozen of books from O'Reilly who now wants to get a peace of this lucrative market.  But there are also tremendous amount of low quality books and some of them are rated pretty highly on Amazon (lemmings effect ;-)

Spreadsheet applications were the killer application that drove the personal computer market. In the early PC days, Lotus 1-2-3 (long with WordPerfect) dominated the productivity software business.

It's probably a safe bet that there's more data stored in Excel spreadsheets than in all the world's databases.

Excel's Data menu offers basic database features, such as sorting, filtering, validation, and data-entry forms. You can quickly import and export data in a variety of formats, including those of database management applications, such as Access. Excel's limitations become apparent as your needs for entering, manipulating, and reporting data grow beyond the spreadsheet's basic row-column metaphor.

In cases Excel power is not enough moving to a relational database management system (RDBMS), such as Access might be appropriate.  The latter makes creating pretty complex databases with multiple linked tables quick and easy. It also contains Query by Example wizard (called  called Query Design view).  Access 2003 and 2007 has a collection of wizards to lead you step-by-step through each process involved in developing and using a production-grade database application.

Reviews

ATM • Spreadsheet Files

Here we present a huge collection of spreadsheet files which originally appeared on a CD which was sent, free, to members of ATM. We have now negotiated permission to make them available here.

But, if you’re not a member, please consider joining ATM. Not only will you be supporting the further development of resources such as these but you will also receive the other benefits of belonging to one of the leading professional associations for mathematics educationalists.

ATM • Problem Solving with Interactive Spreadsheets

ATM’s ICT publication is a CD of 16 programs especially designed to be used with any interactive whiteboard or projector. The programs provide a variety of teaching opportunities for KS2, KS3 and KS4 classrooms and because they are so flexible, almost all can be used at all these key stages.

<< Samples from Problem Solving with Interactive Spreadsheets

The CD associated with this book contains sixteen interactive spreadsheets which have been developed to pose interesting and challenging problems. The tasks, many of which are well known, have been transferred to this format to help teachers offer learning opportunities to pupils that help develop their skills of mathematical reasoning.

The files will work with Excel and most other spreadsheet applications.

The problems require little prior knowledge in terms of explicit mathematical content. The aim is to provide problems that are challenging but accessible to students in the upper primary and secondary age ranges. Most of the problems include a range of extension problems to challenge even the most able.

The files are interactive so that as learners respond to the tasks and questions posed the next tasks appear on the screen automatically. The teacher’s guide offers suggestions for further extension and hints concerning solutions and proofs.

[Nov 5, 2006] http://www.exceltip.com/st/TipofTheDay/295.html

Today’s Tip

*Applying Colors to Maximum/Minimum Values in a List*


*To apply colors to maximum and/or minimum values:*

Rounding all the numbers in column A to zero decimal places, except for those 
that have "5" in the first decimal place.

1. Select a cell in the region, and press Ctrl+Shift+* (in Excel 2003, press 
this or Ctrl+A) to select the Current Region.
2. From the Format menu, select Conditional Formatting.
3. In Condition 1, select Formula Is, and type =MAX($F:$F) =$F1.
4. Click Format, select the Font tab, select a color, and then click OK.
5. In Condition 2, select Formula Is, and type =MIN($F:$F) =$F1.
6. Repeat step 4, select a different color than you selected for Condition 1, 
and then click OK.

*Note:*
Be sure to distinguish between absolute reference and relative reference when 
entering the formulas.

See screenshot for a sample

<http://www.exceltip.com/images/screenshots/295.gif>  

[Nov 4, 2006]Excel 2003 Formulas Books John Walkenbach

Very weak and expensive cookbook, November 4, 2006
 

Reviewer:

Nikolai N Bezroukov "kievite" (Budd Lake, NJ) - See all my reviews

This is an eclectic collection of various recipies for Excel. This is a strange "lemmings" effect that the book has such a high rating on Amazon.

The book is essentially a badly written cookbook as it does not provide underling mechanics and key ideas behind the Excel formulas. Chapters are more or less disconnected and most of them can be read in any order.

At the end the reader is left with very new good findings that probably are not worth the price of the book.

Pagecount is very deceptive -- considerable part of the book is fluff -- brainless reproduction of basic things that one can find in help and that is not worth even one dollar. Many examples are very trivial and not worth reading.

Tricks like Appendix B are simply disgusting -- the author just copied the listing of functions that has no practical value whatsoever to inflate the pagecount.

Explanations mostly are extremely fuzzy. The author has real talent to make simple things complex and complex things impossible. Also this is just "do like I said" type of cookbook: the author never tries to explain concepts that are used (use of absolute adressing vs relative, the syntax intricacies of the second argument of countif and similar functions, etc)

Also the book suffers from frequent references to previous versions of Excel, which only distract the reader. One can assume that if the reader really wants to use one of the previous versions of Excel he can buy prev. edition of the book and save money.

In few places were things became more interesting they are also incomplete and/or incorrect (creation of your own VBA functions and collection of functions, usage of array functions like frequency, etc).

My impression is the author is just a book writer and does not have rich real world experience with Excel, the experience that is necessary to distinguish between important and trivial things.

As a result he cannot provide the reader any help in getting the grasp of underling architectural ideas, that are often very non-trivial (Excel is extremely powerful analytical tool disguised as a commodity spreadsheet).

Tips

[Nov 3, 2006] Cell reference can be relative ( K2), absolute ($K$2) or mixed ($k2). Difference between relative references to cell and absolute references is that the first do not change in recurrent formulas.

[Nov 3, 2006] In counif, sumif and similar functions it is possible to use complex formula by constructing the last argument from string constants and functions.

=countif(Range,">"&AVERAGE(Range)+STDEV(Range)*3)

[Nov 3, 2006] Some Notes about EXCEL

Once tables become large one can easily get lost in a mess of numbers, forgetting their meaning. Therefore, it is recommended to use names. To create names for cell areas you have several possibilities. You may mark an area and edit its name in the box to the left of the editing line. Another possibility is to use column or row headings. Then you mark the area to be given a name and press CTRL-SHIFT-F3. The list of all names that are used can be seen by pointing to an empty area, pressing F3 and choosing "Add List" from the options. An area can be changed by the key combination CTRL and F3.

There are three ways to create range names:

• Entering a range name in the Name box

• Choosing the Name, Create command from the Insert menu

• Choosing the Name, Define command from the Insert menu

[Nov 3, 2006] Strategy Survival Guide Strategy Skills Modelling

Modelling in Excel is like any other piece of analysis - you require a clear understanding of the questions at hand, a vision of the output, a good plan to get there, time to work through the plan to completion and the ability to package the analysis for review. Failure to do so will almost certainly result in the need for rework, lost time and frustration.

There are a number of steps, which if followed, will assist in creating a successful Excel model.

[Nov 3, 2006] Oreilly/Excel Hacks

Hack 2: Entering Data into Multiple Worksheets Simultaneously (PDF)
Hack 9: Locking and Protecting Cells Containing Formulas (PDF)
Hack 18: Identifying Formulas with Conditional Formatting (PDF)
Hack 28: Customizing Cell Comments (PDF)
Hack 39: Addressing Data by Name (PDF)
Hack 46: Sharing PivotTables but Not Their Data (PDF)
Hack 51: Creating Two Sets of Slices in One Pie Chart (PDF)
Hack 60: Adding Descriptive Text to Your Formulas (PDF)
Hack 81: Speeding Up Code While Halting Screen Flicker (PDF)
Hack 99: Accessing SOAP Web Services from Excel (PDF)

Copying & Pasting Cell Content to Thousands of Cells

To copy & paste cell content to thousands of cells:

1. Select and copy cell A1.
2. Type a cell address in the Name box, for this example, type A5000.
3. Press Shift+Enter to select the cells from A1:A5000.
4. To paste, press Enter.

Counting the Number of Values between Two Specified Values in a List

Problem:

Finding the number of values in List1 (Column A) that are between each two successive values in List2 (Column B).

Solution:

To count the numbers in List1 that are, for example, between 1 and 2 (B2:B3), use the COUNTIF function as shown in the following formula:
=COUNTIF($A$2:$A$10,">="&B2)-COUNTIF($A$2:$A$10,">"&B3)

Using VLOOKUP and ISNA functions to find matching values in different sheets

Column B in sheet1 contains numbers. Columns B:C in sheet2 contain numbers and their matching text.
How could we find the text in sheet2 matching each number in sheet1?

Solution:

The following formula will find the text in sheet2 matching to the number in cell B13 in sheet1:
=IF(ISNA(VLOOKUP(B13,$B$20:$B$24,1,FALSE)),"Not Found",VLOOKUP(B13,$B$20:$C$24,2,FALSE))
In case the number is not found in sheet2, using ISNA function, the formula will return Not Found.

Example:

Sheet1

Number__Result_______Formula
1_______One__________=IF(ISNA(VLOOKUP(B13,$B$20:$B$24,1,FALSE)),"Not Found",VLOOKUP(B13,$B$20:$C$24,2,FALSE((
9_______Not Found____=IF(ISNA(VLOOKUP(B14,$B$20:$B$24,1,FALSE)),"Not Found",VLOOKUP(B14,$B$20:$C$24,2,FALSE((
3_______Three________=IF(ISNA(VLOOKUP(B15,$B$20:$B$24,1,FALSE)),"Not Found",VLOOKUP(B15,$B$20:$C$24,2,FALSE))


Sheet2

Number__Text
4_______Four
5_______Five
3_______Three
6_______Six
1_______One

Simplifying Formulas by Reducing the Complexity of IF Functions

We want to create a conditional formula that will return TRUE if there is at least one number in List1 greater than the value in D1. Otherwise the formula will return FALSE.

Solution 1:

Use the following formula:
=(($A$2>D1)+($A$3>D1)+($A$4>D1))>0

Solution 2:

Use the OR function as shown in the following Array formula:
{=OR($A$2:$A$4>D1)}


List1
8
2
3

Number to Compare: 20

F1FormulasEvaluation

Excel 2002 and Excel 2003 enables selecting an exact data table on a Web site, and importing and refreshing only the needed data.

To import and refresh information from a Web site:

1. Open Excel 2002 or Excel 2003, and from the Data menu, select Import External Data, and then New Web Query.
2. In the Address box of the New Web Query dialog box, type or paste the address of the Internet site. For example, open the site www.bloomberg.com, which includes a table of various currency exchange rates. The address of the page containing the table of currency exchange rates is http://www.bloomberg.com/markets/index.html.
3. In the dialog box, notice the Web page. Click the small arrow in the upper left-hand corner of the table to select only the data table.
4. Click Import.
5. To save the query, click Properties in the Import Data dialog box.
6. In the External Data Range Properties dialog box, enter the query name in the Name box, select the Save Query definition checkbox, and then click OK.
7. In the Import Data dialog box, click OK to import.

To refresh the Internet data (the Web site does not have to be open):

1. Select the cell in the sheet containing the data.
2. From the Data menu, select Refresh Data.
OR
Display the External Data toolbar and click the Refresh Data icon. To display the External Data toolbar, select one of the toolbars, right-click and select External Data, and click OK.

To automatically refresh the Internet data:

1. On the External Data toolbar, click the Data Range Properties icon.
2. Select the Refresh every option, and set the number of minutes between each refresh action.
3. Select the Refresh data on file open checkbox to automatically refresh the data when the file is opened.

To run a saved query:
1. From the Data menu, select Import External Data, and then Import Data.
2. Select the saved query , and click Open.
 

Notes: Those pages are written by people for whom English is not a native language. Some amount of grammar and spelling errors should be expected. This is a Spartan WHYFF (We Help You For Free) site. It cannot replace the best teachers and the best books. The site contain some obsolete pages as it develops like a living tree... Some links on older pages are broken. Please try to use Google, Open directory, etc. to find a replacement link (see HOWTO search the WEB for details). We would appreciate if you can mail us a correct link. Search Amazon by keywords:     Open directory Research Index

Old News ;-)

[Oct 24, 2006] Microsoft Office Assistance You asked for it Crabby's top 5 Excel tips and tricks

Double-click your way to fast formatting

By default, when you copy text from a different program (such as Microsoft Word, Microsoft PowerPoint®, or even Microsoft Internet Explorer) and paste it into an Excel cell, the original formatting comes along for the ride. Sometimes this leaves you with a tiny little cell with GIGANTIC FORMATTING that you need to fix by hand.

The Paste Options button that looks like this:

pops up, and you can use it to keep the original formatting or to match the destination formatting (that is, the formatting you've chosen for your cell). However, if you're like me (or want to be like me — aren't you sweet), that is just too many steps if you know that you want to match the formatting of the cell. This is especially true if you have a another program and keep your cell's formatting

1. Select the text you want to pop into your Excel worksheet.
2. Press CTRL+C.
3. Switch back to Excel.
   Tip   Hey! Now's a good time to use that shortcut I told you about in the first tip!
4. Double-click in the cell, and press CTRL+V.

It's like magic in a click (or two).

And so you have it. Five decent little tips to boost your skill level and keep your work humming along. So start the music!

"We are what we repeatedly do. Excellence, then, is not an act, but a habit." — Aristotle

[Oct 24, 2006] 4 quick tips for using Excel

Creating a drop-down menu

Lynda Morris, president of NicLyn Consulting Corp, an Internet-based computer and network service company, often finds herself entering the same information or formulas in different parts of a spreadsheet. Instead of typing data repeatedly, she creates drop-down menus that save typing time. To create a drop-down menu:

•	Open a worksheet and label your menu (Days for example) in the top cell of a column.
•	Enter data (days of the week, in this case) in the cells below the label (such as A2 to A8).
•	Highlight the list, including the label.
•	Click the Insert menu, then click Name, and then Define.
•	In the box under Names in workbook you will see the name of the range (Days). In the Refers to box, it should list "=Sheet1!" followed by the range (=Sheet1!$A$1:$A$8).
•	Click Add and then OK.
•	Select any cell or range of cells in which you want to enter data.
•	Click the Data menu and then Validation.
•	From the Allow drop-down menu, select List. In Source, type "=" and the name of your range (=Days). Make sure that the In-cell dropdown box is checked.
•	Click one of the cells you highlighted to see your drop-down menu. Choose the item you want to enter in the active cell. Enter data from your list, in any order, in each of the cells you selected.

[May 22, 2006] freshmeat.net Project details for SpreadsheetWriteExcelSpreadsheet::WriteExcel 2.17  by John McNamara

About: Spreadsheet::WriteExcel is a Perl module which can be used to create native Excel binary files. Formatted text and numbers can be written to multiple worksheets in a workbook. Formulas and functions are also supported. It is 100% Perl and doesn't require any Windows libraries or a copy of Excel. It will also work on the majority of Unix and Macintosh platforms. Generated files are compatible with Excel 97, 2000, 2002, and 2003, and with OpenOffice and Gnumeric. An older version also supports Excel 5/95.

Changes: This release includes minor bugfixes and enhancements.

[Feb 16, 2006] Write VBA with the Macro Recorder in Word and Excel Take advantage of autogenerated code to speed up your coding efforts.

[Jan 13, 2004] Excel 2003 Power Programming with VBA (Excel Power Programming With Vba)

by John Walkenbach (Paperback - Jan 13, 2004)

Buy a book to match your background and needs, February 11, 2004
  Needing to finish my first Excel add-in, and frustrated by the incompleteness and obscurity of MS's help system, I picked up this book after reading warm recommendations from readers of earlier versions.

If you have never programmed Excel before, but have programmed a tiny bit in some other language, and do not have great ambitions for software development, this might be a fine text. It is quite readable and full of useful information. Walkenbach introduces VBA quickly, which is great, but so quickly he forgets to say what most of the language constructs do. His approach to teaching the Excel object model is to provide several fairly well written examples of little macros and utilities, each one with a clear English explanation. Unfortunately, if the technique you need does not appear in any of these examples, you are out of luck, because his explanations are neither extensive, detailed, nor thorough enough to impart a good understanding of what is going on. This, coupled with Excel's erratic behavior (mis-type a property name and watch your user form mysteriously disappear, for instance), makes it very difficult to become independently productive without spilling a lot of sweat and tears.

The book's strengths include the numerous and well-organized examples provided on the companion CD; the occasional sidebar that offers first-hand knowledge of bugs, inconsistencies, and strange design; fairly broad, if incomplete, coverage of the major aspects of Excel VBA programming; and very clear indications of differences among various Excel versions (97, 2000, 2003 mainly). Walkenbach is obviously an expert and has been so for a long time.

The weaknesses become apparent in contrasting this book with, say, Roman's text (O'Reilley). Where Walkenbach gives a macro to display all the icons associated with the several thousand Excel 'FaceId's, Roman publishes the complete table as an appendix. Where Walkenbach loosely skims over the properties of many key objects, such as ranges and charts, Roman takes the time to provide a terse but useful description of nearly every property, as well as a very illuminating diagram of the object hierarchy. Where Walkenbach completely omits to describe how VBA works, Roman actually offers a deeper explanation (showing how object references are arranged in memory, for instance, and describing exactly how a for..next loop is executed). Boring stuff for some, maybe, but a huge time saver for those who appreciate that the details matter. For someone who either has a lot of programming experience, or who plans to develop more than toy utilities or one-off apps in Excel VBA, Roman's approach is much more useful than Walkenbach's.

If Walkenbach is appropriate for your background and ambitions, then you will probably agree it is a four- or five-star effort. Otherwise, you will likely be somewhat disappointed and, like me, will quickly find yourself looking for another book.

Excel 2003 Formulas Books

by John Walkenbach

• Paperback: 864 pages
• Publisher: John Wiley & Sons; Bk&CD-Rom edition (October 17, 2003)
• Language: English
• ISBN: 0764540734
• Product Dimensions: 9.2 x 7.4 x 1.9 inches
• Shipping Weight: 2.7 pounds. (View shipping rates and policies)
• Average Customer Review: based on 8 reviews.

Walkenbach and Excel...a great combination!, November 24, 2003
 

Reviewer:

William A. Huber (Rosemont, PA USA) - See all my reviews

Reviewer:

Claire (Jasper, Indiana) - See all my reviews

Excel 2003 Formulas provides some amazing ways to really master Excel. Walkenbach's formulas are the best! He covers operators, naming techniques, debugging, auditing, developing custom VBA functions, array formulas, imported 1-2-3 files, etc. Oh, and the CD that comes with this one is load too! Keep it within arms reach, August 19, 2005
 

Reviewer:

Mark Jeffrey Lipin (Arizona) - See all my reviews

If you only get 1 book on Excel, buy this book! Its simplicity of use and easy reading style make it a perfect companion for a basic to an experienced Excel user (or abuser).

Excel Scientific and Engineering Cookbook (Cookbooks (O'Reilly)) Books David Bourg

Great showcase of Excel's mathematical capabilities, February 6, 2006

Reviewer:

calvinnme "Texan refugee" (Fredericksburg, Va) - See all my reviews

It is often either inconvenient or financially unfeasible to solve every mathematical problem with a standalone procedural language or an expensive application such as Matlab or Mathematica. Since Excel has such advanced computational capabilities built into it and is ubiquitous in offices and homes, it would make sense to mine this application for numerical problem solving techniques. Bourg does a masterful job of presenting the tools available in Excel and showing the reader how to put them to work to solve real world engineering and mathematical problems. First, he spends a couple of chapters briefly going over Excel and its language VBA (Visual Basic for Applications). Next he goes over some intermediate level tasks such as collecting and cleaning up data, charting including 3D charts, and statistical analysis tasks such as correlating data and generating random numbers. Next Bourg moves into purely mathematical tasks such as working with discrete Fourier transforms, manipulating matrices and vectors, as well as working with basic mathematical functions. Once you have learned to use these mathematical tools in Excel, Bourg uses them to show you how to perform curve fitting and regression, solve equations, perform integration and differentiation, and solve both ordinary and partial differential equations. He even spends time on performing multivariable calculus and the finite element method in Excel. Chapter 13, on optimization, was my favorite chapter. Here, Bourg is actually getting into a little algorithm analysis and design via linear programming and genetic algorithms. This chapter showed me some truly innovative uses of Excel. The final chapter deviates from the scientific flavor of the book and concentrates on more traditional financial applications.
The writing is very accessible, the examples are clear and very creative, and the author does a tremendous job of capturing the range of Excel's mathematical capabilities. Do not expect mathematical theory in this book. Bourg assumes you already know how to set up a mathematical problem and that you just need a computational platform and tools with which to perform your work. It is nice to know I am not necessarily held hostage by Mathworks(maker of Matlab) every time I need to solve a mathematical problem of any level of sophistication. Highly recommended. The table of contents is as follows:
1. Using Excel
2. Getting Acquainted with Visual Basic for Applications
3. Collecting and Cleaning Up Data
4. Charting
5. Statistical Analysis
6. Time Series Analysis
7. Mathematical Functions
8. Curve Fitting and Regression
9. Solving Equations
10. Numerical Integration and Differentiation
11. Solving Ordinary Differential Equations
12. Solving Partial Differenby Gerald Knight tial Equations
13. Performing Optimization Analyses in Excel
14. Introduction to Financial Calculations

Analyzing Business Data with Excel

by Gerald Knight

• Paperback: 262 pages
• Publisher: O'Reilly Media, Inc.; 1 edition (January 3, 2006)
• Language: English
• ISBN: 0596100736
• Product Dimensions: 9.1 x 6.7 x 0.7 inches
• Shipping Weight: 14.7 ounces. (View shipping rates and policies)
• Average Customer Review: based on 1 review.

Recommended Links

oreilly/Excel Annoyances

• Chapter 3: Formula Annoyances (PDF Format)

• Over 70 Free Downloads