Excel Everest

Formulas

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…

Cleaning up Data in Microsoft Excel

Formulas,Resources,Tips and tricks Sean Duffy

Sigh.

Wait, edit that.

Siiiiiiiiiiiiiiiiiiiiigh!

So you’re running a moderately successful business selling medical equipment, and these are good times. You’ve built up a great team of bright, enthusiastic people who genuinely love their work, are good at what they do, and are responding well to your leadership. Sales are on the up, and the money is pouring in from all sides. There’s just one teensy-weensy problem – these guys are horrible at Microsoft Excel.

And not just at the complicated stuff – you don’t expect them to run macros or build complicated pivot tables – but surely they can manage basic data entry?!

Here’s a very basic table that these guys are expected to build every week, except for the last column, which your secretary updates for you based on their performance.

So the agent’s name goes in first, sales managed in the previous month next, average selling price in the third column, total revenue in the fourth. Once this is done, the secretary, Chris, fills in the final column based on your recommendations, and you receive this report on the first Monday of every month. Simplicity itself, right?

Except that it ends up looking like this:

Roget’s thesaurus has a word for that kind of feeling. It goes something like this: Arrrrrghhhh!

Why can’t names be capitalized? Why can’t per unit prices and revenue be in dollars? Why can’t performances have the words (again!) capitalized?

Well, it’s an irritant, I’ll admit. But it’s nothing a little bit of Microsoft Excel can’t cure, let me assure you. Let’s get cracking, shall we?

Choose a new cell, and for the first colum, let’s use a formula called “Proper”. Proper does what proper says – it writes names properly.

Enter the same formula for all the cells in the first colum (and all the cells in the last column, while you’re at it), and things are looking much better already.

Now, as far as the dollar conundrum goes, you could change the formatting using this little feature on the ‘Home’ tab:

But did you know about the

A-Counting We Will Go!

Equations,Formulas,Functions,Tips and tricks Sean Duffy

So there are 21 students in your class, and you have just finished entering the marks they recieved in your latest exam (which was for a 100 marks). Curiosity assails you, and you wonder how many of your students got more than 40. Such is life with academicians – they sometimes think of the most random thoughts.

Still – here’s the list for your perusal:

Now, as we pointed out in the earlier post, there is the index finger method. But, as we also pointed out in the post, it is an almost guaranteed migraine inducer. This Excel tutorial points out a solution to your problem, and throws in a bonus – no migraine!

Use the COUNTIF function. The COUNTIF function only counts those values in a range which match a condition within the formula – if the condition is not met, no counting.

Here’s the formula one would use in our example:

Remember – non-numeric parts of your condition must be in inverted commas (“>”), and an ampersand (&) is needed to connect the text with the number (“>”&40).

But apart from that little niggle, you are more or less set to go. Hitting Enter will tell you that 15 students got more than 40 marks – I would consider that a decent performance really – wouldn’t you?

Our professor, of course, will not cease from his musings. Having nothing better to do on a lazy Tuesday afternoon, he wonders how many students got marks between 40 and 60. 

Well, if you happen to be the hapless TA helping out the Professor-with-the-insatiable-curiosity, fret not. And turn instead to the COUNTIFS function. The COUNTIFS function is the proverbial Big Brother to the COUNTIF function, with the ability to deal with more than one condition at a go.

It’ll look something like this:

And what’s more – it’ll return what you wanted it to – the number of students who scored between 40 and 60 – 5 in this case.

Keep these Count formulas in mind when you’re dealing with tricky situations and large amounts of data – it can save you a lot of time, and a lot of head-scratching!

Mapping the Great Unknown

Formulas,Functions,Resources,Tips and tricks Sean Duffy

Every now and then, my work involves wading through an ocean of data. I’m sure I’m not the only one in these data driven days, so it’s fairly certain that I have plenty of company the world over.

And I’m equally certain that most members of my tribe experience a sinking feeling the first time they cast an eye over the large amount of data that needs analyzing.

And it goes on and on and on! Go down a couple of rows, and then a dozen and then a hundred, but there’s no end in sight. Go right for a couple of columns, and then a dozen…. but you get the idea.

Now it’s almost a given that I’m going to have to use pivots or some equally big, nasty weapon to subjugate this monster, and that’s fine. But to begin with, I’d prefer to know the size of what I’m dealing with – and what I’d like is a couple of functions that tell me just that: how many rows, pray tell, and how many columns?

Turns out, there are a couple of functions that do nothing but just that: return the number of rows and columns for a specified array.

And here’s how it works:

Head on over to the first cell in your gargantuan array, like so. 

Hit ALT-I-R a couple of times, followed by ALT-I-C a couple of times. These are keyboard shortcuts that insert rows and columns in your sheet, leaving it looking something like this. 

Why do that? Well,  so that you can type in the following command in cell A1:

Now fill in the argument by selecting the first cell in your array, and hitting SHIFT-END-DOWN. If you hit ENTER post this little operation, Excel quickly, quietly and efficiently (as it its wont) returns just what you wanted, the number of rows in that rather large array.

Much the same operation along transpose-ian lines will give you the number of columns in your database as well. And hey, who doesn’t like a little formatting? So a touch here and a touch there will leave you with the following:

The point being, whichever unfortuante soul (including you!) needs to work on this file knows exactly what she is up a

Using the Left Formula in Excel

Formulas,Resources,Tips and tricks Sean Duffy

Do you happen to have well meaning people in your office who just don’t get Excel?

In their effort make life as easy for you as possible, they end up making it a lot more complicated?

Here’s an example that might seem all too familiar to you. Your boss has just finished reviewing the sales (in 2009) of each person in your sales team. Based on their experience, and his gut feel, he has assigned sales targets for everybody in 2010. These are expressed as percentages of sales achieved in 2009. This data is shown below. He wants you to get at the actual sales targets.

In order to compute the sales target, you’d ideally want to multiply the numbers in the second column with those in the third. Except that you can’t.

You can’t do this because your boss has entered each data point as a text (” 180% of Last Year”). So what you’re going to have to do is manually look up each number, and enter it as a formula in the adjoining cell.

Now, there are only nine people in this hypothetical example, but imagine a sales team that comprises of say, 200 people.

Problem? Late night at the office? Frustration all round?

Not with Excel, there isn’t!

Excel has this rather nifty formula called “left”. And left is as left does.

Left (the formula) lets you take a cell and extract a certain number of characters from the left. So, for example “=Left(A3,4)” would extract the first four characters out of whatever happens to be in cell A3.

You can see where we’re going with this, can’t you?

All you have to do is what’s shown in the picture below, and drag throughout the entire problem. Voila, problem solved!

That’s a fairly simple application of the Left formula. And yes, Excel has a “Right” version as well. Not to mention a “Mid”.

How have you gone about using these formulas? Any particularly delectable use cases? Do let us know!

Thank You, Transpose formula!

Formulas,Functions Sean Duffy

I bet you’ve been here: Hundreds of tables to be downloaded from the internet, and a report that has to be delivered yesterday. It has got to be error-free, of course, along with being well formatted, neatly laid out and in a standardized layout that brooks no exceptions. And you’ve just about finished figuring out all your data sources, and are getting ready to prepare the final report when you notice (dear me!) that your base data files has data in this layout:
 

And of course the standardized layout requires them to be laid out top to bottom, rather than left to right.

Much weeping, and perhaps an occasional joust of head butting with the wall – that would be the standard response, right?

Wrong! Not when your friendly spreadsheet software with the comforting green logo is around – this sounds like a job for Microsoft Excel.

For the Transpose function within Microsoft Excel, actually.

Doing this using the transpose function is just a little bit tricky, but pay a little attention, and you should be able to pull this rabbit out of the hat! First up, select a range of cells that does not overlap with the original range – make user that the dimensions are the same as the original, but reversed.

In other words, there are two rows and twelve columns here, so your new range should have two columns and… you guessed it… twelve rows.

Enter the following formula (you want to, of course, adjust the numbers shown to suit your requirements)

And what you get is the following:

Umm, whoops. That didn’t quite work out, now did it? But hey, help’s only a click away. Selecting the forst column, and formatting the cells in the data format will have you with your data just the way you wanted.

And off you go! That should help you get home on time after all.

There’s a much, much easier way to do this as well – it involves using some nifty Paste Special commands – and that’s what we’ll talk about the next time around. In the meantime, feel free to let us know what you think in the comments below!

All About Cells in Excel

Formulas,Tips and tricks Sean Duffy

Complicated spreadsheets are a double edged sword – for the Excel enthusiast, they are fun to prepare. But no matter how much of an enthusiast you are, decoding somebody else’s excesses is a right royal pain.

