Spreadsheet Page Blog

 My Interview

Chandoo, at Pointy Haired Dilbert post this: Interview with John Walkenbach on Excel and Banjo Charts

Here is the interview with John Walkenbach. John is famous excel personality. He has authored 50 excel books, numerous articles and has one of most popular excel related sites - spreadsheetpage.com.

I wore my treasured Excel error shirt:

Permalink | Posted in General on 27 March, 2009 11:58am

 On This Day

January 26, 1983: Lotus 1-2-3 was released.

The Lotus Development Corporation was founded by Mitchell Kapor, a friend of the developers of VisiCalc. 1-2-3 was originally written by Jonathan Sachs, who had written two spreadsheet programs previously while working at Concentric Data Systems, Inc. To aid its growth, in the UK, and possibly elsewhere, Lotus 1-2-3 was the very first computer software to use television consumer advertising.

1-2-3 was released on January 26, 1983, started outselling then-most-popular VisiCalc the very same year, and for a number of years was the leading spreadsheet for the DOS operating system. Unlike Microsoft Multiplan, it stayed very close to the model of VisiCalc, including the "A1" letter and number cell notation, and slash-menu structure. It was free of notable bugs, and was very fast because it was programmed entirely in x86 assembly language and bypassed the slower DOS screen input/output functions in favor of writing directly to memory-mapped video display hardware.

This reliance on the specific hardware of the IBM PC led to 1-2-3 being utilized as one of the two litmus test applications for true 100% compatibility when PC clones started to appear in the early- to mid- 80s. 1-2-3 was used to test general application compatibility, with Microsoft Flight Simulator being used to test graphics compatibility. Because all of a spreadsheet needs to be resident in memory, it also drove the race to utilize more memory, and extended memory and expanded memory techniques were needed to overcome the DOS limit of 640KB to allow larger spreadsheets - this was so important that a memory used/remaining indicator was displayed on-screen.

Permalink | Posted in General on 26 January, 2009 10:36am

 Automatic TRIM Patented

U.S Patent 7,475,086 granted to IBM: Method of automatically removing leading and trailing space characters from data being entered into a database system.

A computer Implemented method of automatically removing space characters from data being entered into a database system are provided. When a user creates a table in a database system into which data having leading and/or trailing character spaces may be entered, the user may specifically instruct the database system to remove any character spaces before entering the data into the table.

Consequently, when a piece of data is being entered into the table, the database system will determine whether the piece of data includes leading and/or trailing character spaces. If so, the database system will automatically remove the character spaces from the piece of data before it is entered into the table. Hence, the use of TRIM functions when retrieving data from the database system will be obviated.

It took three people from Texas to come up with that idea, and they thought it was so brilliant that it deserved to be patented.

Does this mean that the following VBA procedure violates the patent?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    On Error Resume Next
    For Each cell In Target
        If Not cell.HasFormula Then cell = Trim(Target)
    Next cell
End Sub
Permalink | Posted in General on 17 January, 2009 11:26am

 Recycling Calendars

2009 is almost here, and that means a new calendar. Or, you can just recycle an old calendar. If you think about it, only 14 different calendars exist. January 1 can occur on any of seven days -- but some years are leap years and have an additional day.

Assume column A contains years, (beginning with 1901) and row 1 contains the same list of years, arranged horizontally. Put this formula in cell B2, copy it down and across:

=IF(AND(WEEKDAY(DATE(B$1,1,1))=WEEKDAY(DATE($A2,1,1)),DATE(B$1,12,31)-DATE(B$1,1,1)=
DATE($A2,12,31)-DATE($A2,1,1)),"X","")

The cells that display an X indicate matching calendar years. For example, the 1901 calendar is identical to the 1907 calendar.

If you use conditional formatting to highlight the cells that contain an X, it makes an interesting pattern:

If you don't want to go through the trouble of creating a worksheet, you can use any of the following calendars for a 2009 calendar: 1903, 1914, 1925, 1931, 1942, 1953, 1959, 1970, 1981, 1987, or 1998.

By the way, if you start saving calendars in 2009, you will have a complete set of 14 different calendars in 2036.

Permalink | Posted in General on 28 December, 2008 2:37pm
Page 3 of 3 pages
[Previous page]  

Search the Blog


Blog Posts Categories

Subscribe

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy