Worksheet Examples

Worksheet Function Example Files (A work in progress)

My advice is for you to download the files directly to your hard drive by right-clicking on the link and then selecting "Save Target As..." - otherwise the Excel files may not work fully in your browser window!

Three ARRAY Tutorial type workbooks:
To help you through these, make sure that you first read all of the Text Boxes, Comment Boxes and on clicking in Cells, read the formula from the Formula Bar.
Many of the ideas and some help on their solutions came from the Newsgroup: microsoft.public.excel.worksheet.functions (Click here if you have Outlook Express, or here if you don't have a Newsreader of any sort and then scroll down to excel.worksheet.functions) You'll get lots of excellent advice from many Excel philanthropists!

Arrays - Introduction, Examples and Practice.xls (132KB)
This Workbook begins with a sheet dedicated to reproducing the BOOLEAN Operators: NOT, AND, NAND, OR, NOR, EXOR, and other functions on several columns of Text and Numerical Data. The formulae almost exclusively use the SUM Function in Array form or the SUMPRODUCT Function. Often, a variety of different formulae are used to get the same result and, though the list is by no means exhaustive, it is hoped that it will be a starting point.
The second sheet leads the reader through the construction of quite a complex Array formula by breaking it down into its component parts.
The third sheet is an example of a practical form - bookkeeping-ish!
The fourth sheet is a practice sheet with answers generated by VBA (Press Alt + F11 to get into the VBA Editor - Click on the "Forms" directory, choose a Form and then double-click on the body of the Form to see the code as a small exercise in loops)
Array Example and Practice - Frequency Table (Using Forms).xls (52KB)
(Useful to print out the worksheet first and remake the whole thing in a new Workbook)
This is a single sheet Workbook in which a Frequency Array formula is used to complete a Frequency Table. Several added extras are included, such as how to embed a List Box in the Worksheet.
Array Example and Practice - Pupil Easy Marker.xls (84KB)
(Useful to print out all sheets first and remake the whole thing in a new Workbook)
This is a useful little Tutorial of four sheets that takes the reader through how to use Arrays to make up a useful Pupil Markbook for teachers. If you want to see a more versatile and robust Markbook check out my Teacher's Friend© Easy Marker

Dynamic Named Range Tutorial type Workbook:
To help you through these, make sure that you first read all of the Text Boxes, Comment Boxes and on clicking in Cells, read the formula from the Formula Bar.

Dynamic Named Ranges.xls (38KB)
This is a single sheet Workbook in which the user can see how to set up three different types of Named Range together with two simple applications: The Average of a Range and the Last Cell in the Range. (For more detail and applications click here to see my main Named Range Tutorial page)
(10/01/02 Updated for error in sheet name reference for Named Range "x" - Thanks Dave: www.OzGrid.com)

Links