Tuesday, September 27, 2005

Excel Links


I am constantly amazed at who reads this site. Even more amazing and humbling is the people who link me. Giants in the Excel world who I grew up (not literally) reading and listening to link me! Wow. Thanks a lot guys.
Here are some of these sites here.
Excel Pragma
Excel Blog
Unofficial Office Stuff
Excel Tricks
Excel Geek
Excel Watch - John Walkenbach himself!
And Johns Main Blog lists me too! Wow!
AJP Excel Information
Daily Dose Of Excel
Andrews Site


Posted by: Jethro | 8:36 AM |

Wednesday, September 21, 2005

Using the LARGE and SMALL functions in Excel


Excel has a number of statistical functions that are very powerful in analysing large chunks of data.
While the MAX and MIN functions find the largest and smallest values in a list respectively sometimes you need to find the 2nd largest or 14th smallest etc.

The syntax is =SMALL(array,n) where n is the nth smallest value to return. The syntax for LARGE is the same.
Of course n can be a reference to another cell or a function returning a value. The array is a range of cells Eg. A:A, R24:T34 etc.

Make sure that n isn't greater than the number of items in the list or Excel will return a #NUM! error.


Posted by: Jethro | 10:09 AM |

Sunday, September 11, 2005

Maximising Dialog Boxes in Excel


From Andrew's Excel Site comes a great tip about Excel.
By the way, here's something that might be useful for later versions of Excel. If you double click the title bar of dialog boxes such as Find, Open or Save As, they will maximize which makes things a lot easier to see. Double click the title bar again and it minimizes (returns to it's normal size).
Try it - its great.


Posted by: Jethro | 12:46 AM |

Tuesday, September 06, 2005

Using the Advanced Filter in Excel


There are some specific instructions to be followed in order to use the Advanced Filter to filter a list and return the result to another location.
From the Help file:
The Advanced Filter command on the Data menu lets you use complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.) to filter a range, but it works differently from the AutoFilter command in several important ways.

It displays the Advanced Filter dialog box instead of the Custom AutoFilter dialog box.
You do not type the complex criteria in the Advanced Filter dialog box as you do in the Custom AutoFilter dialog box. Rather, you type the complex criteria in a criteria range on the worksheet and above the range you want to filter. Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the complex criteria.
Although you can filter a range in place, like the AutoFilter command, the Advanced Filter command does not display drop-down lists for the columns.
Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure there is at least one blank row between the criteria values and the range.
Here is an example. You have a list of values in a spreadsheet with a header called salaries. This is in Column A.

First make sure there is plenty of room - at least 3 blank rows between row 1 and the header of the list.
Then copy the list header to cell A1
In cell A2 type ="<50000" - including the quotes. This will create a criteria that will return all salaries under $50,000.
This creates a text criteria. Of course you could use the concatenate formula to derive this.

Select the range of data in the list including the header but not the criteria range.
Now click Data | Filter | Advanced Filter on the menu bar.
Click the radio button for Copy to another location.
The list range should already be correct. (selected data)
The criteria range needs to include the header so in this instance $A$1:$A$2
Click in the Copy To box and then click the place on the spreadsheet that you want the result returned and click OK. If you only want one instance of each duplicate value check the Unique records only box first.


Posted by: Jethro | 1:29 PM |