Introduction to Excel - Top-5 Functions

re: Introduction to Excel - Top-5 Functions

LilA — Wed, 21 Oct 2009 22:49:07 GMT

I'm gonna go with Nate here and agree with SUMIF and COUNTIF versus SUMPRODUCT. Those are both very simple functions and yet get plenty of usage.

Personally, I use VLOOKUP all the time and it's widely used where I work.

Explaining VLOOKUP to someone is far far easier than INDEX/MATCH.

Generally when users need more advanced functions, they pass the request on to a superuser. When you have users that use a CALCULATOR to do the math and then manually enter the results into Excel so the numbers line up all nice and pretty, about the extent of "stretching" you're going to achieve is SUM or other basics like that.

re: Introduction to Excel - Top-5 Functions

Sven — Wed, 08 Jul 2009 01:53:44 GMT

As a 14 year old, I just wanted to say I'm getting a kick out of this.

re: Introduction to Excel - Top-5 Functions

Nate Oliver — Mon, 06 Jul 2009 23:16:30 GMT

I don't need emphasis - what might be better is for you and I to sit down and have a happy hour. It's Shaumburg, right?

I think you're getting a little carried away, a 14-year old shouldn't have to go out and buy Office Professional, or Oracle, or SAP, to manage some stats. Nor should my mother.

At the same time, I concede, this is a very database-like function - one that's used a lot. No one said lots of data, take the following:

www.pro-football-reference.com/.../MillKe00.htm

That's not a lot of data.

Is it your contention that a 14-year might not want to cut something like that, in Excel? I'd say your imagination is lacking - I have cut this kind of data, in Excel, before.

Why would I set up a SQL Server to cut that? Allow me - you wouldn't.

You don't need all caps - we can all read what you're typing.

re: Introduction to Excel - Top-5 Functions

fzz — Mon, 06 Jul 2009 21:07:00 GMT

From my previous response with CAPS since you seem to need emphasis: What NONcontrived VLOOKUP uses have you found that would be of any interest or practical value to 14 year-olds?

As for your lookup example, nice try. I've already shown my 2 older kids how to use OpenOffice Base to store and retrieve tabular data. If lookups provide final results, databases are more appropriate than spreadsheets. Or should I be teaching them how to use hammers to drive screws?

Apparently your imagination isn't sufficient to come up with an example more relevant to spreadsheets than databases.

Yes SUMIF and COUNTIF can plow through lots of cells more efficiently than SUMPRODUCT, but databases can run through lots of data while SUMIF and COUNTIF would shuffle and SUMPRODUCT would crawl. For little data, SUMPRODUCT's greater flexibility usually makes it a better choice despite its slowness. For lots of data, better to keep it in databases and use database queries to produce conditional summaries. That's definitely into advanced territory.

For interactive use, filters, outlining, subtotals and pivot tables eliminate the need for lookups. Lookups are mostly needed in formula-driven, less interactive models, and those usually aren't produced by spreadsheet beginners.

re: Introduction to Excel - Top-5 Functions

Nate Oliver — Mon, 06 Jul 2009 16:42:37 GMT

So, the request is a contrived, non-contrived, example?

Well, I'd have to guess that a 14-year old would use VLookup() for the same reason as anyone, to extract data from a given list. If you're asking for a context? Perhaps sports statistics of some sort? A player's career, team statistics, a fantasy league, their own stats, etc... This is only one context, and is only limited by the imagination.

I use SumIf() and CountIf() all the time, they're more efficient than SumProduct() when it comes to conditional calculation with respect to a single condition. Using SumProduct() in these cases is akin to hunting mice with an elephant gun - it works, but, I think I'd proceed with something a little less heavy-duty.

There's an extreme example of this, here:

www.decisionmodels.com/optspeedk.htm

A musical outfit, Men At Work, wrote a song about this; it's called 'Overkill'. It's a good song, if you haven't heard it, I recommend you check it out.

re: Introduction to Excel - Top-5 Functions

fzz — Mon, 06 Jul 2009 02:49:52 GMT

I have taught 14 year-olds (my kids - actually when they were younger than that) how to use spreadsheets for charting (OpenOffice Calc rather than Excel). Some calculations involved, but no need for VLOOKUP.

What noncontrived VLOOKUP uses have you found that would be of any interest or practical value to 14 year-olds?

SUMPRODUCT does everything SUMIF and COUNTIF can do and a lot more. I don't use SUMIF and COUNTIF, so it'd be just a bit hypocritical to teach them.

re: Introduction to Excel - Top-5 Functions

Nate Oliver — Fri, 03 Jul 2009 15:28:52 GMT

Apologies, I edited your comment to remove the all-caps. When I requested no more all-caps comments, what I meant was no more all-caps comments, please.

It's too loud, we can all read what you're saying without it, and I don't want to scare off potential readers/comments with such loudness. You wouldn't send a colleague an email as such, would you?

Okay, so you're teaching a 14-year old who has never used Excel before, and you're saying VLookup() is too over-the-top? I guess I just don't agree. Grid, lookup, offset.

But, I wouldn't jump straight into SumProduct() and Index(Match()) functions, these simply come after, imo. Wasting time? I believe you need to learn to crawl before you walk, and walk before you run the marathon.

re: Introduction to Excel - Top-5 Functions

fzz — Fri, 03 Jul 2009 10:10:00 GMT

Forgiving?

