# Frequently Used Funtions and Methods



## Lewiy (Feb 22, 2007)

Just curious really, what functions and methods of doing things does everyone use the most often and more specifically, which unusual or less well known functions you find particularly useful?  (referring to actual worksheet functions as well as general Excel options/functions)


----------



## ExcelChampion (Feb 22, 2007)

Most used:-

SUMPRODUCT
VLOOKUP/LOOKUP
INDEX
MATCH

Uncommonly known, but useful:-

Can't think of particular functions, (other than DATEDIF, which has issues).


----------



## Richard Schollar (Feb 22, 2007)

> Most used:-
> 
> SUMPRODUCT
> VLOOKUP/LOOKUP
> ...



I'm in danger of doing a Truby here and going off-topic , but I hadn't ever used (actually, hadn't ever even heard of) Sumproduct before I joined this Board.  How times change - I couldn't imagine using Excel without it now.  I agree with everything in EChamp's list.  Lookup has some particularly useful applications, which are not hinted at at all in the Excel Help.


----------



## Cbrine (Feb 22, 2007)

I was agree with the above's but also the really simple logic checks, which when combine correctly, can become very powerful.

if(or(and(or())))

Also the string manipluation functions.
Mid()
left()
right()

HTH
Cal


----------



## SydneyGeek (Feb 22, 2007)

Yep, the worksheet functions above are all on my list. Like Richard, I had never come across SUMPRODUCT before finding this site, and LOOKUP was a curiosity. 

But it's also how you use them: I like to go through posts where the formula wizards have been, just to get ideas. 

I like the Advanced Filter. 2 main reasons:

1. Pulling a unique list from a column of names
2. I find it easier to use in VBA than the Autofilter. 

Also, I'm a fan of pivot tables for data crunching. 

When entering dates and times, the DATE() and TIME() functions are good.

And in VBA, whenever I have to parse anything I reach for the Split function. 

Denis


----------



## RichardS (Feb 22, 2007)

SUMIF, COUNTIF and such conditional formulae in conjuction with other basic formuale. Like Denis, I use Advanced Filters extensively. Also, as I spend a lot of time manipulating data from other applications, whcih is quite often pretty ugly, the Text to Columns function gets a bit of a work out.

Again in agreeance with Denis, Pivot Tables are indispensible.

Cheers,

Richard


----------



## Lewiy (Feb 22, 2007)

> I hadn't ever used (actually, hadn't ever even heard of) Sumproduct before I joined this Board.



Whilst I was aware of the existance of Sumproduct, I too had never found a use for it before joining the board, now it features heavily in many of my routine tasks (and thanks to a post earlier this week I now understand why it works as well as how to use it), no more trying to push the limits of {SUM(IF...}!!!

The learning continues.


----------



## ExcelChampion (Feb 22, 2007)

> if(or(and(or())))
> 
> Also the string manipluation functions.
> Mid()
> ...



Not to say that these aren't useful, but I would say these are secondary functions, for me, that I use as dependents of SUMPRODUCT, INDEX, MATCH, etc...(most of the time)


----------



## Cbrine (Feb 22, 2007)

My thoughts are that no one formula or method is the key.  It's having the knowledge of as many functions and methods as possible.  If you don't know that something is possible, you will end up trying to force what you do know into doing the job.  Each situation you need to deal with is unique, and most likely has a 1/2 dozen possible solutions from pivot tables, to array formula's, to sumproduct, to vba, etc...  Once you have a good solid knowledge base, that's when Excel becomes a powerful tool.


----------



## NateO (Feb 22, 2007)

Hello,



> My thoughts are that no one formula or method is the key.


I agree. 

Some of the functions listed are extremely useful, no doubt about it, I think SumProduct() is my favourite Excel function...



> Just curious really, what functions and methods of doing things does everyone use the most often and more specifically, which unusual or less well known functions you find particularly useful?


The most used function has to be Sum(), yes-no? Perhaps even miss-used, to some extent. And If() can't be too far behind...

I wouldn't say I use Max() a lot, but I did tonight, when helping a buddy with a financial model... He has a complex method for allowing highly variable comp. employees to draw salaries with fixed and variable caps, and the whole nine yards... 

Here are two examples of how I used Max(), tonight:


```
=IF(SUM($C$4:C4)+MAX($B$4/12,D6-SUM($C$4:C4))<50000,MAX($B$4/12,D6-SUM($C$4:C4)),MAX(50000-SUM($C$4:C4),0))
```


```
=IF(D10>400000,MAX(D9-(D10-$B$9),0),D9)
```
It would help more if you saw the model... But, Max() and Min() are great for setting floors and ceilings when doing comparisons...

And what do you know? There are those Sum() and If() calls again!


----------



## Norie (Feb 23, 2007)

Nate

Did you really use this?

SUM($C$4:C4)



> The most used function has to be Sum(), yes-no? Perhaps even miss-used


Or is it a typo?


----------



## NateO (Feb 23, 2007)

Hi Norie,

Yes, no typo...

It would have helped to see the compensation schedule... It was a monthly schedule, and I dragged it to the right, calculating YTD figures. E.g.,Book2DEFG4$4,166.67$4,166.67$4,166.67$4,166.67Sheet1

Another frequent method?


----------



## ExcelChampion (Feb 23, 2007)

> > if(or(and(or())))
> >
> > Also the string manipluation functions.
> > Mid()
> ...


I agree that these functions are extremely important, to say the least.  But the question was, "What functions do you use the most?"  In these terms, I use SUMPRODUCT the most with any of the string functions {etc} and comparison operators to create conditions within SUMPRODUCT.  That's why I said they are secondary.

Also, I use SUMPRODUCT way more than I use SUM.  Almost everything I count or sum is conditional...and there's usually more than one condition..."Give me the sales for company ABC for the month of X and year of Y")

It's interesting to see what other people use frequently though...


----------



## NateO (Feb 23, 2007)

It really depends on what you're doing.



> Also, I use SUMPRODUCT way more than I use SUM. Almost everything I count or sum is conditional...and there's usually more than one condition..."Give me the sales for company ABC for the month of X and year of Y")


And some people might use a Pivot Table for this and never use SumProduct()...

I use SumProduct() a lot, more that your average user to be sure, and I'm a big fan, I think it's wide array of use is under-rated... 

But I can't seem to apply SumProduct() anywhere near, and I mean nowhere, near as often as your plain-old, vanilla Sum(). For whatever reason, I just seem to be summing lots of data... 

It seems to me that Sum()'s not secondary, it's primal. It's one of the basic building blocks for all Worksheet Functions in Excel, in practice and conceptually, no? 

But, yeah, it depends what the task at hand is. If you're doing a DCF model, IRR() might just be the best Worksheet Function ever (if it works), 'cause that iterative, hit-or-miss calculation is a nightmare to do by hand.


----------



## Mark O'Brien (Feb 23, 2007)

> If you're doing a DCF model, IRR() might just be the best Worksheet Function ever (if it works), 'cause that iterative, hit-or-miss calculation is a nightmare to do by hand.



MIRR() kicks IRR()'s a$$!!!


----------



## NateO (Feb 23, 2007)

> MIRR() kicks IRR()'s a$$!!!


Does not!!!    

They both have their uses... MIRR() has its faults, too. I generally use both.


----------



## Mark O'Brien (Feb 23, 2007)

Don't make me pull out XIRR().    

I found that more text book questions require the use of MIRR() over IRR(), but either way, user beware.


----------



## Greg Truby (Feb 23, 2007)

> ...And some people might use a Pivot Table for this and never use SumProduct()...
> ~Nate



Yup, that's how I roll.  I'd ballpark my _pivottable:sumproduct()_ ratio at around 50:1.


----------



## ExcelChampion (Feb 23, 2007)

The people here where I work hate...and I mean HATE, pivot tables.  They want custom design...that's why I use SUMPRODUCT so much.  But regardless, the question was what functions, in terms of formulas, do we use most...speaking for myself, it has to SUMPRODUCt with VLOOKUP as a close second.


----------



## Lewiy (Feb 23, 2007)

> the question was what functions, in terms of formulas, do we use most...


In actual fact, I'm interested to know what methods everyone uses in Excel both in terms of Worksheet Functions and other general stuff (perhaps not clarified enough in the OP).  So pivot tables are valid as would be "set print area" if it's important enough to you!!

Personally I've never really got to grips with pivot tables, I use them very occasionally just to give me a quick unique value list.  I'm sure they are much more powerful than that but I've never come across a problem I can't get around any other way which would necessitate pivot tables (but perhaps that's a different discussion entirely).

By the way, thanks for everyone's input so far, it's made most interesting reading!


----------



## ExcelChampion (Feb 23, 2007)

I took it to mean worksheet functions only...

But in that case...

I use the FIND function quite often...nearly every few minutes when working with a spreadsheet.

Pivot Tables are invaluable.

Autofilter saves tons of time...so does Sort...

How about Freeze Panes?  That's a good one that I find many users don't know...


----------



## brian.wethington (Feb 23, 2007)

> I took it to mean worksheet functions only...
> 
> But in that case...
> 
> ...


Great, someone push his button again... we may never get a word in edgewise.  Just kidding of course....   

Really though, I use sumproduct daily but for every time I use sumproduct I find myself using pivot tables 2 or 3 times.  I can't forget about text to columns though.  Can't forget about Conditional Formatting..... There really is no an end to this list for me, is this unfortunate or fortunate?


----------



## Lewiy (Feb 23, 2007)

> How about Freeze Panes? That's a good one that I find many users don't know...



Now freeze panes is one of my favourites, good call


----------



## SydneyGeek (Feb 23, 2007)

Another feature I use heaps is Names. 

Sheet-level for workbooks that consolidate multiple worksheets with the same structure but different numbers of rows

Dynamic for anything that will expand or contract, either for use in formulas or with charts and pivot tables. 

And just plain vanilla names too. 

Denis


----------



## Fazza (Feb 28, 2007)

I guess my work these days is handling database-style lists. I am forever using pivot tables. They are fantastic for the work I do.

General functions are mainly the routine ones, SUM would be used the most. Or on a filtered list, looking at the sum in the status bar.

Array type formulas such as long winded SUMPRODUCT I virtually never use. There was a time some years ago that I used them briefly, but they are not good for the tasks I do.

I do use query tables a bit and it makes doing some things so simple. A little bit of SQL and you can simply do some fancy selection, filtering, tables joins (instead of VLOOKUP), etc, etc.

Also SQL for consolidating from multiple workbooks (even closed ones) or worksheets.


----------



## Patience (Mar 5, 2007)

I have never. Ever. Used SUMPRODUCT. But I am determined. I believe it can help me, but I have never found a practical use for me. Maybe I should make it my task for this week. By Friday I will probably wonder what the blue rinsed hel* I did before!

My most frequently (over)used funtions are (in no particular order):

*CONCATENATE*
*VLOOKUP* - (sometimes using *HLOOKUP* in the column number variable slot)
*IF*
*ISERROR *- (often these are together)
*ISBLANK*

Other ones I use reasonably frequently - TRIM, LEFT, RIGHT, MID, PROPER, COUNTIF, MONTH, DATE.

This was an interesting thread. Thanks.


----------



## Domski (Mar 5, 2007)

> I have never. Ever. Used SUMPRODUCT. But I am determined...



Have a look at this, it helped me to get my head around how they worked:

http://www.mrexcel.com/wwwboard/messages/8961.html

I have to analyze very large lists quite a lot and find 10,000 sumproduct formula take forever to calculate on my poor local government PC.

One way round this I've found is a quick Pivot Table followed by Advanced Filtering the original list to produce a summary and then a cheeky GETPIVOTDATA to extract the info from the pivot table into a nice list again. Might seem a bit weird but works for me.

And although I'm still learning the basics of VBA you can't beat a few macros for automating repetitive tasks that would have taken me hours a couple of years ago.

Dom


----------



## Jon von der Heyden (Mar 5, 2007)

On my part I more commonly use:


1: SUMPRODUCT - used typically within established financial models where a Pivot Table just doesn't cut it.
2: SUMIF - which I prefer over single criteria SUMPRODUCT
3: COUNTIF - as above
4: VLOOKUP - where range lookup = 1 to eliminate unnecessary use of multiple IF's (hence also CHOOSE and others)

Though I like array formula, I try to avoid where ever possible because they seem to slow my models up.

Agree with SydneyGeek, names make things so much neater and easier to audit.  Dynamic ranges are also particularly helpful.

BUT....

As I do alot of workbook sharing amongst colleagues, many choose to use alot of fill colour  :x so SUMCOLOR / COUNTCOLOR etc. becomes especially necessary.  But I have opted to use XLM4.0 macros over the UDF's posted on all the Excel sites as it makes it easier to share with other users and easier for so many others to understand.

Ever so grateful to Schollar for introducing these to me.


----------



## Jon von der Heyden (Mar 5, 2007)

Oh and...

From my favourites:

Schollars suggestion to avoid an unnecessary loop: http://www.mrexcel.com/board2/viewtopic.php?t=192430

Clever use of Excels big number: http://www.mrexcel.com/board2/viewtopic.php?t=207653

Clever use of MOD to perform calculation on every other row / column: http://www.mrexcel.com/board2/viewtopic.php?t=241353

Working with TAX year: http://www.mrexcel.com/board2/viewtopic.php?t=210382

And I also like smart use of COUNTIF to help identify unique items from those that have been duplicated, though there are many other techniques this is one I commonly use.  E.g. =COUNTIF($A$1:A1,A1)>1

I also typically install alot of validation in my models to make avoid user errors and to help them understand (I like the input feature).

Not to forget use of conditional formatting for further diagnostic checks.

As a 'typical' user (i.e. not one of these mathematicians and incredible coders), I like to use as much of the standard features intuitively to help make my models more user-friendly


----------

