Frequently Used Funtions and Methods

[Lewiy]

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]

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]

Most used:-

SUMPRODUCT
VLOOKUP/LOOKUP
INDEX
MATCH

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]

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]

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]

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]

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]

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

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

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]

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]

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!