Dynamic Address in Excel

Today I was solving a problem in Microsoft Excel and part of the problem was to reference dynamic cells. Rather than hard coding cell-reference such as: A1 or $A$1 I needed to dynamically create a reference based on other cells in the workbook. To do this there is an excel function called ADDRESS (see below):

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

Row_num      is the row number to use in the cell reference.
Column_num  is the column number to use in the cell reference.
Abs_num      specifies the type of reference to return.
A1               is a logical value that specifies the A1 or R1C1 ref.
Sheet_text   specifying the name of the worksheet to be used.

Now row_num can be hard coded or dynamically referred to based on other cells on the workbook. Address returns an address which then can be passed to INDIRECT to get the address’ value for example:

=INDIRECT(ADDRESS(L1,2,TRUE,,"Bal Query - CFY"))

The above code returns the L1-th row (whatever value L1 contains in the current sheet) and 2nd column’s value of the “Bal Query – CFY” sheet.

Geometric Mean (GEOMEAN) in Excel 2002

Today I was trying to find the geometric mean (function GEOMEAN) of some (about 90 sample set) data in Excel (ver: 2002). But for some odd reason I kept getting #NAME or #VALUE errors. Although as far as I could think of the geometric mean of these numbers should exists. So I looked up Excel-help and realized that Microsoft Excel is using a mathematically correct yet very naive method of computing the geometric mean. They are computing the product of these N numbers and then taking the Nth root (this is really bad). If the result of the product is an overflow the Nth root is still an overflow. This makes GEOMEAN almost useless.

Anyone who has taken introductory numerical analysis course know that the logarithm of the product of a bunch of numbers is equal to the sum of their individual logarithms, and therefore easily replace the GEOMEAN algorithm to the following: add (notice add and not multiply) the logarithms of the N numbers together, divide by N, then exponentiate.

Here’s an example:

POWER(10,(LOG(A1)+LOG(A2)+LOG(A3)+LOG(A4)+LOG(A5)+LOG(A6)+LOG(A7)+LOG(A8))/8)

Easy! I ended up writing the above algorithm as a formula to find the geometric mean. I am sure the problem still exists in Office XP — but can’t verify that until I go home tonight.

Date Format (using XSLT)

It was sort of surprising to find out that XSLT does not have any predefined date formatting function. So after spending sometime on my own I came up with this solution:

<xsl:template name="format-date">
	<xsl:param name="date" />

	<xsl:variable name="monthName" select="substring-before($date, '/')" />
	<xsl:variable name="day" select="substring-before(substring-after($date, '/'), '/')" />
	<xsl:variable name="year" select="substring-after(substring-after($date, '/'), '/')" />
	<xsl:variable name="month" select="substring(substring-after('01Jan02Feb03Mar04Apr05May06Jun07Jul08Aug09Sep10Oct11Nov12Dec', $monthName), 1, 3)" />

	<xsl:value-of select="concat($month, ', ', $year)" />
</xsl:template>

You can call this function like this:

<xsl:call-template name="format-date">
	<xsl:with-param name="date" select="@date" />
</xsl:call-template>

Where @date is the date string (in this case the date attribute) you want to format. For instance this XSLT function rewrites dates written in this format: 06/23/2004 to. Jan. 2004. If you want to include the day in the resulting date, just modify the xsl:value-of concat line and concat the day also.

MSDN Magazine

The August Issue of MSDN Maganize is now online. You can download the magazine in Windows help format (.CHM) and read it later at your own pace. The MSDN team did a really good job on this..

And best of all it’s free (the magazine generally costs around $6 dollars).

Search for PGP key given a keyserver

In Linux just do this:

gpg --keyserver "ldap://certserver.pgp.com" --search-keys "<e-mail address>"

Note the option: –keyserver can be other Keyservers like the MIT Key Server (pgp.mit.edu).

Changes in Windows XP SP2

Microsoft did an amazing job on Windows XP SP2. Just take a look at all the changes you have to make on a site to make it properly viewable by a user using Windows XP SP2.

All the changes seems to make logical sense.

Default Documents and Settings location

The default location for user profiles in Windows XP/2000/2003 is “C:\Documents and Settings”. Underneath this folder each user will have a sub-folder which contains desktop items, Start Menu items, temp folders, etc. Due to backup issues, I had to move this folder to another drive. Unfortunately there isn’t any easy way to do this. To change the location of the profiles directory change the following registry key:

Hive: HKEY_LOCAL_MACHINE
Key: Software\Microsoft\Windows NT\CurrentVersion\ProfileList
Name: ProfilesDirectory
Data Type: REG_EXPAND_SZ
Value: path

Note that this will only take effect for users logging in after the change to the Registry. You may also have to copy the Default User profile to the new location.

Microsoft does not support this configuration! Because although Microsoft may follow the convention of reading the registry key: ProfilesDirectory to store data, other software vendors may not which might cause problems.

Human Mind

“Aoccdrnig to a rscheearch at Cmabrigde Uinervtisy, it deosn’t mttaer in waht oredr the ltteers in a wrod are, the olny iprmoetnt tihng is tath the frist and lsat ltteer be at the rghit pclae. The rset can be a ttoal mses and you can sitll raed it wouthit a porbelm. Tihs is bcuseae the huamn mnid des not raed ervey lteter by istlef, but the wrod as a wlohe. Amzanig huh?”

Painting with Light

Painting with light is a technique that’s used by both painters and photographers. This technique is being used since the darkroom ages, but now have extended to digital techniques giving the photographer more control than ever before. While trying to learn this techinique myself, I found this online and I thought I share:

• Painting with Light from Solo i, Elemental 2002.
• Adobe Photoshop – Paiting with Light

Digital Workflow with Windows XP

A very helpful short book/article on the digital photography workflow — under Microsoft Windows XP.