And this is especially true when it comes to decoding the thought process that somebody else put into making an Excel file with 50 spreadsheets. Multiple formatting styles, different cell widths and heights, dates, text and number formatting that varies by column are just some of the problems that you are likely to face. Of course, it doesn’t end there – certain rows or columns might be hidden, others might be resized and so on and so forth.

If you are the unfortunate soul upon whom lies the heavy responsibility of editing or adding to this spreadsheet… well, it would seem there are long nights in store for you, my friend.

Well, not quite. 

An especially impressive arrow in Excel’s quiver is the “Cell” formula. The “Cell” formula can take multiple arguments ans return a wide variety of information about the cell in question, unlocking all of it’s myriad mysteries.

For example, let’s say you need information about whether the text in a cell is formatted as a date, or if it simply is text. One way to find this out might be to right click on the cell, and check its formatting. But you can’t go about doing that for a thousand cells at once, right?

Enter the “Cell” formula.

The cell formula is a quick and easy way to find out what exactly lies in a cell in terms of formatting, contents and so on. Here’s a quick and easy example:

D1, in the middle cell, is the value that is returned if you use the formula shown in the cell to the right. It simply tells you that the contents of the first cell are formatted as a date, and in particular, in the D1 format. Particularly useful if you have to figure out varying date formats over a thousand rows or so.

Of course, there are many such tricks up the “Cell” formula’s sleeve. Here’s another one:

This tells you if the cell contains text or numbers. Quite often, what seems like numbers actually turns out to be a cell containing text – and good luck trying to edit that complex formula you entered in a cell many, many spreadsheets away. A “Cell” formula in a nested “if” formula can save you a lot of time.

Sometimes, the variant shown above can turn out to be particularly useful – simply di

Using The Search Formula in Excel

Formulas,Functions,Tips and tricks Sean Duffy

Here’s a little known trick about Excel that will come as a pleasant surprise to folks who have spent many an hour in unravelling ‘messy’ data.

Say you’re working with an Excel sheet that has a data set as seen below.  Numbers have been fed into a column, but the formatting is not consistent. Some entries read (Roll_No_1) while others are (R12). What you need at the end of the day is just the number, without any of the preceding text. 

The easy, simplistic and slightly crude way would be to look at each cell and make a note of the the numbers, but good luck to you if you are handling admissions at a university, with around 13,000 applications to go. That’s a very long night indeed.

But there is a quicker way around this conundrum, and it’s quite simple too!

Excel has this formula called the SEARCH formula – all it does is look up a particular piece of text within another piece of text. So if you asked it to search for the letter ‘e’ in ‘text’, it would come up with the answer ’2′. 

After that, all you need to do is use the ‘RIGHT’ function to arrive at just what you need – the numbers themselves.

Painless, quick and simple, right? Try it the next time you’re working with data that is tricky to navigate – you’ll be surprised at how powerful this simple formula can be.

Do you know of any other uses of SEARCH that totally rock? Share ‘em with us! Let us know in the comments below!

Simplifying Loan Payments with Microsoft Excel

Formulas,Functions,Tips and tricks Sean Duffy

All of us have been tempted every now and then to succumb to the allure of that fancy new LED HD TV, or perhaps the latest and greatest version of the iPhone. Or maybe you’ve had your eye on that fancy sports car and can’t wait to own one.

Well, except for the lucky few who are able to fall back on their millions, these kind of temptations imply some kind of a loan.

And here’s the thing – there’s an entire industry out there that tries it’s best to confuse you about exactly how much is due from you, month on month. Some of them will tell you that no payments are due for up to six months after you buy the product, while others will talk about zero down payment. Still others will lure you with ‘teaser rates’… you get my drift, right?

Time for the good news – Microsoft Excel steps up to the plate, and simplifies matters for you.

Here’s how:

Let’s say you’ve decided to go and buy yourself the simply the best vacation around – a month week stay at the most expensive resort in Hawaii, first class tickets to and fro – the works. The only problem is, your travel agent estimates the costs to come out to around 100,000 USD. That’s a cool hundred grand, right there.

So if you were to take a loan, exactly how much would the monthly payment be?

Well, if you were to take a five year loan at 10%, here’s what the basic information looks like.

So, at the end of the day, what’s the monthly damage to your wallet? Ignore all of what the agent has to say, and rush to your laptop. Excel has the answer!

