Useful Excel Functions

[MarkAndrews]

I'm not too sure if there's been a post like this before, but here goes...

Have you ever found a function (or anything else) in Excel that has surprised you? or anything that could have made your life easier prior to finding it

Reason I ask, recently (through this board) I discovered the Indirect function, using data validation

May be useful to people if brief scenario's are included in any reply's (just a thought)

Mark

 

[njimack]

INDIRECT would deffo be up there as a formula in its own right, as well as within data validation (usually combined with SUBSTITUTE). SUMPRODUCT is a fave as well. I use those 2 on a daily basis.
On the rare occasions I use pivot tables, I use GETPIVOTDATA(despite it's horrific user interface and syntax).

 

[MarkAndrews]

INDIRECT would deffo be up there as a formula in its own right, as well as within data validation (usually combined with SUBSTITUTE). SUMPRODUCT is a fave as well. I use those 2 on a daily basis.
On the rare occasions I use pivot tables, I use GETPIVOTDATA(despite it's horrific user interface and syntax).

I've never really understood SUMPRODUCT

**note to self - must learn**

 

[njimack]

I've never really understood SUMPRODUCT

It's a very simple formula to understand. There are other board members who could probably explain it much better, but think of it as a COUNTIF or SUMIF formula that can handle multiple conditions...

COUNTIF:

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),--(Range3=Criteria3))

SUMIF:

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),--(Range3=Criteria3), (Amount_to_be_summed))

 

[MarkAndrews]

Thanks Neil, will have a play with it in a draft document

Mark

 

[MarkAndrews]

How come this was moved? I put it in Lounge to chat about...

 

[Richard Schollar]

I strongly agree with SUMPRODUCT - I thought I was the bees' knees knowing how to do simple array formulae, but Sumproduct removes the need for CSE formulas in a lot of cases.

Richard

 

[SydneyGeek]

Yep, SUMPRODUCT gets a vote from me too.

I also like INDIRECT and OFFSET, but for sheer grunt when you analyse and report on large amounts of data, Pivot tables are my favourite. Their main drawback it that coding pivot tables in VBA can be messy, but I'm getting there.

Denis

 

[Richard Schollar]

I like OFFSET too - it's only when you start doing some more complicated stuff that you actually begin to appreciate how useful it can be (true of INDIRECT also). Of the newer functions that have got me excited (maybe the wrong word here? - Oh well, maybe I am a little geeky ) is SQL.REQUEST.


Richard

 

[just_jon]

Understand the difference between binary and sequential search.

Careful data layout taking advantage of binary syntax will speed you up.