S Anand

User-defined functions in Excel

March 23, 2009 / Excel tips / 1 Comment

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

August 31, 2006 / Simple explanations / 7 Comments

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.

Making a Tamil transliterator

March 23, 2009 / How I do things, Tools / 21 Comments

I’ve built a simple Tamil transliterator. You can type in words in English and it will spell them out in Tamil. You can copy-paste the Tamil above into Microsoft Word, etc.

You may need to turn on tamil scripts to see the Tamil fonts above. If you have Windows 98, it may not work well. If you’ve visited this page recently, you will need to refresh this page as well (press F5).

Browse through my Javascript to see how it works. Feel free to reuse.

I’ve also made a Google Gadget that searches Google in Tamil using this tool.

Here’s what to type:

Tamil English
அ a
ஆ A or aa
இ i
ஈ I or ee
உ u
ஊ U or oo
எ e
ஏ E
ஐ ai
ஒ o
ஓ O
ஔ au
க k or g
ங n
ச ch or s
ஜ j
ஞ n
ட t or d
ண N
த th or dh
ந n
ப p or b
ம m
ய y
ர r
ல l
வ v
ழ zh
ள L
ற R
ஷ sh
ஸ S
ஹ h

I also have a gadget that lets you search in Tamil.

Tamil song lyrics quiz 1

January 25, 2025 / Quizzes / 14 Comments

Here are words from the middle of 10 songs. 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.