All Excel Templates Normally an 80% saving at $299.00. Super Special $175.00!
Check out this months super half price offer !
|Microsoft Excel Add-ins||Financial Add-ins and Software|
|Microsoft Excel Training & Tutoring||Conversion Software|
|Microsoft Excel Templates||Construction and Estimating Software|
|Excel, Word, Access Password Recovery||Financial Calculators|
|Corrupt Excel, Word, Access File Recovery||Business Planning|
|Database Software||Excel on the WWW|
|Windows & Internet Software||Downloadable Excel Training|
|Barcode, Fonts, ActiveX, labels and DLL's||OUR CURRENT SOFTWARE SPECIALS!|
|Chart Tools & Add-ins||The Analysis Add-ins Collection|
|Trading Software & Add-ins||TOTALLY FREE 24/7 EXCEL, VBA SUPPORT!|
What's New at OzGrid | 50% Off Offer | Excel Tips and Tricks | Excel VBA Tips and Tricks
February was another busy month at OzGrid, with the final touches being put to our book - "100 Excel Hacks". We are currently in the process of proof-reading and checking, then re-checking all Hacks and making sure we have the right figures in the right places. Hopefully we have picked up any outstanding errors as the book is off to the printers at the end of the month, it should be out on the shelves in April.
Apart from the book, we have been busy preparing lectures and assessments as the school year commenced this week and students have Start ed at College. This will certainly keep us busy for the next few weeks while the students (and us) settle in. Also keeping us busy is the Consulting Services side of OzGrid.com. Business is steadily increasing in this area, due to the extra advertising we have undertaken, we see hope this will continue to grow in the future.
Over the next few months we will Start updating our training lessons to reflect Microsoft Excel XP. We will keep you informed as to when they will be available.
Seems like we have lots to keep us busy, so we must keep Excelling....
This month we are extremely happy to be able to offer to all our newsletter membersNeural Networks Signal For Microsoft Excel at a 50% discount. This is an extremely popular Excel add-in for traders. The offer is open to all newsletter members until 29 Feb 2004.
This month I thought I would share with you the most important (I cannot stress that enough) part of using Excel, data layout. Without doubt the single biggest mistake made by those who use Excel is their data layout. Out of every 10 Workbooks I see, at least 8 are set-up in the wrong manner. This then means that, 8 out 10 Workbooks are built on a very poor foundation. If your foundations are not correct you cannot expect to build a good solid spreadsheet.
Excel, although not a true database, often expects data to be laid out in a database fashion. This simply means that you should lay out your raw data in a standard table manner. Have your headings going across row 1 of your table and all associated data laid out directly underneath these headings. There should be no blank columns or rows at all in the table. See the two tables below to see what I mean.
Correct Layout of Raw Data
Incorrect Layout of Raw Data
Let's look at why the second table is just one of many incorrect ways to layout raw data.
Remember, this is for your raw data, not your final results. Each Workbook should have a Worksheet that houses raw data (and can be used for further data entry). Try to keep all related data in the one table and on the one Worksheet. As a general rule, the more Worksheets you use for raw data the harder it becomes to extract and return meaningful results.
Once you follow the above rules you will find you are able to do things with Excel that most will never know. Some of features that will become easy to use when/if following these rules are;
Too many people make the mistake of using their raw data as their only data and hence their final resulting data. There should rarely be a need for users to see the raw data. Users only need to see the results they are after. The raw data sheet can be completely hidden from view (Format>Sheet>Hide) and only shown when/if more data is to be added. Think of the raw data sheet as your database not your information.
The above information, in my opinion, is the most valuable bit of information I can pass on. To read more about spreadsheet design go to: Efficient Excel Spreadsheet Design
For those of us that use Excel a lot, both the interface and VBA, you will appreciate just how painful dates in Excel can be at times! This is particularly true for those of us that live outside the US. Excel has quite a rich selection of date and time functions and even more with the Analysis Collection Add-in installed. One function I would like to see in Excel would be one that can tell me how many specified days there are in any specified month of any specified year. There is probably a way to do this without VBA (never really attempted it) but I would imagine it would be one of those horribly long deeply nested functions. Anyway, I decided to set upon writing my own custom function (seems well suited) as VBA has even more date and time functions available. The reason I see this problem is well suited to a Custom Function, as opposed to several nested standard Excel functions, is because we should not have to worry about looping through and reading cells. This is what makes most Custom Functions so horribly slow, even compared to a deeply nested mega formula.
The function below can be used to determine how many days (e.g Mondays etc) there are in any specified month. For example
Will return 5 as there are five Wednesdays in the month of December in 2003
Will return 4 as there are four Thursdays in the month of December in 2003
To be able to use this custom function in a Workbook, you must first place the code below into a standard module.
Function HowManyDaysInMonth(FullDate As String, sDay As String) As Integer
Dim i As Integer
Dim iDay As Integer, iMatchDay As Integer
Dim iDaysInMonth As Integer
Dim FullDateNew As Date
iMatchDay = Weekday(FullDate)
Select Case UCase(sDay)
iDay = 1
iDay = 2
iDay = 3
iDay = 4
iDay = 5
iDay = 6
iDay = 7
iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
(Year(FullDate), Month(FullDate) + 1, 1)))
FullDateNew = DateSerial(Year(FullDate), Month(FullDate), iDaysInMonth)
For i = iDaysInMonth - 1 To 0 Step -1
If Weekday(FullDateNew - i) = iDay Then
HowManyDaysInMonth = HowManyDaysInMonth + 1
Now simply enter the function into any cell as shown above.
If you are not familiar with DateAdd, DateSerial, Weekday, Day, Month and Year functions in VBA I strongly suggest reading up on them via the VBA help. They can make life working with dates (in particular the DateSerial) so much easier.
Until next month, keep Excelling!
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :