Year: 2006

Tamil song lyrics quiz 1995s

Here are words from the middle of 10 songs from 1995-1999. Can you guess which movie they are from? (Films are NOT repeated)

Don’t worry about the spelling. Just spell it like it sounds, and the box will turn green.

Tamil song lyrics quiz 2000s

Here are words from the middle of 10 songs from the 2000s. Can you guess which movie they are from? (Films are NOT repeated)

Don’t worry about the spelling. Just spell it like it sounds, and the box will turn green.

User-defined functions to get cell formatting

Sometimes you want to check the colour of a cell, or whether a cell is bold. This can be easily done with user-defined functions (UDFs). (To create a UDF, press Alt-F11, Alt-I-M, and type the code below.)

User defined functions to get the background colour and bold value of a cell

You can use ISBOLD(cell) to check if a cell is bold, and BGCOLOR(cell) to get the background colour of the cell. This lets you selectively process bold or shaded cells. The two examples below show how you can add only the cells in bold, or only the shaded cells.

Example to selectively add shaded cells

Example to selectively add bold cells

Rather than use an additional column for ISBOLD or BGCOLOR, you can use an array formula, like below. (Remember to press Ctrl-Shift-Enter instead of Enter after typing this formula)

Example to selectively add bold cells using a single array formula

But first, you need to change the UDF to return an array rather than a single value. So IsBold will have to be modified as shown.

User defined function isBold modified to return an array

User-defined functions that process arrays can be very powerful. It can bring the full power of functional programming into Excel. I’ll describe some next week.

PS: In case you’re wondering, Application.Volatile tells Excel to recalculate the function every time the worksheet is recalculated. When a cell is made bold, or shaded, the value of the cell doesn’t change. So Excel doesn’t recalculate any formulas. You’ll have to manually press F9 every time to recalculate these cells. And Application.Volatile ensures that when you press F9, these functions are recalculated.

User-defined functions in Excel

Excel lets you create your own functions. If you wanted to create a function that returned the distance between two points (x1,y1) and (x2,y2), you can create a function DIST that takes these 4 parameters, and use it as shown below.

Example of a user-defined function in Excel

To create such a function,

  1. press Alt-F11 to open the Visual Basic Editor
  2. insert a new module (Alt-I-M)
  3. type the following code:

    Visual Basic code for the DIST user-defined Excel function

Anything you declare as a “Function” in Excel’s Visual Basic automatically becomes visible in the Insert-Function dialog box under the category “User Defined” (see examples). The function is normally saved with the file. This is a good idea if you’re going to distribute the file. You can also save your functions in your personal.xls file and load it on startup.

There are 3 places where I suggest using UDFs.

  1. You need to repeat a formula or use an additional cell to get the job done (e.g. replace Excel errors with empty strings)
  2. You can’t get the information from a formula (e.g. a cell’s background colour)
  3. It’s very cumbersome to get the information using formulas (e.g. regular expressions)

Let’s take the first one: replace Excel errors with empty strings. Normally, you’d store the results in a cell (say A2), and have another cell with the formula =IF(ISERROR(A2),””,A2). Instead, create this function NOERROR:

Function NOERROR in Excel Visual Basic

Now you can enclose any Excel function inside a NOERROR() and it’ll filter out the errors.

How the NOERROR function is used

Notice that cell E2 would’ve had a #N/A error if you’d just used the VLOOKUP. This function also filters out #REF, #DIV/0!, #NAME? and all other errors.

BTW, you see column F displaying the formula in column E. I didn’t type it out. That’s another UDF.

FormulaString function returns the formula of a cell

I will talk about the other two places where you use UDFs tomorrow.

Absolutely convergent series

I’ve seen many proofs that 1=2. Here’s a classic.

Proof that 1=2 using algebra

The (not-so-subtle) error in the above proof is that we’re cancelling (a-b) on both sides, when (a-b) equals zero. That is, we’re dividing by zero on both sides. That completely invalidates the equality.

Another proof uses the fact that the square root of a number can be both positive or negative.

Proof that 1=2 using square roots

(Proving -1=1 is the same as proving 1=2. Once you have one wrong proof, you can prove every other falsehood.)

The flaw here is that the square root of 1 is 1 and -1. So right after the square root symbol appears, every equation should have a plus-or-minus symbol on both sides.

The most convincing proof uses absolutely convergent series as the key idea. Here’s how the proof goes.

Proof that 1=2 using non-absolutely convergent series

Most people initially think that the flaw is in the re-arrangement of the series. That’s not true! The re-arrangement works just fine, and you can prove that every term is correct to infinity.

The flaw is subtler.

When an infinite series is summed, it can be summed in any order. But the total may vary depending on the order you sum it up! You are guaranteed that the total is the same only if the series is absolutely convergent. That is, if the sum of the absolute values of each number is finite. (See the Wikipedia article on the Riemann series theorem.)

For the log 2 series, it’s not absolutely convergent. The series diverges, as shown below:

log 2 is not absolutely convergent

So, by re-arranging the series for log 2, we’ve invalidated the equality anyway.

This fact once saved an entire class. We had a problem in our first year physics course to which the answer was the series above. (It had to do with calculating the electromagnetic potential created by an array of charges.) Since the series is not absolutely convergent, and every possible answer was correct, the whole class got marks for this question, as long as they attempted it.