I don't claim my opinion is the one true way, but VLOOKUP really isn't for beginners. It may prove useful for small tables, but once tables extend past 10 or so columns, the potential for errors in the 3rd argument begin to grow exponentially. Experience teaches those few who become proficient Excel users that

=INDEX(DF3:DF1002,MATCH(x,A3:A2002,0))

is less prone to error than

=VLOOKUP(x,A3:DZ1002,110,0)

The latter is more compact, but that 110 as 3rd argument is one of those 'magic' numbers that should be avoided if possible.

One way around this is using distinct column headings, then using MATCH against these column headings to determine the column, e.g.,

=VLOOKUP(x,A3:DZ1002,MATCH(f,A2:DZ2,0),0)

but at that point why not use

=INDEX(A3,DZ1002,MATCH(x,A3:A1002,0),MATCH(f,A2:DZ2,0))

which makes some ex-123 users nostalgic for

@XINDEX(A2:DZ1002,f,x)

The problem with VLOOKUP, SUMIF and COUNTIF is that not long after one becomes comfortable using them, one discovers their shortcomings and has to move on to INDEX(.,MATCH()) and SUMPRODUCT. Why waste time on these intermediate steps rather than go directly to robust formula idioms?

re: Introduction to Excel - Top-5 Functions

Nate Oliver — Fri, 03 Jul 2009 00:59:17 GMT

Hi fzz, I see you're not as forgiving as I am, e.g.,

msmvps.com/.../online-excel-resources.aspx

"When you make a list like this, given the vast amount of high-quality Excel resources on the Internet, you're going to get it a little right, and probably a little wrong..."

There's a lot of Worksheet Functions - to pick only 5 is kind of tricky. I probably got this a little bit right, and a little bit wrong.

And keep in mind, we're not all insurance actuary's in a Chicago suburb.

While I might agree that VLookup() is more intermediate than Int() or Round(), it could easily prove to be more useful to a lot more people. You seem confident that your opinion is pretty much beyond reproach. Why is that?

It comes back to what Debra said, not to put words in her mouth. If you learn these five, the next five become a lot simpler.

At least we all agree that Sum() is where you start?

re: Introduction to Excel - Top-5 Functions

fzz — Thu, 02 Jul 2009 21:32:46 GMT

If you're going to include SUM and COUNT, basic descriptive statistics, you should include AVERAGE, MIN and MAX.

VLOOKUP is more of an intermediate function.

COUNTIF and SUMIF have too many gotchas to be appropriate for beginners. Just look at all the newsgroup questions they generate.

So I guess that means my basic 5 would be SUM, COUNT, AVERAGE, MIN and MAX. I might extend this to 8 (per Ken Puls) with IF, ROUND and INT - can't have ROUND without INT.

re: Introduction to Excel - Top-5 Functions

Nate Oliver — Mon, 29 Jun 2009 17:23:58 GMT

Thanks for the vote of confidence, Debra!

Ken, you make a great point, and If() is probably a glaring omission due to its usefulness, in general.

re: Introduction to Excel - Top-5 Functions

Ken Puls — Mon, 29 Jun 2009 15:17:51 GMT

It astounds me when I teach how many "intermediate" users have never used a vlookup.

Personally, the top three formulas that I would teach a new user out of the gate are SUM, ROUND, IF. Add Count to that list and they can achieve SUMIF and COUNTIF with the use of helper columns.

re: Introduction to Excel - Top-5 Functions

Debra Dalgleish — Sun, 28 Jun 2009 16:42:39 GMT

Nate, you're right -- this list is a good start for someone new to Excel functions. Once you understand these five, it's easy to figure out how some of the other, similar, functions work.

re: Introduction to Excel - Top-5 Functions

Nate Oliver — Sat, 27 Jun 2009 17:21:52 GMT

Keep in mind, this list is fairly arbitrary, because I made it up - in good faith.

Said list could easily include Min(), Max(), Round(), Average(), etc... As I said, there's a lot of useful Worksheet Functions in Excel.

For this particular entry, I simply had to start and stop somewhere - and I wanted to write some content for someone starting out with Excel.

It is my intention to cover more advanced Worksheet Functions in future blog entries.

re: Introduction to Excel - Top-5 Functions

Nate Oliver — Sat, 27 Jun 2009 16:54:58 GMT

Hi Sam,

I think the list you're providing, while solid, differs from my intent. SumProduct() is far and away my favorite Worksheet Function, because it's so versatile.

However the intent of this blog entry is to simply introduce Excel to someone who hasn't used it - in terms of 5 Worksheet Functions.

While I agree that Index(Match()) is much more robust than VLookup(), I'd recommend learning how to use VLookup() before learning that combination.

re: Introduction to Excel - Top-5 Functions

sam — Sat, 27 Jun 2009 07:21:10 GMT

For me its

1) IF

2) SUMPRODUCT (Which can do SUM, SUMIF, SUMIFS, COUNT, COUNTIFS, VLOOKUP -to some extent)

3) MATCH/INDEX - Which can do everything that Vlookup/Hlookup can and lots more. It also removes limitation of Vlookup/Hlookup

4) Count/Counta/Large/Small

Sumproduct cant handle errors between data

However SumIF(s) and CountIF(s) cant distinguish between beyond 15 digit in a number with 16 digits (stored as text)

Eg; Credit card number where the 1st 15 digits are same.