Excels MOST useful formulas (Excel (All))

Topic

New Reply

Hi,

I am currently compiling a list of (arguably) Excels MOST useful formulas. I am planning to set up a short training course around this topic and would like to get some input as to what you think is a valuable formula/function in Excel. Since there are soooooo many functions available, I am not after the standard functions like IF and VLOOKUP, etc… I am after those amazing or special ones that are not “common”. For example what Jan Karel mentioned the other day on IF(INDEX(MATCH….), in post 754,382, which is faster than VLOOKUP, or array functions that many people don’t know or consider using, like {SUM(IF(…),IF(…))}.

Even if you can point me to a thread in the lounge with one or two unique/interesting/special formulas that can do amazing or uncommon things in Excel.

I know I can sit and search for hours myself, but if 10 loungers can point me to some threads or inform me of an example, it will same me so much time and personal effort.

Much appreciated for your help. I will be grateful for amy leads.

Cheers

Reply | Quote

Replies

(Edited by Rudi on 27-Jan-09 11:25. Adding additional examples…)

Conditional arrays:
Here is a post from Hans that has more examples of these “Special” formulas I am refering to: post 738,134

Reverse Name and Surname
And yet another thread that shows how to use Excels powerhouse formulas to accomplish a “non mathematical” result: The thread starts at post 751,759

Reply | Quote

The second thread mentions Bob Umlas’ Array Formulas which describes a lot of unusual things you can do with array formulas.

Many of the SUMPRODUCT and SUM(IF(…)) formulas posted here in the Lounge are workarounds for SUMIF and COUNTIF with multiple conditions. If your audience uses Excel 2007, you can mention that multiple conditions are finally supported directly by the new functions SUMIFS and COUNTIFS.

Reply | Quote

This is a great link Hans. Thanks for pointing me to these sample array formulas. There is a lot of interesting ideas and solutions to problems that can be encountered on a spreadsheet.

It often amazes me how extraordinarily powerful Excel’s functions are – the fact that you can string a few together to do amazing tasks and not even mentioning the array options. It makes one wonder what CANNOT be done with a creative mind and an Excel formula or two!

TX

Reply | Quote

Rudi:

Attached is an article from Microsoft about how to use Excel to compare two lists. It not only show the power of functions but also how to use Excel to better organize data.
Plus it a very popular item with Users.

Regards,

Tom Duthie

Reply | Quote

Many thanks Tom for your addition. Its great!!!

Reply | Quote

I find I use
OFFSET
and
INDIRECT
quite a lot

zeddy

Reply | Quote

Strange…those are two that I hardly ever use. It depends on what tasks you perform in Excel.

TX Zeddy

Reply | Quote

OFFSET is very useful in combination with MATCH, for example in lookup-to-the-left.

Reply | Quote

Oh, and take a look at Dynamic Named Ranges – they also use OFFSET.

Reply | Quote

Ah, yes…this rings some old bells.

I had to dig deeeeep down for this thread…

See post 394,337 for an example of a Scrolling Chart using dynamic range names,
and
See post 466,223 for an example of a Changing Range Chart

Reply | Quote

So you *knew* about the power of the OFFSET function…

Reply | Quote

Its the *only* time I ever use OFFSET.

Its a cool trick though!

Reply | Quote

Thanks to Torquemada, Hans has provided another interesting function that I have added to this thread.
See it here: post 755588

Its seems like an array within another array. I must delve into this a bit more. The only other time I have seen this is in the functions LARGE and SMALL where one can set it up like this:
=LARGE(A1:A100,{1,2,3,4,5}) to return the 5 largest values in the range. The function itself is not confirmed as an array function! So you simply ENTER (not CTRL+SHIFT+ENTER)

Reply | Quote