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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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.
 

Forum statistics

Threads
1,141,592
Messages
5,707,287
Members
421,500
Latest member
Alex2302

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top