Microsoft Excel Training | Online Excel Training

Top 5 Excel Formulas That Are Just Plain Weird

Formulas,Tips and tricks Sean Duffy

You might think this post to be a little offbeat, a little off the beaten path – and we wouldn’t argue with you.

What we have done in this Microsoft Excel tutorial is looked through Excel’s capacious archives to come up with five formulas that won’t be used all that often. Sure, there’ll be people over the world who use these day in and day out, and thank heavens for the very existence of these formulas – but there won’t be mroe than ten of them, I’m sure.

All right, that’s an exaggeration, maybe – but seriously: how many of you knew about the “Roman” formula, for example?

a) The Roman:

The Roman formula does what the Roman formula promises. It returns the Roman value of an Arabic integer. Arabic integers are the good old numbers that we always end up using. 543, for example. But I bet you a rather sizeable sum you can’t come up with the Roman numeral equivalent. Well, Excel can. 

It’s DXLIII, for what it’s worth. It may be a neat party trick, or it may be of great help if you are in academics. But it’s a useful thing to know, the Roman formula – that’s for sure.


b) Combin

In how many ways can you choose 2 objects out of, say, 50? Brings back horrific memories of math in the sixth grade or thereabouts? Are you about to break out into hives at the mere thought of that accursed blackboard and those dreaded sums?

Fret not, want not. Fire up Microsoft Excel, and enter “=combin(50,2)” in any cell and hit enter. Learn that there are 1225(!) ways of choosing 2 objects out of 50. Who would have thought?

c) Now

We couldn’t resist putting this one in for it’s immense, well, coolness. Any formula called “Now” deserves to get in simply because it is called “Now”. Still, it does what you think it would. It simply returns today’s date and time as of… wait for it… now.

d) Rept

This is Bart Simpson’s dream come true. If only the blacboard was a spreadsheet!

The rept formula takes a text string as an input, and spews it out as many times as you like.

Take that…

Use this…

…to get this…

5) Bahttext

And we saved the best for last. We confess, we don’t quite get what this is for. Put a number (say 23) in a cell (say B2) and enter the formula “=bahttext(B2)” in any other cell (without the quotation marks). Excel returns the number converted into the Baht text, with the word “baht” added at the end. It looks something like this:

Excel’s help section does tell us that the output can be changed in the Regional Settings tab in your Control Panel (assuming you are running Windows) – but quite why it should be in Baht to begin with (and why the formula should be called bahttext in the first place) is sadly not revealed.

That being said, if any of you do know the answer to this little mystery, do let us know in the comments!