Microsoft Excel Training | Online Excel Training

Excel Formulas

What’s inside Excel Everest? Tutorial Contents Part 14

Resources,Tips and tricks Sean Duffy

Continuing on with our “What’s inside Excel Everest?” series, which showcases the introductory, “Why is this important?” paragraphs found within Excel Everest.

…why is it important to learn?…

IF statement (logic formulas)

The IF statement in Excel is a “logic” formula. Logic formulas in Excel let you choose to do something (perform a calculation, do a vlookup, input text, etc) based off criteria you specify. You might use the IF statement, for instance, if you want to sum a range of cells but exclude any negative numbers. The IF statement is THE essential logic formula. It’s extremely flexible and can be used for many purposes, even including conditional formatting. You can put IF statements into other IF statements (called a nested IF) in order to perform multiple functions based on multiple criteria.  Lastly, you can use multiple criteria to choose whether or not to perform an operation by using the AND and OR functions within an IF statement.

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.
  • The Excel COUNTIF Function

    Functions,Tips and tricks Sean Duffy

    Today’s Excel tutorial uses the countif function to solve a problem that seems simple, but may well have you scratching your head for a while. We’ll be honest and admit that we were scratching ours for a fair bit! Turns out it’s solved by a very interesting use of the Excel Countif function or formula.

    A friend got in touch and asked us the following question:

    Given these range of numbers, how would one count the number of times a number falls between 50 and 75?

    Pshaw!, we said. Hmph!, we sneered. And in tones that might best be described as condescending, we suggested that COUNTIF might work. Said friend thanked us from the bottom of her heart, and all was right with the world. Until said friend called back and said it wasn’t working.

    We cleared our throats, harrumphed gently, and allowed that what we meant to say was COUNTIFS, really, and not COUNTIF.

    Friend went and attempted to use COUNTIFS, but to no avail.

    At which point we commenced our head-scratching. To realize that yes, COUNTIF will not work, because COUNTIF does not accept multiple criteria. and COUNTIFS will not work, because COUNTIFS do use multiple criteria, but on different columns, not multiple criteria on the same column.

    And after some diligent head scratching, we came up with  solution for our friend. And a blog post for you, dear reader.

    Which is appended below.

    So, here’s how you go about. To start with, name the range on which we shall perform our Excel magic:

    Then, use the Excel COUNTIF formula, but with a twist.

    First, use the COUNTIF formula to identify the number of times “50″ is exceeded.

    Then, use COUNTIF to identify the number of times “75″ is exceeded.

    Subtract the second COUNTIF from the first.

    Attain Excel nirvana.

    Simple, wasn’t it? And yet, it’s a pretty powerful application of set theory (remember reading up about it in school?) and COUNTIF. Hope this helps – and as always, if you have some nifty ideas that the world would benefit from – well, let us know about ‘em!

Evaluating a formula in Excel

Equations Sean Duffy

BODMAS.

It’s been a while, but I still remember the shiver of apprehension that would run up my spine at the very mention of the term. If you don’t know what I’m talking about, you might want to wander over to this link.

Brings back all those algebraic memories, eh?

And the reason I bring this up is because today’s Excel tutorial talks about how Excel spares you the trouble of evaluating if you have got a complicated formula just right. Here’s how:

Imagine if you had to write a formula that would double 14, divide the result by 3, add 12 to this result and finally subtract 2 from whatever answer you got. I know, I know – this is stretching your imagination a bit, but humor me.

You’d write something like this, then, wouldn’t you (correct me if I’m wrong!)

12+(14*2)/3-2

Still, wouldn’t it be nice if Excel could walk you through this formula, step by step?

Yes it would, and yes Excel can. Here’s how:

Simply write out the formula in a cell, and go over to the Formula tab. Choose Evaluate Formula:

It goes without saying that you should click on this button making sure that you have selected the cell in which the formula has been entered, of course.

A new window should pop-up now:

Excel underlines the term it shall evaluate first (14*2). Once you click on evaluate, the result shall appear in italics in the next round of evaluation:

And so on and so forth. It’s a pretty useful trick to make sure you have got increasingly complicated formulas down just right – isn’t it?