Frequently Used Funtions and Methods

[Norie]

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]

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.,

Book2
	D	E	F	G
4	$4,166.67	$4,166.67	$4,166.67	$4,166.67
Sheet1

Another frequent method?

 

[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)

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]

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]

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]

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

Does not!!!

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

 

[Mark O'Brien]

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]

...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]

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]

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!