The syntax is simple:

  1. PMT(rate, nper, pv, [fv], [type])
  2. rate: is nothing but the interest rate payable, which in our case is 10%
  3. nper: the number of periods of payments (60 months as far as we’re concerned)
  4. pv: the principal value (100 grand in our little tale)
  5. fv: the future value, which you can safely ignore here
  6. type: if you ignore this, Excel assumes that payments are due at the end of the period. If you put in the value 1 (one), Excel assumes that payments are due at the beginning of the period.

Well, simply enter the values as shown below, and ta-da! You’re good to go!

Once you hit enter, Excel tells you exactly how much you will owe on this loan for the terms mentioned. Anything more, and you got a problem on your hands!

Automagic Lists

Formulas,Tips and tricks Sean Duffy

Every so often, every Excel user needs to create a list.

This is a fact of life, and and every Excel user across the world accepts it as fate decreed from the heavens. You know the kind of lists that we are talking about – you might have to enter dates in succession, perhaps, or maybe it just involves a long column of numbers from (heaven knows why) 2390 to 5630.

The thing is, creating this list can be an incredibly banal task (unless you are in the know).

Well, today’s banal dispenser… err… Excel Training…. is about lists – specifically, about how to automagically create lists.

Let’s begin with a fairly simple example.

Here’s a series, the beginning of which has been typed out manually:

Let’s say this series needs to be extended upto 10. Now, typing out those seven numbers shouldn’t take very long at all, but what if the list had to go out to a 100? That’s where auto-fill comes in pretty useful. All you need to do is select the series typed out thus far (think of it as the ‘seed’ for Excel to know what to do), and move your mouse over to the lower right corner, until the icon changes from the usual white cross to a small black cross… like so:

… and that’s it! Just drag the series down for as long as you like.

Here’s a little known tip – it works the other way too! Select the cells in which you have typed in 1,2,3 and move your cursor over to the bottom right until it becomes the stubby little black cross that enables automagic fill. Now, instead of dragging the series down, drag it up.

Full of pleasant little surprises is Microsoft Excel, isn’t it?

The best part is, this feature extends to dates as well! If you were to type in the 1st of January 2011 (say) and extend that series out, Excel would assume you wanted daily increments and fill out the series for you.

And what if you wanted monthly increments? Well, no problem there. Type in Jan 1, 2011 in the first cell, Feb 1, 2011 in the second… and use the automagic drag. That’s all!

Excel’s SUMPRODUCT Formula: Understanding & Using It

Formulas,Tips and tricks Sean Duffy

Today, we will discuss the Excel SUMPRODUCT formula - one of the more handy ones in Excel’s engine-room which is literally teeming with formulas of all kinds. Not only are there formulas for different aspects of numbers, these formulas can be used in ways not thought of by the coders themselves. Fertile ground, in short, for a lifetime of learning.

Here’s the one line description that Excel offers in terms of explanation for the formula:

“Multiplies corresponding components in the given arrays, and returns the sum of those products”

Yes, a very dry and drab description… But hey, that’s why we are here! Here’s a little bit of an extended explanation.

Say you have a range of numbers in column A, and another range of numbers in column B.

Well, what Sumproduct allows you to do is, take the array (an array is best thought of as data stored in more than one cell) that reposes in columns A, and multiply it with those in columns B. The sum of the individual products is what the Sumproduct formula returns.

Have a looky at the data given below. If you have time on your hands, you could go ahead and try and compute the following (1*6) + (2*7) + (3*8) +(4*9) + (5*10).

Or, you could just use Sumproduct instead, and know that the answer is 130 before… well, before anybody using Sumproduct does.

 

Well, good for you, the cynics among you might be thinking. And so what?

Well, you could make use of this formula to save you a lot of time. Calculating sales volume over a year for 500 salesmen over 12 months might leave you hunting for a pivot table or array, but the sumproduct forumla is another, often easier way. 

 

If you would like to learn many more such tips, and to become dazzlingly proficient at Excel, try these top Excel tutorials.

Chandoo does a particularly good job of explaining just how that is done.

And that’s just the simplest application. People have gone ahead and done some seriously cool stuff with the Sumproduct formula. If your knowledge of Excel goes beyond the intermediate stage, do have a look at the following:

  • Charley Kyd explains how to go about using the Sumproduct formula to find the last item in a list. Contrary to what he says on that page, there are a couple of other ways to go about it as well, but the Sumproduct method is certainly one of the easier ones.
  • Roman To Arabic, And Back Again!

    Formulas,Tips and tricks Sean Duffy

    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 A