Useful Excel Functions

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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:
Code:
=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),--(Range3=Criteria3))

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

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

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

Mark
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Legend
Joined
Sep 3, 2002
Messages
10,473
Understand the difference between binary and sequential search.

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

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top