S'il Vous Plait, Messrs. Google and Google inform me, translates as "If you please".

Couldn't be more appropriate, if you ask me.

The (quite frankly) awesome Jon Peltier has come out with a survey asking us Excel users a couple of questions. And in our humble opinion, anything we can do that will help Jon make the Excel world a better place to live in is well worth our time. 

So head on over to this link, and  click the appropriate boxes, please.

Danke!

Ever wondered if it was possible to convert Roman numerals to Arabic in Excel?

Given Excel's proclivity in coming up with formulas that range from the impressively versatile to the mind-bogglingly vague, you'd figure the odds are good, and as you're about to find out in a minute, you wouldn't be far off the mark.

But first, a little lesson in what we're talking about. Arabic numberals are the numbers that you and I use in everyday life. You know, 1,2,3 and so on. Roman numerals are those infuriatingly complicated beasts that seem resolutely indecipherable at the best of times. You know, I, II, III and so on.

I, II and III might not be such a problem, but do you have any clue at all how to write 523 in the Roman numeral system? I bet you don't, and I don't blame you at all.

Still, there are times in our lives (when reading incomprehensible legal documents, for example) when it is mighty useful to be able to read Roman numerals - this Excel tutorial is about how to go about doing this in Excel.

As it so turns out, there is a formula for taking Arabic numerals and converting them into Roman ones. Take a look at this simple list of numbers, for example.

Now, most of us will be able to guess that 1 is I, 5 is V and 17 is... umm, well, XVII? Anything beyond that, and I, at any rate, am way out of my comfort zone.

So what is to be done? A simple formula called (duh!) ROMAN, that's what is to be done used.

Don't worry about the zeroin the syntax, it doesn't really make too much of a difference to anything in our case. In fact, you could safely omit it altogether from the formula.

And oh, in passing, note that 523 is to be written as DXXIII. Might turn out to be useful during Trivia Nights at the local pub.

So that is how you go about converting Arabic numerals to Roman ones.

Which made me wonder (I wonder a lot... a major failing, in the opinion of some people) about a way to get the priginal numbers back. In other words, might it be possbile to enter Roman numerals, and get back Arabic numerals? Turns out there isn't a formula called ARABIC, sadly.

But hey! That doesn't mean there isn't a workaround!

For if writing on this blog and working on Excel has taught me anything, it is that it is always possible to conjure up a workable solution in Excel. Given enough time along with a dash of inspiration and a splash of ingenuity, almost any numerical problem can be solved. And so it proved to be in this case. A couple of quick searches on Google led me to this page, where a rather ingenious solution is provided. I won't expound upon it here, save to note that it is a very nifty use of three formulas at once (MATCH, ROW and ROMAN).

You get to learn something new everyday, now don't you?

Tips, Tricks, and Insights

The always awesome Chandoo comes up with a tip that needs to shared as widely as possible, more about which in just a second.

Have you ever needed to enter numbers in a sheet where for whatever reason, they need to be entered as 001, rather than 1?

Of course you have, who hasn't? Well, in that case, you have also obviously suffered from the bang-my-head-against-the-wall syndrome, because Excel is, in this case, more stubborn than an obdurate mule.

If you don't know waht I'm talking about, simply fire up an Excel sheet and enter "001" without the inverted commas in any cell

... and hit Enter

Slightly frustrating, isn't it? You want Excel to show the leading zeros, but Excel couldn't care less.

There are a couple of  workarounds to this, which are mentioned in Chandoo's blog post linked to above, but there's one that is even simpler than the solutions he has mentioned... simply append a leading ' to the number you wish to enter.

Once you press enter, the leading zero's don't disappear!

The only thing is that Excel gently reminds you that what you've entered as text really seems to be a number:

And the number, since it has been formatted as text, is left-aligned. But it is a pretty quick workaround all the same!

Tips, Tricks, and Insights

Every now and then, our sojours on the Internet seas lead us to places where not too many people have trodden before.

These virgin, unexplored islands are often that-a-ways for a reason; they're poorly designed, perhaps, or maybe the content is not too useful. We just heave resigned sighs and set sail from them, in search of better lands.

Every now and then, though, we just luck out, plain and simple. And the link that we're going to share with you today is one such internet treasure.

The unimaginatively titled "Site Map | Microsoft Office" - I mean, c'mon! - is actually a repository of link related to Microsoft Office products. And when I say repository, I mean every single link one could ever hope to keep in one's bookmarks and when I say Microsoft Office products, I mean every single last one of 'em.

Just insaney good stuff, this.

Just scroll down from the rather drab SmartArt-ish graphic at the top of the page...

... and immerse yourself in the Excel links section.

We've previously talked about Excel's own help section, and it is pretty darn good. But if ever you need to forage through the big bad world wide web... well, this is as good a place as any to start.

Happy surfing!

Tips, Tricks, and Insights

Just a quick post, this, and it isn't going to make you any better at Excel.

It'll make the world a more cheery, whacky and weird place, though, that much we can guarantee.

Presenting the world's first ever, one and only... (drum roll, please)...

Ode To Vlookup:

VLOOKUP, VLOOKUP,

How do I love thee?

Let me count the ways ...

The tens of thousands of records, nay,

The now 1,048,576 potential records per sheet,

A full 16,384 potential fields wide,

Would be a mountain of bad data

Too high to climb...

Whether tracking statewide underground facility excavation violations,

National subcontractors' software bug fixes,

Or hospital CFOs' every financial metric desires,

My eyes grew weary and my fingers waxed numb

With every scroll up and down those endless records,

Scanning for that needle of a matching value

In a towering haystack of a list -

Over and over and over again.

--Kip J.

This is from the otherwise-quite-sober Excel blog over at Office Blogs. They usually are a pretty good site for figuring out stuff on the net about Excel, but we must say they have outdone themselves this time around.

Take a bow, all of ye.

And hey, if you feel the urge to wax lyrical about, say SUMPRODUCT, you know where to go.

Tips, Tricks, and Insights